executing a select stmt held in a variable 1

[tek2002]

i have this stored proc that accepts a parameter called @spParam which is of type char(40) and i have a variable called @SQL_TEXT which holds a select stmt 'select prog_NAME from tblArea where area = '

This select stmt btw is stored in a table that i am retrieving in the SP.

when i try to execute it using this code:

SELECT @SQL_TEXT = @SQL_TEXT + @spParam
EXEC(@sql_text)

i get this error message:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'AREA'.


i have a feeling it has soemthing to do with singe quotes - can anyone advise as to how i can go about fixing.

thanks

 

[SemperFiDownUnda]

You forgot to put the closing single quote on after concatinating the @spParam variable to the @SQL_TEXT variable.
Thus your select statement is looking like

select prog_NAME from tblArea where area = 'somevalue

notice there is no closing '



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[tek2002]

the select stmt actually looks like

'select prog_NAME from tblArea where area = ' somevalue

so the opening and closing quotes here is incorrect as i get a different error --> i need to place quotes around the value held in @spParam.....how can i do this programatically?

SELECT @SQL_TEXT = @SQL_TEXT + @spParam
EXEC(@sql_text)

thanks for your help.

 

[nigelrivett]

SELECT @SQL_TEXT = @SQL_TEXT + '''' + @spParam + ''''
EXEC(@sql_text)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.