CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresscope-identity
intangible02
intangible02

Reputation: 993

Pass scope_identity into a stored procedure

There are a lot of discussions about passing a scope_identity from a child to parent stored procedures. I have an opposite scenario where the row is inserted at parent stored procedure and I want to pass the identity value to a child stored procedure.

However, directly passing scope_identity to child procedure generates a parse error in SQL Server Management Studio.

create procedure parent
as
begin
   --  insert a record
   exec child scope_identity() -- this does not work
end

However, using a local variable resolves the issue.

create procedure parent
as
begin
   --  insert a record
   set @id = scope_identity()
   exec child @id -- this works
end

What is the reason for the failure of directly passing scope_identity() as input parameter?

Upvotes: 4

Views: 1858

Answers (1)

user596075
user596075

Reputation:

You can't pass a function result as a stored procedure parameter. You need to do you latter method, by saving the result to a variable and then passing the variable.

Upvotes: 4

Related Questions