How to insert varchar variable in dynamic sql query

Question

I have a query with a select statement in a loop and at the same time I want to insert a varchar variable and selected value into a temporary table, but I get an error like:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'SP419001_SID'

This SP419001_SID is the value contained in the varchar variable @dbName.

This is my query:

CREATE TABLE #tempCounter 
(
    dbName1 varchar(max), 
    counterNumber1 int
)

DECLARE
    @counter INT = 1,
    @max     INT = 0,
    @dbName  VARCHAR(100),
    @count   INT = 0,
    @SQLTEXT VARCHAR(MAX),
    @counterNumber VARCHAR(10)

SELECT @max = COUNT(id) FROM #tempPnamePadd

WHILE @counter <= @max
BEGIN
    SET @dbName='';

    -- Do whatever you want with each row in your table variable filtering by the Id column
    SELECT @dbName = name 
    FROM #tempPnamePadd
    WHERE Id = @counter

    PRINT @dbName

    SET @SQLTEXT    =
    --SELECT distinct PN.NAME_FORMAT_CODE, NAME_BUSINESS, INDIVIDUAL_FIRST, A.ADDRESS_ID, A.ADDR_LINE_1, A.ADDR_LINE_2, A.ADDR_LINE_3, A.CITY, A.STATE
    'DECLARE @dbn VARCHAR(200)
    SET @dbn ='+ @dbName +';
    INSERT INTO #tempCounter 
    (dbname1, counternumber1)
    SELECT @dbn ,
         (SELECT  count(*)
                                FROM '+ @dbName +'.dbo.PRELA PR  
                            INNER JOIN '+ @dbName +'.dbo.PNAME PN  
                            ON PR.NAME_ID = PN.NAME_ID  
                            INNER JOIN '+ @dbName +'.dbo.PNALK NK  
                            ON PN.NAME_ID = NK.NAME_ID  
                            INNER JOIN '+ @dbName +'.dbo.PADDR A  
                            ON NK.ADDRESS_ID = A.ADDRESS_ID  
                            WHERE (NAME_FORMAT_CODE=''B'' and NAME_BUSINESS like ''%BN'') OR
                            (NAME_FORMAT_CODE <> ''B'' and INDIVIDUAL_FIRST = ''John'') OR
                            (ADDR_LINE_1=''WELLS STREET'' AND CITY=''HOLLYWOOD'' AND STATE=''IA'')

                            )
                            '

    --PRINT @SQLTEXT
    EXEC  (@SQLTEXT)

    SET @counter = @counter + 1
END

Make it easy to assist you, simplify your code. (minimal reproducible example.) — jarlh
– jarlh, Commented Sep 20, 2019 at 12:57
It's this bit: SELECT @dbn , you need quotes around the @dbn or it tries to select the db name as a column. — Richard Hansell
– Richard Hansell, Commented Sep 20, 2019 at 12:58

Thom A · Accepted Answer · 2019-09-20 13:19:39Z

This is very likely not the most efficient way to do this; most likely you should be using STRING_AGG or FOR XML PATH and STUFF to do this.

Anyway, you need to parametrise your variable, and quote your dynamic objects. This results in the below:

CREATE TABLE #tempCounter (dbName1 sysname,
                           counterNumber1 int);

DECLARE @counter int = 1,
        @max int = 0,
        @dbName sysname,
        @count int = 0,
        @SQLTEXT nvarchar(MAX),
        @counterNumber varchar(10);

SELECT @max = COUNT(id)
FROM #tempPnamePadd;

WHILE @counter <= @max
BEGIN
    SET @dbName = '';

    -- Do whatever you want with each row in your table variable filtering by the Id column
    SELECT @dbName = name
    FROM #tempPnamePadd
    WHERE Id = @counter;

    PRINT @dbName;

    SET @SQLTEXT = N'INSERT INTO #tempCounter 
    (dbname1, counternumber1)
    SELECT @dbn ,
         (SELECT  count(*)
                                FROM ' + QUOTENAME(@dbName) + N'.dbo.PRELA PR  
                            INNER JOIN ' + QUOTENAME(@dbName) + N'.dbo.PNAME PN  
                            ON PR.NAME_ID = PN.NAME_ID  
                            INNER JOIN ' + QUOTENAME(@dbName) + N'.dbo.PNALK NK  
                            ON PN.NAME_ID = NK.NAME_ID  
                            INNER JOIN ' + QUOTENAME(@dbName) + N'.dbo.PADDR A  
                            ON NK.ADDRESS_ID = A.ADDRESS_ID  
                            WHERE (NAME_FORMAT_CODE=''B'' and NAME_BUSINESS like ''%BN'') OR
                            (NAME_FORMAT_CODE <> ''B'' and INDIVIDUAL_FIRST = ''John'') OR
                            (ADDR_LINE_1=''WELLS STREET'' AND CITY=''HOLLYWOOD'' AND STATE=''IA'')

                            )
                            ';

    --PRINT @SQLTEXT
    EXEC sp_executesql @SQLTEXT, N'@dbn sysname', @dbn = @dbName;

    SET @counter = @counter + 1;
END;

Note I have also changed some of your data types.

Olivier Depriester · Accepted Answer · 2019-09-20 12:59:57Z

0

You did not quote around @dbName in your dynamic query. So instead of

 SET @dbn ='SP419001_SID';

you get

 SET @dbn =SP419001_SID;

Do

    'DECLARE @dbn VARCHAR(200)
    SET @dbn ='''+ @dbName +''';
    INSERT INTO #tempCounter 
    ...'

answered Sep 20, 2019 at 12:59

Olivier Depriester

1,6351 gold badge10 silver badges20 bronze badges

1 Comment

Thom A Over a year ago

This is wide open to injection. You should be properly quoting your values in you're going to inject them.

Collectives™ on Stack Overflow

How to insert varchar variable in dynamic sql query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related