0

I'm building a procedure into SQL Server, and for that I need to receive a table name as a variable, create a temporary table based on the table requested, search some substrings on each column of the given table, remove then, and return the result to the user. For that, I'm first tried to do that:

declare @TableName varchar(max)

select @TableName = 'xxxxxx'
select * into #temp from @TableName
select * from #temp

From this code, I've got the error:

Must declare the table variable "@TableName"

To use a table variable, I need to know the structure of said table beforehand, which I don't.

If I build the statement and execute it inside an EXEC() function, then the scope changes, and the temporary table will not be available for the outer process.

Is there a way to use the variable directly, or to declare this table variable dynamically?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Diego Campos Vaz dos Santos
  • 9
  • 2
  • When you use dynamic SQL you build a string to exec e.g. `exec('select * into #temp from ' + @TableName + '; select * from #temp');` and you can carry out multiple statements to avoid the context issue. – Dale K Jul 06 '22 at 02:27
  • 1
    Also referring to it as `a table variable` is misleading, because the variable is just a string... there are such things as table variables, and this isn't one. – Dale K Jul 06 '22 at 02:28
  • 3
    You have chosen a path to a really complicated implementation of dynamic sql - one that may challenge your abilities. So before you go down this path, think about why you made this decision. Why do you need to create a table and duplicate existing information? Can this all be done with a single query (even if more complicated than you might prefer)? Perhaps it can be done in a larger batch of multiple statements that you generate dynamically? It is concerning that your parameter can be any table name - are you expecting this will only be used with tables of similar structure? – SMor Jul 06 '22 at 02:41

0 Answers0