How to pass dynamic TABLE name to sp_executesql?

Question

Hi,

I want to pass table name through dynamically in sp_executesql. I am using below query and getting error, please guide me to resolve this.

 Code:

DECLARE @InsertString NVARCHAR(700);

Declare @PrmTableName NVARCHAR(200);

DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */

SET @InsertString =N'

select *, @newSnapshotId as newSnapshotId  from @TableName where snapshot_id= @oldSnapshotId'

/* Specify the parameter format one time. */

SET @ParmDefinition = N'@TableName varchar(200), @newSnapshotId varchar(40), @oldSnapshotId varchar(40)';

/* Execute the string with the first parameter value. */

SET @PrmTableName='Base_Data'

EXECUTE sp_executesql @InsertString, @ParmDefinition,

                      @TableName = @PrmTableName, @newSnapshotId = '09EB8E86-4B75-4F1C-9E13-D748A1EBF928', @oldSnapshotId = 'B69FD1AC-786C-43B3-9733-0ED744511439';

go

Error:

Msg 1087, Level 16, State 1, Line 2

Must declare the table variable "@TableName".

Regards

Deepak Goyal

Answer 1

declare @tablename nvarchar(100)

set @tablename='yrtable'

SET @InsertString =N'

select *, @newSnapshotId as newSnapshotId  from '+quotename(@TableName)+' where snapshot_id= @oldSnapshotId'

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

Correction: There was a serious bug in the code I posted as I missed the quotename with the tablename, the one which erland pointed..

---

Thanks and regards, Rishabh K

Answer 2

Hi Rishabh,

I had check the same but its not working.

In my case query has parsed but I am not getting result.

Regards

Deepak Goyal 

Answer 3

See this example please
declare @tab
set @tab='Tab1'
            set @sql='drop table '+@tab

            exec sp_executesql
                @sql

---

Many Thanks & Best Regards, Hua Min

Answer 4

Well you can't access table names as a parameter the way you are doing this. I would also suggest you to first use print() to see the sql being generated

---

Thanks and regards, Rishabh K

Answer 5

Hello,

Try this

DECLARE @InsertString NVARCHAR(700);
Declare @PrmTableName NVARCHAR(200);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @TableName varchar(200);

SET @PrmTableName='Base_Data';
SET @TableName=@PrmTableName;
/* Build the SQL string one time. */
SET @InsertString =N'
select *, @newSnapshotId as newSnapshotId  from '+ @TableName +' where snapshot_id= @oldSnapshotId'
/* Specify the parameter format one time. */
SET @ParmDefinition = N' @newSnapshotId varchar(40), @oldSnapshotId varchar(40)';

/* Execute the string with the first parameter value. */


EXECUTE sp_executesql @InsertString, @ParmDefinition,
                      @newSnapshotId = '09EB8E86-4B75-4F1C-9E13-D748A1EBF928', @oldSnapshotId = 'B69FD1AC-786C-43B3-9733-0ED744511439';

go

Regards
satheesh

Answer 6

I had check the same but its not working.

In my case query has parsed but I am not getting result.

Maybe there is no result to return, have you checked that?

In any case the code should be:

SET @InsertString =N'select *, @newSnapshotId as newSnapshotId  from ' + quotename(@TableName) + ' where snapshot_id= @oldSnapshotId'

Else funny thing can happen if @TableName contains special characters like space.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

can you check the string in next line before sp_executesql

as

print @InsertString

i think you will get the answer automatically.

Answer 8

Hi Erland,

I had again recheck and its working as Rishabh or you told.

But further I have a query for the similar thread.

In my case, I have 50+ table with similar type of column and datatype where I need to Select / Insert / Update on same column. Through above process, SQL Server create per execution plan per table. I want to create only one plan to save memory. 

Is it possible.. If yes then please guide me..

Regards

Deepak Goyal

Answer 9

Yes, it's possible, if you make all those 50+ tables into the one and same table. Else it is not.

In a relational database, each table is supposed to model a unique entity; you are not supposed to have 50 tables with the same structure. And the query optimizer is designed from this perspective. Each table is has own set of indexes and statistics, and even if two tables have exactly the same layout the same query against the two tables can yield different query plans, if the data is distributed differently.

Now, in your case I can understand from the names with snapshotid, that these columns serves as a helpers for some replication scenario. And, yes, in such situations you may have the same meta-column cropping up in multiple tables. Nevertheless, you probably don't want the same plan for all tables. For some tables, there may be only a handful of rows matching the snapshotid why a non-clustered index may come in handy. In another table, there may be lots of rows, why a table scan is better.

While it is true that you can litter the cache with query plans if you use unparameterised SQL, there is little reason to worry about 50 extra plans. That will only take up some tens of megabytes of memory.

Also, I don't know what this snapshot thing is, but if you are on SQL 2008, you might be interetsing to check out Change Tracking.

---

Erland Sommarskog, SQL Server MVP, [e-mail]