Autogenerate the field value in stored procedure

   Generally we auto generate a field to identify a uniqueness when we insert  a record into a table in our code(c#,vb) page and it is use full when it is used by a single user.But it creates a major problem when multiple users are using a program which has to auto generate a field in our code page. Because of  depending on user's efficiency at the time of data entry the auto generate code may mismatch or  through error. So it is advisable to generate a number in stores procedure at the time of insert and then should display to user  that your "Transaction number is:P0001" 

 Table Design :-

Stored Procedure :-

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[PROC_ADD_POLICY_MST]

(@P_POLICY_PLAN varchar(100),
@P_MONTHS decimal(10,2),
@P_MON_INSTL INT,
@P_CREDIT_VAL INT,
@P_INSTL_AMT INT,
@P_YEAR_PLAN INT,
@P_ACTIVE CHAR(1)=null,@P_PLAN_TYPE CHAR(1)=null,
@P_POLICY_CD varchar(20)=null,@P_RET_VALUE varchar(10) OUTPUT)
AS

declare @p_pcd varchar(20)
set @p_pcd=(select right('0000'+ convert(varchar(20),max(substring(policy_cd,2,4))+1),4)as policy_cd from policy_mst)
select @p_pcd='P'+ @p_pcd
set @P_RET_VALUE=@p_pcd
INSERT INTO POLICY_MST VALUES(@P_POLICY_PLAN,@P_MONTHS,@P_MON_INSTL,@P_CREDIT_VAL,@P_INSTL_AMT,@P_YEAR_PLAN,@P_ACTIVE,@P_PLAN_TYPE,@P_RET_VALUE)

return 

VB.Net Code

           con.Open()
            cmd = New SqlCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "PROC_ADD_POLICY_MST"
            With cmd.Parameters
                .AddWithValue("@P_POLICY_PLAN", Me.TxtPlan.Text)
                .AddWithValue("@P_MONTHS", Me.NmbMonths.Value)
                .AddWithValue("@P_MON_INSTL", Me.NmbMonInstl.Value)
                .AddWithValue("@P_CREDIT_VAL", Me.NmbCreVal.Value)
                .AddWithValue("@P_INSTL_AMT", Me.NmbInstlAmt.Value)
                .AddWithValue("@P_YEAR_PLAN", Me.numYearPlan.Value)
                .AddWithValue("@P_policy_cd", "")

                If ChkActive.Checked = True Then
                    .AddWithValue("@P_ACTIVE", "Y")
                Else
                    .AddWithValue("@P_ACTIVE", "N")
                End If
                If CmbPlanType.Text = "FD" Then
                    .AddWithValue("@P_PLAN_TYPE", "F")
                ElseIf CmbPlanType.Text = "RD" Then
                    .AddWithValue("@P_PLAN_TYPE", "R")
                Else
                    .AddWithValue("@P_PLAN_TYPE", "M")
                End If
            End With
            cmd.Parameters.Add("@P_RET_VALUE", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output                  cmd.ExecuteNonQuery()  
             con.Close()
            MessageBox.Show("Record Saved and Your Transaction number is:" & Convert.ToString(cmd.Parameters("@p_ret_value").Value))