asked on

using string inside a sql string with EXECUTE sp_executesql

Hi

I have some sql that I am runing with EXECUTE sp_executesql . When I try to include a string within the sql I get an error "Invalid colum name 'my Msg1' ". But if I don't include a string but a number instead it works fine.

how do I do the first code snippet below ?

-- this does NOT work
SELECT @query =
'SELECT prev_HoleID, prev_dTo, dFrom, info, 
CASE WHEN ([prev_dTo] < [dFrom]) THEN "my Msg 1"  ELSE "my Msg 2" END -- this is the bit with a problem
FROM #StagCheck
WHERE	(NOT (gapCheck IS NULL)) AND (NOT (gapCheck = 0))
ORDER BY prev_HoleID, prev_dTo'
				
EXECUTE sp_executesql @query

-- this DOES work
SELECT @query =
'SELECT prev_HoleID, prev_dTo, dFrom, info, 
CASE WHEN ([prev_dTo] < [dFrom]) THEN 1 ELSE 0 END 
FROM #StagCheck
WHERE	(NOT (gapCheck IS NULL)) AND (NOT (gapCheck = 0))
ORDER BY prev_HoleID, prev_dTo'
				
EXECUTE sp_executesql @query

Open in new window

waltersnowslinarnold

As what have you declared, @query? varchar or int?

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Goodangel Matope

Its the kind of quotes you are using. You need to use two single quotes instead of one double quote to enclose your strings. See code snippet below that should work.

SELECT @query =
'SELECT prev_HoleID, prev_dTo, dFrom, info, 
CASE WHEN ([prev_dTo] < [dFrom]) THEN ''my Msg 1''  ELSE ''my Msg 2'' END -- this is the bit with a problem
FROM #StagCheck
WHERE   (NOT (gapCheck IS NULL)) AND (NOT (gapCheck = 0))
ORDER BY prev_HoleID, prev_dTo'

Open in new window

LukeB

ASKER

duh, thanks ... only thing i did not try !!

shru_0409

try this

SELECT @query =
"SELECT prev_HoleID, prev_dTo, dFrom, info, CASE WHEN ([prev_dTo] < [dFrom]) THEN "+char(34)+"my Msg 1"+char(34)+" ELSE "+char(34)+" my Msg 2"+char(34)+" END -- this is the bit with a problem
FROM #StagCheck
WHERE (NOT (gapCheck IS NULL)) AND (NOT (gapCheck = 0))
ORDER BY prev_HoleID, prev_dTo"

or

SELECT @query =
"SELECT prev_HoleID, prev_dTo, dFrom, info,
CASE WHEN ([prev_dTo] < [dFrom]) THEN 'my Msg 1' ELSE 'my Msg 2' END -- this is the bit with a problem
FROM #StagCheck
WHERE (NOT (gapCheck IS NULL)) AND (NOT (gapCheck = 0))
ORDER BY prev_HoleID, prev_dTo"