DYNAMIC SQL VERSUS STATIC SQL

[cristi22]

Hello all -

I've changed my sp to run dynamically with prepare statement & it does not return any results.
It runs fine in the regular procedure.

Any ideas, please???!!!
I think it has something to do with quotes…

CREATE PROCEDURE BASYS.UMASS_ADD_NK_test (
                                        in in_period date,
                              --          in tran_app char (6),
                                        in in_empl char (9)
                                        )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE p_in_empl varchar (200);


DECLARE stmt VARCHAR(4000);
DECLARE in_Employer_fmt VARCHAR(250);
DECLARE p_period varchar (50);
DECLARE cursor1 CURSOR WITH RETURN FOR s1;


if in_empl is null then
set stmt ='SELECT e.ssn, count( period) cnt FROM umass_contr_gr e WHERE e.period >= (date( ''' || CHAR(in_period)  || ''' ) - 5 months) '||
'and e.period<date( ''' || CHAR(in_period)  || ''' ) ' ||
'GROUP BY  e.ssn ' ||
' having count( distinct period)=5';
end if;

  PREPARE s1 FROM stmt;



    -- Cursor left open for client application
END P1

It does not return any results....

Here is the working statement w/o prepare:

SELECT
			                e.ssn,
			                 count( period) cnt
                        FROM
                            umass_contr_gr e
            			WHERE
            			   e.period >= (date( in_period ) - 5 months) and e.period<date( in_period )
            			
    	                GROUP BY  e.ssn

        			    having count( distinct period)=5

HELP PLEASE!!!!

 

[MarcLodge]

Hi Cristi,

First off, why are the 2 pieces of SQL so different? The dynamic version seems to be using CHAR and concatenating in something, yet the static one does not. Why?

Looking at your dynamic version where you seem to have the problem, I can't quite understand what you are trying to do. Are you trying to concatenate a space in front of the in_period and then one after it, because on my screen (and it may just be the way that it is formatted) there oesn't appear to be a space.

Let me know what you are trying to achieve.

Remember also that you are going to have problems with this because you are using two different languages, the SP procedure language and SQL. SQL will remove sucessive quotes together so if you want a single quote, you will need to supply 3. In the SP language, I think you would need to supply 5!

EG. Simply SQL
SELECT '''Hi Cristi''' FROM SYSIBM.SYSDUMMY1
would return
'Hi Cristi'

To get the same result from SP I don't think that:
set stmt ='SELECT '''Hi Cristi''' FROM SYSIBM.SYSDUMMY1'
would work. I think you would have to code something like:
set stmt = 'SELECT '''''Hi Cristi''''' FROM SYSIBM.SYSDUMMY1'

I've probably got the actual number of quotes needed wrong as I'm at home and unable to test, but hopefully you understand what I'm saying in that the two languages need lots of quotes as they each strip various one of them out.

Let me know how you get on.

Marc

 

[cristi22]

Hi all & thanks for your help; i did not have an open curson in my sp