Re: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"
25.04.2018 22:33, blackfalconsoftw...@outlook.com [firebird-support] wrote: > 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... INTO belongs to EXECUTE STATEMENT clause, not to executed SQL. Read Language Reference. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"
Thank you very much for your help, Karol... As soon as I corrected my procedure based on your information, it worked as expected. Thank you, again... Sincerely, Steve Naidamast
Re: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"
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: blackfalconsoftw...@outlook.com [firebird-support]
Sent: Wednesday, April 25, 2018 10:33 PM
To: firebird-support@yahoogroups.com
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
[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;
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
