Friday, 13 September 2013

How To Create PK As I need My Code Attached Sql Server 2008?

How To Create PK As I need My Code Attached Sql Server 2008?

i am trying to create PK as "CP-00001" but my code is creating PK as
"INV-19072013-0001" in my sp i need to create like "CP-00001" if no row
exist other wise it increment in previous id and then id become "CP-00002"
my sp is,
ALTER procedure [dbo].[sp_AutoGenerateInoviceNumber]
AS
Declare @InvNumberDate varchar(15)
Select @InvNumberDate = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 105),'-','')
Declare @NewNumber varchar(20)
If Exists (Select 1 from tbl_InvoiceMaster Where InvoiceNo LIKE '%'+
@InvNumberDate +'%')
Begin
Select Top 1 @NewNumber = InvoiceNo from tbl_InvoiceMaster
Where InvoiceNo LIKE '%'+ @InvNumberDate +'%'
order by ROW_ID desc
Declare @NewSerialNo varchar(10)
Set @NewSerialNo = SUBSTRING(@NewNumber, CHARINDEX('-',@NewNumber, 5) +
1, LEN(@NewNumber))
Set @NewNumber = SUBSTRING(@NewNumber, 0, CHARINDEX('-',@NewNumber, 5))
Select @NewNumber = @NewNumber + '-' +
dbo.func_IncrementNumber(@NewSerialNo)
End
Else
Set @NewNumber = 'INV-'+@InvNumberDate+'-0001'
Select @NewNumber
Hopes for your suggestion thanks

No comments:

Post a Comment