CODEWITHSUNDEEP
sqlsql-server
user1989
user1989

Reputation: 217

Inserting 0 in sql server by default

I want to return the identity element from the table and use it further in the stored procedure, but I always get value as 0.

When I execute this, I get the actual value

dbo.TEMPP:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO Table(Name) 
OUTPUT Inserted.HId INTO @OutputTbl(ID) 
VALUES ('hello')

SELECT ID 
FROM @OutputTbl

But, when I run this code, it inserts 0 into my table

declare @id int;
exec @id = TEMPP

insert into Table2(Name, sId) 
values ('hi', @id)

Can anyone please suggest?

Upvotes: 0

Views: 299

Answers (1)

jtimperley
jtimperley

Reputation: 2544

Your sample appears to be calling a stored procedure so @id would result in the return value of that procedure. The default return value of a stored procedure is 0. This is the value you receive because you are not returning anything. The SELECT statement isn't 'returned' from the procedure, you'd have to insert it into a temporary table to use it that way.

Add the following immediately after your procedure. If you are inserting into multiple tables, you'll have to set or select it into a variable and return that. You do not need to output the identity in this scenario. 

DECLARE @OutputTbl TABLE (ID INT)
insert into Table(Name) values ('hello');

RETURN SCOPE_IDENTITY();

Upvotes: 3

Related Questions