Pass scope_identity into a stored procedure
scope-identity, sql-server, stored-procedures, t-sql
Solution
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.
Problem
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?