4

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?

Drew
  • 29,895
  • 7
  • 74
  • 104
intangible02
  • 993
  • 1
  • 9
  • 19
  • possible duplicate of [Cast integer and concatenate to varchar in TSQL](http://stackoverflow.com/questions/4936180/cast-integer-and-concatenate-to-varchar-in-tsql) – Martin Smith Jun 06 '12 at 18:44
  • Syntactically you are allowed to pass some functions (e.g. `@@IDENTITY`). Expanding this list [is an open request on the Connect site](https://connect.microsoft.com/SQLServer/feedback/details/352110/t-sql-use-scalar-functions-as-stored-procedure-parameters) – Martin Smith Jun 06 '12 at 18:47
  • @Martin - Thank you. It is the first time I learned that I cannot pass an expression as a parameter after so many years. I am a bit shocked. SQL2012 is out but don't know whether this is improved. – intangible02 Jun 07 '12 at 03:09

1 Answers1

4

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.