Scope identity on a stored procedure in SQL
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
December 10, 2008 7:54 PM
UPDATED:
December 11, 2008 3:54 AM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
