CODEWITHSUNDEEP
sqlsql-servert-sqlsp-executesql
Giuseppe Lolli
Giuseppe Lolli

Reputation: 177

Dynamic Query does not work

I am trying to do a dynamic query passing to it parameters, but it doesn't work:

DECLARE @ASA_EXPORT_TABLE NVARCHAR(MAX) = 'EXPORT_PRODUCT'
DECLARE @ASADB_NAME NVARCHAR (MAX) = 'WHITESTUFF_WSSI_7700_ASA'
DECLARE @TOP_1 NVARCHAR(MAX)
SET @TOP_1 = '
        SELECT TOP 1 ''['' + COLUMN_NAME + '']''
        FROM @AsaDb_Name_IN.INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = ''@ASA_EXPORT_TABLE_IN''
        '
EXEC sp_executesql @TOP_1, N'@ASA_EXPORT_TABLE_IN NVARCHAR(MAX), @AsaDb_Name_IN NVARCHAR (MAX)', 
@ASA_EXPORT_TABLE_IN = @ASA_EXPORT_TABLE, @AsaDb_Name_IN = @AsaDb_NamE

The error message is:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can't specify a database name, table name, or column name as a parameter. The rules for parameters are only for constants in the query.

So, this should work:

DECLARE @ASADB_NAME NVARCHAR(MAX) = N'WHITESTUFF_WSSI_7700_ASA';
DECLARE @TOP_1 NVARCHAR(MAX);
SET @TOP_1 = '
        SELECT TOP 1 ''['' + COLUMN_NAME + '']''
        FROM @AsaDb_Name_IN.INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = ''@ASA_EXPORT_TABLE_IN''
        ';
SET @TOP_1 = REPLACE(@TOP_1, '@AsaDb_Name_IN', @AsaDb_NamE);

EXEC sp_executesql @TOP_1, N'@ASA_EXPORT_TABLE_IN NVARCHAR(MAX)', 
    @ASA_EXPORT_TABLE_IN = @ASA_EXPORT_TABLE;

Upvotes: 1

Related Questions