I've confused myself with some dynamic sql. HELP!!!!

[timfoster]

I seem to have gotten myself into a bit of a tangle with this bit of dynamic sql. Can anyone look at it with fresh eyes and clear it up for me?

The statement is stored in a table as a string. It's actaully a string that runs against a Postgres database using OPENQUERY. The table is read and each statement executed in turn.

My statement needs to have a series of ' (single quote) in it. My problem is I can't work out how many I need and where for it to execute properly.

The 'complied' version should be:

select id, trim(leading '<searchdate>' from substring(demographicdata from POSITION('<searchdate>' IN demographicdata) for POSITION('<searchdate>'
IN demographicdata)-POSITION('<searchdate>' IN demographicdata))) as searchdate from xmltable

This works fine when I run it on the Postgres box. Obviously when I run it from SQL I have it as a string to pass to OPENQUERY. This is where my problem comes. I can't get the right mix of single quotes to force SQL to pass the correct string to Postgres.

Any help would be appreciated. I've been staring at this for absolutely ages now.

Thanks folks.

 

[markros]

You just need to double the singe quote, e.g.

declare @SQL = replace(myField,char(39),char(39) + char(39)) --contains select id, trim(leading '<searchdate>' from substring(demographicdata from POSITION('<searchdate>' IN demographicdata) for POSITION('<searchdate>'IN demographicdata)-POSITION('<searchdate>' IN
demographicdata))) as searchdate from xmltable

also take a look at QuoteName function - very helpful for SQL.

And take a look at my friend's explanation of dynamic SQL here

http://www.sqlusa.com/bestpractices2005/quotename/

PluralSight Learning Library

 

[djj55]

Thanks markros for the "new" function. I have had the quote problem in the past. Also with the settings I have for FF the font on the link is painful for an old guy.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[markros]

Are you saying you have problems reading that link? I'll tell Kalman...

There are many other good links about dynamic SQL - I didn't post them yesterday, but here they are

The Curse and Blessings of Dynamic SQL

and also

http://www.sommarskog.se/dyn-search.html

PluralSight Learning Library