Balancing quotes in SP

Database
Enthusiast

Balancing quotes in SP

Hi All,

    I have a SP like below.

replace procedure temp(in temp varchar(1000))
dynamic result sets 1
begin
declare stmt varchar(1000);
DECLARE C1 CURSOR
WITH    RETURN ONLY FOR STMT1;
set stmt = 'sel name from temp WHERE name  LIKE '''||temp||'''';
prepare stmt1 from stmt;
OPEN C1;
end;

And the DDL and insert for temp is

create volatile table temp (name varchar(1000)) on commit preserve rows;

insert INTO temp values ('asdf''s');

When i try to call, i need to escape the single quotes by giving like below else its failing.

call temp('asdf''''s')

is there any other way to escape the quotes while calling itself without touching the actual data

ex call temp ("asdf's")  -- Quotes just for illustration.

1 REPLY
Enthusiast

Re: Balancing quotes in SP

Any thoughts on this???