If you really wanted to specify the sno value for an insert, you can use IDENTITY_INSERT.
http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx[
^]
Here is a working example:
create table tblbilldetails (sno int identity(1,1), billno int, itemcode int, qty int, price money, total money);
go
create procedure spaddtogrid
(@sno int, @billno int,@itemcode int,@qty int,@price money,@total money)
as
begin
if @sno is not null begin
SET IDENTITY_INSERT tblbilldetails ON;
insert into tblbilldetails
(sno,billno,itemcode,qty,price,total)
values(@sno,@billno,@itemcode,@qty,@price,@total)
SET IDENTITY_INSERT tblbilldetails OFF;
end
else if @sno is null begin
insert into tblbilldetails
values(@billno,@itemcode,@qty,@price,@total)
end
end
go
exec spaddtogrid null, 1, 1, 1, 1, 1;
exec spaddtogrid null, 2, 1, 2, 1, 2;
exec spaddtogrid null, 3, 1, 3, 1, 3;
exec spaddtogrid 11, 4, 1, 1, 1, 1;
exec spaddtogrid 12, 5, 1, 2, 1, 2;
exec spaddtogrid 13, 6, 1, 3, 1, 3;
exec spaddtogrid null, 7, 1, 1, 1, 1;
exec spaddtogrid null, 8, 1, 2, 1, 2;
exec spaddtogrid null, 9, 1, 3, 1, 3;
exec spaddtogrid 2, 2, 1, 2, 1, 2;
select * from tblbilldetails;
drop table tblbilldetails;
drop procedure spaddtogrid;
go
Ordinarily I would not create a stored procedure to perform an insert in this manner.
But rather create an insert and update to the table like so:
create procedure SaveToGrid
(@sno int, @billno int,@itemcode int,@qty int,@price money,@total money)
as
begin
if @sno is not null begin
end
else if @sno is null begin
end
end
go