How can you use a variable tablename and retrieve the output from the Insert?

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @.tTemp.

How can you use a variable table name and retrieve the output from the Insert?

declare @.tTestTable varchar (20)

set @.tTestTable = 'mis.test_sequence'

--DECLARE @.tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)

declare @.testsql varchar (4000), @.testseq int

DECLARE @.tTemp table (mainpk int)

set @.testsql = 'DECLARE @.tTemp table (mainpk int) INSERT ' + @.tTestTable + ' OUTPUT INSERTED.sqVal into @.tTemp VALUES (getdate() ) SELECT @.testseq=mainpk FROM @.tTemp'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output,@.tTemp table (mainpk int),@.tTemp table (mainpk int) ',@.tTemp,@.tTemp,@.testseq output,@.tTemp

SELECT * FROM @.tTemp

Please help

Thanks Tim.

Why not to create an sp per each table, instead trying to come with a general one?

Code Snippet

use tempdb

go

create table #t (c1 int not null identity, c2 datetime)

declare @.tTestTable varchar (20)

set @.tTestTable = '#t'

declare @.testsql nvarchar (4000), @.testseq int

set @.testsql = 'INSERT into' + quotename(@.tTestTable) + '(c2) values(getdate()); set @.testseq = scope_identity()'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output',@.testseq output

SELECT @.testseq

drop table #t

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Thankyou hunchback,

Your Code Snippet helped me solve my problem.

Tim.

Here's my final code.

USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc GetNext (@.sequenceName varchar(40) , @.nextVal int output)

as

begin

declare @.sqlStmt nvarchar (4000)

declare @.tTemp table (mainpk int)

set nocount on

-- This option works using the OUTPUT clause.

set @.sqlStmt = 'DECLARE @.tTemp table (mainpk int) Insert ' + @.sequenceName + ' OUTPUT INSERTED.sqVal into @.tTemp DEFAULT VALUES SELECT @.nextVal=mainpk FROM @.tTemp'

execute sp_executesql @.sqlStmt, N'@.nextVal int output',@.nextVal output

set nocount off

end