Scope identity on a stored procedure in SQL

Tags:
Parameters
scope
SQL Server stored procedures
I have created the following stored procedure in SQL: CREATE procedure [dbo].[sp_trip_number] @Table_Name nvarchar(100), @Trip_Number int output as begin set nocount on begin transaction DECLARE @DynamicSQL NVARCHAR(1000) DECLARE @New_Trip_Number int SET @DynamicSQL='INSERT INTO ' + @Table_Name + ' DEFAULT VALUES' + ';' + 'SELECT'+ @New_Trip_Number+ +'=' + 'SCOPE_IDENTITY()' EXEC (@DynamicSQL ) SET @Trip_Number = @New_Trip_Number rollback end I am passing a table name as one parameter, but I want scope identity as an output into the second parameter. How can I do this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You will want to use the sp_executesql stored procedure to do this. Something like this.

<pre>CREATE procedure [dbo].[sp_trip_number]
@Table_Name nvarchar(100),
@Trip_Number int output
as
begin
set nocount on
begin transaction

DECLARE @DynamicSQL NVARCHAR(1000)
DECLARE @New_Trip_Number int

SET @DynamicSQL=’INSERT INTO ‘ + @Table_Name + ‘ DEFAULT VALUES; SELECT @New_Trip_Number = SCOPE_IDENTITY()’
exec sp_executesql @DynamicSQL, ‘@New_Trip_Number INT’, @New_Trip_Number=@New_Trip_Number OUTPUT

rollback
end
</pre>

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following