My query:
SET @s_query =
'Select ROW_NUMBER() OVER (ORDER BY ' + @ColNames + ') AS ##RowNum,
'+@ColNames+' INTO ##Results' +
' From TableA
Where FirstName = ' +@Search+ '
ORDER BY FirstName';
I am running the stored procedure with parameters:
@Search = 'Adam', @ColName = 'FirstName','LastName'
And getting the error:
" Invalid column name 'Adam'.
Looks like you're just not quoting your string. The way your code comes out, to SQL it looks like:
Where FirstName = Adam
But you want it to look like:
Where FirstName = 'Adam'
So you'd want to change that line of your code to give it the single-quotes it needs. See the modified codeset below:
SET @s_query =
'Select ROW_NUMBER() OVER (ORDER BY ' + @ColNames + ') AS ##RowNum,
'+@ColNames+' INTO ##Results' +
' From TableA
Where FirstName = ''' +@Search+ '''
ORDER BY FirstName';
Now your query will read:
Select ROW_NUMBER() OVER (ORDER BY FirstName, LastName) AS ##RowNum,
FirstName, LastName INTO ##Results From TableA
Where FirstName = 'Adam'
ORDER BY FirstName, LastName
The problem appears to be where you specify the criteria for FirstName, you have:
Where FirstName = ' +@Search+ '
Which will literally be translated to:
Where FirstName = Adam
Note the missing quote around the search criteria Adam.
Try this instead, to ensure the additional quotes are included:
Where FirstName = ''' +@Search+ '''
Which will finally give:
Where FirstName = 'Adam'
Others have already pointed out the missing quotes around the string criteria (= Adam, instead of = 'Adam').
I use a different technique to build the SQL to call, by constructing a template and replacing "parameters":
declare @s_QueryTemplate =
'
Select ROW_NUMBER() OVER (ORDER BY <<ORDER BY COLUMN NAMES>>) AS ##RowNum, <<COLUMN NAMES>>
INTO ##Results
From TableA
Where FirstName = ''<<SEARCH VALUE>>''
ORDER BY FirstName
'
SET @s_query =
REPLACE(
REPLACE(
REPLACE(
@s_QueryTemplate
, '<<ORDER BY COLUMN NAMES>>', @ColNames
), '<<COLUMN NAMES>>', @ColNames
), '<<SEARCH VALUE>>', @Search
)
I find this makes it much easier to see the structure of the code you're trying to create, including things like where there should be quotes.
@ColName = '''FirstName','LastName'''? If that's not the prob please paste the full sql and dump of @s_query.