Vladimir Savić - 11 months ago 52
SQL Question

# Using parameter in PSQL

I have a problem with using parameter in psql.
How to properly use the parameter in where clause.
There are no errors compiling the procedure which I listed below.

CREATE PROCEDURE SELECTCATALOGUE (
TXT  VARCHAR(30))
RETURNS (
CODE VARCHAR(9),
NAME VARCHAR(50))
AS
BEGIN
for execute statement
'select code,name
from catalogue
where name='||:TXT
into :CODE,:NAME
do
suspend;
END


But, when I execute this procedure by replacing the parameter for example:

CREATE PROCEDURE SELECTCATALOGUE (
TXT  VARCHAR(30))
RETURNS (
CODE VARCHAR(9),
NAME VARCHAR(50))
AS
BEGIN
for execute statement
'select code,name
from catalogue
where name=''bla bla bla'''
into :CODE,:NAME
do
suspend;
END


I'm getting results properly. Am I missing something, how to make it work?

In your first version, you concatenate two strings which result in following string:

select code,name from catalogue where name=bla bla bla


You see that there is no quotes around the string bla bla bla, so the engine should treat it as a column name and usually this should result in a error like "column bla bla bla not found" or some such. If it happens that you send in an value which matches some column name then you would get empty resultset unless there is row(s) where the two columns have the same value.

To fix it, use parameterized statement:

for execute statement (
'select code,name
from catalogue
where name = :parName')
(parName := TXT)
into :CODE,:NAME
do


See the documentation for the full syntax of the execute statement.