Generating Auto ID using Stored Procedure

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