[firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"

[blackfalconsoftw...@outlook.com [firebird-support]]

Hello...

I have been doing fairly well at converting a project's database to Firebird 
Embedded, transposing all of the inline SQL to Firebird stored procedures.  
However, with the following procedure I am creating a slightly
complex SELECT statement based upon two incoming parameters.

It seems that no matter how I try to implement the "INTO" clause against the 
return variable, when I run the procedure from my DB-Manager it yields an 
error...

>>>
>>> procedure sql
>>>
CREATE PROCEDURE SP_GET_MSGLOG_REC_COUNT(
  PS_DATE VARCHAR(10),
  PS_MSG_TYPE CHAR(1))
RETURNS(
  PI_CNT INTEGER NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
  PI_CNT = 0;    

  PS_SQL = 'SELECT COUNT(*)';
  PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
 
  IF (CHAR_LENGTH(TRIM(:PS_DATE)) > 0) THEN
      BEGIN
          PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM 
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
        PS_SQL = PS_SQL || '       ' || '(TRIM(CAST(EXTRACT(DAY FROM 
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
        PS_SQL = PS_SQL || '       ' || '(TRIM(CAST(EXTRACT(YEAR FROM 
ML_CREATE_DATE) AS VARCHAR(4)))) = ' || :PS_DATE;
      END
  
  IF (CHAR_LENGTH(TRIM(:PS_MSG_TYPE)) > 0) THEN
      BEGIN
      If (POSITION('WHERE' IN PS_SQL) > 0) THEN
          PS_SQL = PS_SQL || '   AND ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
      ELSE
        PS_SQL = PS_SQL || ' WHERE ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
    END
  
  --PS_SQL = PS_SQL || ' INTO ' || :PI_CNT;
  PS_SQL = PS_SQL || ' INTO :PI_CNT';    
  
  EXECUTE STATEMENT (:PS_SQL);
  
  SUSPEND;
END;
<<<

If someone could take a look at this code and give me an idea as to where I am 
going wrong it would very much appreciated...

Thank you...

Steve Naidamast