Running an INSERT statement a @variable without using EXEC

The Code-Piece below does not work because @.Tablename is not an objekt.

=============================================

DECLARE @.Tablename varchar(256)

set @.Tablename = 'MyTable'

insert into @.Tablename default values

=============================================

How can i make it work without using EXEC?

Thanks in advance

Raimund

You have to use dynamic SQL (which is what I presume you mean by using EXEC). I don't think there is any other way.|||If I use scope_identity with an insert-statement by dynamic SQL, scope_identity reurns NULL;|||

If you put the Scope_identity call into the dynamic sql you will be able to capture it to a variable. That variable can then be returned if you use the sp_executesql form of dynamic sql.

|||

You need to call the identity in the dynamic SQL

declare @.tablename varchar(100)

declare @.sql nvarchar(1000)

declare @.i int

set @.tablename = 'mytable'

set @.sql = 'insert into ' + @.tablename + ' default values

set @.i = scope_identity()'

exec sp_executesql @.sql, N'@.i int output', @.i output

select @.i

|||This does mean you can put this code in a UDF|||

Ok. It works fine.

Thanks.

Best Regards

Raimund