Invalid column name

Christopher Jack 1,616 Reputation points
2022-04-19T13:25:02.727+00:00

Hi,

I have the following code

SET @sqlCommand = '
        select 
            * 
        from 
            #temptable 
        where 
        SALES_OFFICE not in
        (SELECT [SalesOfficeCode] from ' + (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init) 
        + '.[DimSalesOffice]) and sales_division = ' + (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init)

However I am receiving the error

Invalid column name

The error seems to be related to the last part when is doing the comparison

sales_division = ' + (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init)

The part above that pulls the row value fine? Any ideas?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jingyang Li 5,891 Reputation points
    2022-04-19T13:45:58.753+00:00

    Print our your sql string.
    My guess is this:
    and sales_division = ''' + (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init)+''''

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Christopher Jack 1,616 Reputation points
    2022-04-19T13:44:54.753+00:00

    Found the answer .. needed to add a few more quoates

    sales_division = ''' + (SELECT schema_name FROM #schema WHERE ROWNUMBER = @Init)+''''
    
    0 comments No comments