inserting single quote in dynamic sql

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

SQL Server Development (2000)

inserting single quote in dynamic sql

Author

Topic

kaus
Posting Yak Master

179 Posts

Posted - 2002-05-22 : 17:02:55

I have stored proc with
.....
Select @strSQL = @strSQL + ' and wells.well_code in (' + @well_code + ')'
Select @strSQL = @strSQL + ' and well_level.meas_date > "31-DEC-1979"'

it used to work -- but I re-compliled and its saying it cant find column 31-dec-1979. when I run it in query analyzer with single quotes it works. Is there any way I can use single quotes

Thanks

Pete

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-05-22 : 17:17:40

You can use the VB syntax of 'multiple quotes'. Try these.

Select @strSQL = @strSQL + ' and wells.well_code in (''' + @well_code + ''')'

Select @strSQL = @strSQL + ' and well_level.meas_date > ''31-DEC-1979'''

You can also try
SET QUOTED_IDENTIFIER { ON | OFF }
when you compile.
Otherwise, your default setting thinks that the double quote is a column name

kaus
Posting Yak Master

179 Posts

Posted - 2002-05-22 : 17:45:04

thanks -- I used two single quotes in place of the double and it worked great

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources