CODEWITHSUNDEEP
sqlsql-servert-sql
dtjones
dtjones

Reputation: 941

T-SQL: Using parameter for table name in stuff

I would like to generate sql code using the 'stuff' function based on a table name as a parameter

This works: 

declare @sql as nvarchar(max);

select @sql = stuff((SELECT distinct [Site]
                    FROM [ProcterGamble_analytics].[dbo].DATA_table
                    FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1,  0, '');

exec(@sql);

and i'm looking to do something like 

declare @presql as nvarchar(max), @sql as nvarchar(max), @table as nvarchar(max);

SET @table = 'DATA_table';


select @presql = 'SELECT distinct [Site]
                    FROM [ProcterGamble_analytics].[dbo].' + @table

select @sql = stuff((@presql
                    FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1,  0, '');

exec(@sql);

Upvotes: 1

Views: 2576

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Your sql statement is confused as to what is a string and what is the code that generates the string. I think this will work:

select @sql = 'select stuff((' + @presql + '
                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  0, '''')';

When you execute @sql, it should return the value.

Upvotes: 2

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

I think you are confusing creating XML versus concatenate distinct sites with a delimiter and using the stuff function to replace the first delimiter.

I usually like writing code that can be reusable. I am passing the database, schema, table and column names in as input. I am using SYSNAME as the preferred variable.

Noticed I used a group by instead of distinct. In this example, they are the same. However, group by can sometimes save you time.

Check out the processing instruction command. Dumps the dynamic string to a XML document. Real cool?

--
-- Generate a list in XML
--

-- Pass variables w/input
DECLARE @my_database SYSNAME = 'AdventureWorks2012';
DECLARE @my_schema SYSNAME = 'Production';
DECLARE @my_table SYSNAME = 'Product';
DECLARE @my_column SYSNAME = 'Name';

-- Create the dynamic SQL (xml output_)
DECLARE @sql_stmt1 varchar(max) = '';
SET @sql_stmt1 += 'SELECT [' + @my_column + '] FROM [' + 
    @my_database + '].[' + @my_schema + '].[' + @my_table + '] ';
SET @sql_stmt1 += 'GROUP BY [' + @my_column + '] ';
SET @sql_stmt1 += 'ORDER BY [' + @my_column + '] ';
SET @sql_stmt1 += 'FOR XML PATH (''''), ROOT('''+ @my_column +  's'')';

-- Cool instruction ?
SELECT @sql_stmt1 as [processing-instruction(TSQL)] FOR XML PATH 

-- Show my data
EXEC(@sql_stmt1);

In short, here is the output using the adventure works as a example. Just change the input for your case.

enter image description here

Just in-case you wanted a delimited list, I repeated the code using a derived table D and apply the STUFF() function to the resulting field X.

--
-- Generate a delimited list
--

-- Pass variables w/input
DECLARE @my_database SYSNAME = 'AdventureWorks2012';
DECLARE @my_schema SYSNAME = 'Production';
DECLARE @my_table SYSNAME = 'Product';
DECLARE @my_column SYSNAME = 'Name';

-- Create the dynamic SQL
DECLARE @sql_stmt varchar(max) = '';
SET @sql_stmt += 'SELECT STUFF(X, 1, 1, '''') AS LIST FROM ';
SET @sql_stmt += '( SELECT '','' + [' + @my_column + 
    '] FROM [' + @my_database + '].[' + @my_schema + '].[' + @my_table + '] ';
SET @sql_stmt += 'GROUP BY [' + @my_column + '] ';
SET @sql_stmt += 'ORDER BY [' + @my_column + '] ';
SET @sql_stmt += 'FOR XML PATH ('''') ) AS DT (X) ';

-- Cool instruction ?
SELECT @sql_stmt as [processing-instruction(TSQL)] FOR XML PATH 

-- Show my data
EXEC(@sql_stmt);

enter image description here

I hope this answers your question, if not, please write back.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

You don't really need the @presql portion, just need to double up single quotes so they are handled properly when the dynamic portion is processed:

DECLARE @sql AS NVARCHAR(MAX)
       ,@table AS NVARCHAR(MAX) = 'DATA_table';

SET @sql = 'stuff(( SELECT distinct [Site]
                    FROM [ProcterGamble_analytics].[dbo].' + @table + '
                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  0, '''')';

EXEC(@sql);

A good way to test dynamic SQL is to use PRINT(@sql); insted of EXEC to confirm the code that will be executed is what you want it to be.

Upvotes: 1

Related Questions