Hi,
I executed the below select stmt alone and gives the corect result
select pq5.incdnt_all.usrid from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = 'Incdnt_ID'

But when I execute it through store procedure like below , it returns multiple row.
DECLARE USERID INTEGER DEFAULT 0;
SET USERID=(select pq5.incdnt_all.usrid from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = Incdnt_ID);

How is it possible? Please provide some solution to resolve this issue.

Thanks
Surjya

Your subselect is equivalent to which obviously returns all rows in the table. You should use some kind of naming convention that distinguishes table column names from variable and parameter names.

Are you sure it returns multiple rows, or are you just getting an error message that it might return multiple rows and therefore cannot be used in the context of statement you are attempting to use it in?

Yes instead of retriving one column I sued
SET USERID=(select count(*) from pq5.incdnt_all where pq5.incdnt_all.incdnt_id = IncdntID);
It gives total number of records in that table instead of for the particular incdnt_id. incdnt_id is primary key in that table.

Thanks
Surjya

I think your question is same as this thread for which I already answered same as Nick Ivanov's answer.

error while selecting

If you look at the 2 queries closely, you'll see that both are NOT identical. In one case, you compare with a string with the content "'Incdnt_ID'" and in the other case you use the content of the variable named Incdnt_ID because you don't have any quotes around the "Incdnt_ID".