DB2 stored procedure - error in dynamic sql

murugan_mf · Posted: Mon Jun 08, 2009 2:04 pm

I'm facing a strange problem is db2 stored procedure. A dynamic query is written in the SP and it throws sqlcode -206 at runtime. The query has 6 conditions in where clause and the error throws in one of the condition. When the value is hard coded instead of getting from i/p parameter then the SP executes correctly.
Example: IF v_first_value = 'N' THEN
SET v_query = v_query || ' AND A.ST_AD = ' || in_st_ad;
ELSE
SET v_first_value = 'N';
SET v_query = v_query || ' WHERE A.ST_AD = ' || in_st_ad;
END IF;
The query fails in this condition at run time. If i change
A.ST_AD = ' || in_st_ad to A.ST_AD = ''CA''' then it executes correctly.

Also when the query is changed to static, then the SP executes successfully even if the value for the column is got from i/p
parameter.
Is there any solution for this problem?

murugan_mf · Posted: Mon Jun 08, 2009 4:02 pm

Missed quotes in dynamic sql and its working now... Thanks for those who looked into this.

Anuj Dhawan · Posted: Mon Jun 08, 2009 4:11 pm

Glad to hear that it's working now and thanks for the follow-up ...