any Acer for Stored Procs ? How to escape quotes in SP parameter. PL HELP

Database

any Acer for Stored Procs ? How to escape quotes in SP parameter. PL HELP

Hi Folks
I created a Stored procedure that can take a string as a parameter.It all looks good EXCEPT for the part where the string contains commas and single quotes that must be escaped.

<> How can you escape the ',' and ' ' ' ( single quote ) character. Tried the \ does not work. Tried Escape \ does not work. Tried ".......string" c does not work.
<> How can you pass as parameter, a string that has something like
' blah blah , account=('$d$metc') ;'

to a stored procedure.
Any help is appreciated.
Thanks
Samuel Carpenter
3 REPLIES
rgs
N/A

Re: any Acer for Stored Procs ? How to escape quotes in SP parameter. PL HELP


' blah blah , account=(''$d$metc'') ;'

That is two single quotes to escape a quote. You don't need an escape for a comma.

Re: any Acer for Stored Procs ? How to escape quotes in SP parameter. PL HELP

Hi rgs
what if the sp has parameters like

call xyz ('p1','p2' ) only
and p2 is a long string='blah=1 , blah2=2 , paramater1=('Merry Xmas'), parameter2="Somelse '
I'd like to pass the string as the parameter after conveying to the SP that just take this entire thing as a string and don't bother to parse it any more. How could I do that
rgs
N/A

Re: any Acer for Stored Procs ? How to escape quotes in SP parameter. PL HELP

I don’t understand your difficulty or maybe your question. Suppose the stored procedure has two parameters such as

(IN p1 VARCHAR(100), IN p2 VARCHAR(100))

You just supply the strings to the two parameters. If the a string has a single quote in it, you just replace the single quote with two single quotes. It’s that simple.

So if I have: p2 as a long string:

‘blah=1 , blah=2 , parameter1=(‘Merry Xmas’), parameter2=”Somelse ’

and you want to input that as one string as parameter p2 you just type this instead:

‘blah=1 , blah=2 , parameter1=(‘‘Merry Xmas’’), parameter2=”Somelse ’

Note, that Merry Xmas is surrounded by two single quotes: To make it clear let me type the string replacing the single quote character with QUOTE so you can see what is being done.

QUOTEblah=1 , blah=2 , parameter1=(QUOTEQUOTEMerry XmasQUOTEQUOTE), parameter2=”Somelse QUOTE

or if you want the whole thing to be passed as a quoted string to the SP:

QUOTEQUOTEQUOTEblah=1 , blah=2 , parameter1=(QUOTEQUOTEMerry XmasQUOTEQUOTE), parameter2=”Somelse QUOTEQUOTEQUOTE