Passing table name as parameter in dynamic SQL

sqlnewbie 0
Viewed 27

I've seen a few posts related to this topic but can not seem to find the solution that works for me. At the most basic level, I'm trying to create a piece of code that will allow me to replicate the same query for a bunch of different tables. I initially attempted a stored procedure (couldn't make it work) and am now trying as dynamic sql. In the below code I want to be able to swap in/out different tables in the @tablename.Each run I get a "Invalid Column Name '(Table Im trying to pass through)' Are there limitations I am not considering that would prevent me from doing this? Thanks!

declare @tablename varchar(1000)


declare @summary varchar(max)


set @tablename= 'table1'


set @summary = N'

drop table if exists #SUMMARY_TABLE;


SELECT

        COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE

    into #SUMMARY_TABLE

    FROM

        INFORMATION_SCHEMA.COLUMNS

    WHERE

        TABLE_NAME = '+@tablename+'

    ORDER BY 2'

exec(@summary)
tablename dynamic-sql stored-procedures sql
1 Answers

When troubleshooting any dynamic string problem, you first print out the string and inspect it.

When you print it you'll find the issues is here TABLE_NAME = '+@tablename+'

This is not valid:

SELECT
        COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
    into #SUMMARY_TABLE
    FROM
        INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = xyz

Your table name needs quotes around it. Use this syntax to add more quotes

'SELECT  ...........
 ..........
WHERE
TABLE_NAME = '''+@tablename+''' order by 2'

Again I reiterate, basic troubleshooting here is to print out the SQL:

PRINT (@summary)
Nick.McDermaid0
Related