stored procedure -auto increment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

stored procedure -auto increment

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-14 : 02:47:51

Hi All,

I have written sp like this, everytime the insert operation happens , the eid column should be automatically increment

CREATE PROCEDURE Ass_insertable_new7
(@a int OUTPUT,
@ename nchar(40) OUTPUT,
@sal int OUTPUT,
@dept int OUTPUT)
AS
begin

INSERT table11
(eid, ename, sal, dept)
VALUES (@a, @ename, @sal, @dept)
end
GO
declare @a int
declare @ename nchar(40)
declare @sal int
declare @dept int
set @a=1
set @a=@a+1
set @ename='basava'
set @sal=1500
set @dept=50
Execute Ass_insertable_new7 @a=@a OUTPUT,@ename= @ename OUTPUT, @sal= @sal OUTPUT, @dept= @dept OUTPUT

but in this the value is 2 for all the records...
i want it to be incremented how to proceed.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-14 : 03:30:13

you will need read the existing eid from table and increment it inside the stored procedure. Not oustside


CREATE PROCEDURE Ass_insertable_new7
(@a int OUTPUT,
@ename nchar(40) OUTPUT,
@sal int OUTPUT,
@dept int OUTPUT)
AS
begin
select @a = max(eid) from table11
select @a = isnull(@a, 0) + 1
INSERT table11 (eid, ename, sal, dept) VALUES (@a, @ename, @sal, @dept)
end

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources