0

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 '.'.

Ram
  • 3,092
  • 10
  • 40
  • 56
Giuseppe Lolli
  • 167
  • 4
  • 15

1 Answers1

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does `''['' + COLUMN_NAME + '']''` really make sense in it's current form? I figured it should be something like `['' + COLUMN_NAME + '']` – Mackan Jul 06 '15 at 12:33
  • 1
    @Mackan Better use `QUOTENAME(COLUMN_NAME)` instead – TT. Jul 06 '15 at 15:11