Generating Auto ID using Stored Procedure

Raj.Trivedi
Posted by Raj.Trivedi under Sql Server category on | Points: 40 | Views : 1023
In this snippet we will see how to generate AutoID using Stored Procedure

//Table

CREATE TABLE [dbo].[tbl_customer](
[CustID] [int] NULL,
[CustName] [varchar](30) NULL,
[CustAddress] [varchar](50) NULL,
[Tel_No] [int] NULL,
[Mobile_No] [int] NULL
) ON [PRIMARY]

GO

Stored Proc

create proc [dbo].[GenerateCustomerID]
as
begin
declare @Rowcount int;
declare @tempNO int;
declare @CustomerID int
set @Rowcount=(select Count(CustID) from tbl_customer )
if(@Rowcount = 0)
begin
set @CustomerID = 1
end
else
begin
set @tempNO =(Select MAX(CustID) from tbl_customer)
set @CustomerID = @tempNO + 1
end
Select @CustomerID 'CustomerID'
end

Comments or Responses

Login to post response