T-SQL: Using parameter for table name in stuff

Question

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);

Gordon Linoff · Accepted Answer · 2014-01-07 19:29:32Z

2

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.

edited Jan 7, 2014 at 19:29

answered Jan 7, 2014 at 17:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CRAFTY DBA Over a year ago

How does this work? There has to be a SELECT statement on the outside of all this stuff!

Gordon Linoff Over a year ago

@CRAFTYDBA . . . You know, I was operating from the perspective that the first query worked. But as written, this is only partial.

CRAFTY DBA Over a year ago

I solved it both ways with parameters that can be changed. He should be all set. The only reason to use stuff is remove the front delimiter. TTYL

CRAFTY DBA Over a year ago

PS - that processing instruction command is my new friend. I say it used by a MVP a couple of months ago, it went into the tool bag!

Hart CO · Accepted Answer · 2014-01-07 17:35:31Z

1

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.

answered Jan 7, 2014 at 17:35

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Comments

CRAFTY DBA · Accepted Answer · 2014-01-07 18:43:50Z

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.

Collectives™ on Stack Overflow

T-SQL: Using parameter for table name in stuff

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related