Hi, You misunderstand execute statement execute statement exdeute sql from e.g. variable and it can return data to INTO INTO is outside of select sql text
e.g. execute statement ‘select 1 from RDB$DATABASE’ INTO :MYPARAM or sql = ‘select 1 from RDB$DATABASE’; execute statement(:sql) INTO :MYPARAM look for samples in instalation directory regards, Karol Bieniaszewski From: [email protected] [firebird-support] Sent: Wednesday, April 25, 2018 10:33 PM To: [email protected] Subject: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO" 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; & nbsp; 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 --- Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast. https://www.avast.com/antivirus
