Skip to content
Advertisement

How to use scalar variable in XML Path query SQL?

I want to pass table name into into a scalar variable @Table_Name as a string. But I am getting error as:

Must declare the table variable @Table_Name.

How to achieve the same? Below is my code snippet:

Declare @Table_Name as varchar(200);
SET @Table_Name = 'Table_name'
SET @sql = '(SELECT' + STUFF((SELECT *
                                FROM @Table_Name
                                FOR XML PATH('')), 1, 1, '' )
               + ' FROM SomeOtherTable'

Advertisement

Answer

So I created a temporary table using the input provided by the user and used that temp table in the query. Following is a code snippet:

Declare @Table_Name as varchar(200);
Declare @sql as nvarchar(max);
SET @Table_Name = 'Table_name'
SET @sql = 'select * into ##TempTable from '+ @Table_Name

SET @sql = '(SELECT' + STUFF((SELECT *
                                FROM ##TempTable
                                FOR XML PATH('')), 1, 1, '' )
               + ' FROM SomeOtherTable'
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement