In this blog I am going to explain that how to create a procedure which automatically increment id of table. For implementing this task I had created a table which has two columns such as id and name. Value in id column is automatically inserted by procedure and value in name column is passed by variable in procedure.
Creating a table in SQLSERVER
create table AutoIncrementTable
(
id bigint,
name varchar(50)
)
After creating table now I am going to create a paramentrized stored procedure which insert value in table and
accept name as parameter. This procedure insert id in table automatically and increment id by 1.
Creating parametrized stored procedure
alter proc p_auto_increment_table_proc
@name varchar(50)
as
declare @Id bigint
select @Id=count(Id)+1 from AutoIncrementTable
begin
insert into AutoIncrementTable values(@Id,@name)
end
After creating stored procedure now execute it and see the result.
Executing stored procedure
p_auto_increment_table_proc 'Alok'
This will insert record in table and each time id is automatically incremented.
Atul Pathrikar
10-Feb-2017as
select isnull(max(Demo_ID),0)+1 from Tbl_Demo
return
Shailesh Kumar
19-Aug-2013mohan kumar
13-Mar-2012Amit Singh
25-Mar-2011Thanx