syntax error with apostrophe and declare cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

syntax error with apostrophe and declare cursor

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 15:46:17

This statement works in a stored procedure I took a peak at in the database I'm working in:

SET @V_SQL = 'Declare new_cursor CURSOR FAST_FORWARD FOR
Select Column1, Column2, Column3, Column4
From Table1 where Column3 = ''' + @variable + ''' AND Column2 = ''' + @variable2 + ''' + @variable3 + '
Order by Column1'

First, I don't understand the convention of how to use the apostrophe for the variables.

What I need to figure out urgently is how to do the same thing, but with static values, doing something like this:

SET @V_SQL = 'Declare new_cursor CURSOR FAST_FORWARD FOR
Select Column1, Column2, Column3, Column4
From Table1 where Column3 = 'ABC' AND Column2 = 'DEF' + 'GHI'
Order by Column1'

Can anyone help me out?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-18 : 16:12:22

You need to escape the single quote by doubling it.


SET @V_SQL = 'Declare new_cursor CURSOR FAST_FORWARD FOR
Select Column1, Column2, Column3, Column4
From Table1 where Column3 = ''ABC'' AND Column2 = ''DEF'' + ''GHI''
Order by Column1'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-19 : 08:36:48

So if you're assigning field names with variables, as opposed to static values, it's two "'"s then. Thank you.

Kristen
Test

22859 Posts

Posted - 2007-10-19 : 08:40:47

Couple of other approaches to make it more "readable":

use CHAR(34) instead of '

Use REPLACE to double them up.

Say for example that ''ABC'' above was actually @SomeParameter, and that might contain a ' then

SET @V_SQL = 'Declare new_cursor CURSOR FAST_FORWARD FOR
Select Column1, Column2, Column3, Column4
From Table1 where Column3 = '
+ CHAR(34) + REPLACE(@SomeParameter) + CHAR(34) + ' AND ...'

We have a UserDefinedFunction that surrounds with ' and doubles-up any embedded '

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources