CODEWITHSUNDEEP
sql-servercommon-table-expression
GLP
GLP

Reputation: 3675

How to use variable table name in CTE

I have a CTE defined in my stored procedure, the table name used in the CTE is a variable, for example

declare @tblName  char(50)
set @tblName =’…’

;WITH tbl1_CTE AS
(
    SELECT *
    FROM @tblName
    …
)
select * from tbl1_CTE

However I got an error, Must declare the table variable "@tblName". Is there any way to solve this?

Upvotes: 0

Views: 2615

Answers (2)

Rajesh
Rajesh

Reputation: 1620

As your error Specified the @tblname is a Variable but you are using it like a Temp Table and selecting from it Select * from @tblname we can only use like

Select @tblname

if it is a Variable

Upvotes: 0

spencer7593
spencer7593

Reputation: 108450

The only way to solve this is to use dynamic SQL. For example, the sp_executesql procedure. (There are a couple of other SQL Server mechanisms that will do essentially the same thing.

The issue with what you have now is that the value of the variable @tblName won't be evaluated until "execute" time.

But before SQL Server can "execute" the statement, SQL Server first has to "prepare" the statement, and as part of that, to figure out whether the referenced table (or view) exists, if the user has privileges on it, what indexes are available, an so on, in order to come up with an execution plan. Once the execution plan for the statement is prepared, then the statement can actually be executed. And it's at this point that the variables will get substituted in.

But, SQL Server needs to know the name of the table before it gets to the point where variables are substituted.

The workaround is to use "dynamic SQL". With that approach, the variables, like @tblName will get evaluated into the contents of a string. The trick then is to submit that string to SQL Server as a statement. And now, the table name will be a literal within the string.

Upvotes: 3

Related Questions