Subject | RE: [firebird-support] SP Parameter as sql statement ? |
---|---|
Author | Martin Dew |
Post date | 2005-04-21T14:51:14Z |
Thanks to Martijn and yourself Helen for headsup on the EXECUTE
STATEMENT angle,
CREATE PROCEDURE TEST3 (
WHERESTATEMENT1 VARCHAR(250)
) RETURNS (
TOTAL INTEGER
) AS
BEGIN
FOR EXECUTE STATEMENT
'SELECT count(l.urn) FROM log l left outer join patient p on (l.urn =
p.log_urn) WHERE p.area = ' || wherestatement1 INTO :TOTAL
DO BEGIN
SUSPEND;
END
END
SELECT * FROM TEST3('NHSD')
Only results in column unknown errors. Obviously I need a way to send
the 'NHS' instead of just NHSD to identify it as a value, I am sure this
works well for dynamically parsing tablenames or fieldnames but for
parsing string text values it appears to be limited. ? Although I am
probably just not doing it correctly ???
T.I.A
Martin
________________________________
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 21 April 2005 15:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SP Parameter as sql statement ?
At 10:29 AM 21/04/2005 -0400, you wrote:
would
be relatively straightforward (no hackery).
CASE statement.
./heLen
________________________________
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe
Service <http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]
STATEMENT angle,
CREATE PROCEDURE TEST3 (
WHERESTATEMENT1 VARCHAR(250)
) RETURNS (
TOTAL INTEGER
) AS
BEGIN
FOR EXECUTE STATEMENT
'SELECT count(l.urn) FROM log l left outer join patient p on (l.urn =
p.log_urn) WHERE p.area = ' || wherestatement1 INTO :TOTAL
DO BEGIN
SUSPEND;
END
END
SELECT * FROM TEST3('NHSD')
Only results in column unknown errors. Obviously I need a way to send
the 'NHS' instead of just NHSD to identify it as a value, I am sure this
works well for dynamically parsing tablenames or fieldnames but for
parsing string text values it appears to be limited. ? Although I am
probably just not doing it correctly ???
T.I.A
Martin
________________________________
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 21 April 2005 15:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SP Parameter as sql statement ?
At 10:29 AM 21/04/2005 -0400, you wrote:
>You won't be able to go about this the way you are trying. If you areNot feasible without EXECUTE STATEMENT...and using EXECUTE STATEMENT
>doing it for speed, you can make some evil hackery by passing in the
>param name (or some sort of identifier)
would
be relatively straightforward (no hackery).
>and having a big fat CASE statement.Having a big heap of IF..ELSEs is what you intended, right? PSQL has no
CASE statement.
./heLen
________________________________
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe
>* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]