I forgot to mention I'm using firebird 1.5.5 1) The integer was a typo. 2) The stored procedure in your reply, it's looping through every record correct?
I guess my question wasn't very clear. What I'm trying to do is have conditional logic in the where clause. pseudo code: Select * from table T Where Case :input_code when 'A' then T.somefield = 'A' when 'B' then t.somefield = 'B' else /* else we want both types */ t.somefield = 'A' or t.somefield = 'B' end or select * from table T if :input_code = 'A' then where t.somefield = 'A' else if :input_code = 'B' then where t.somefield = 'B' else where t.somefield = 'A' or t.somefield = 'B' This is just a simplified example. There are multiple input parameters and the where clause will get more complex. Do I have to live with having the stored procedure check every record in my example? Daniel -----Original Message----- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Alan J Davies Sent: Wednesday, October 31, 2012 10:19 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] sp case statement No. You have some errors in the logic and data types. You can try this. You will get one record returned for every record in your table. CREATE OR ALTER PROCEDURE GET_DATA ( Input_code char(1) ) RETURNS ( Output_code char(1) ) /* you have an integer which will fail */ as begin for select case when :input_code='A' then 'A' when :input_code='B' then 'B' else 'C' end from table into :output_code do suspend; end Alan J Davies Aldis On 31/10/2012 21:38, Matchey,Brian wrote: > > > Is there a way to achieve the logic I'm trying to accomplish with the > CASE statement in the WHERE clause below? > > CREATE OR ALTER PROCEDURE GET_DATA ( > Input_code char(1) ) > RETURNS ( > Output_code integer ) > as > begin > FOR SELECT T.Output_code > FROM Table T > WHERE T.Input_code = > case :Input_code when 'A' then 'A' > when 'B' then 'B' > else (('A') or ('B')) > end > INTO :Output_code > DO > SUSPEND; > end; > > Vertafore > Unleash your potential > > Daniel Raith > Software Engineer II > 26550 W. Mondovi St, Eleva, WI 54738 > T 800 433 2550 Ext 2917 | F 805 553 9308 | dra...@vertafore.com > <mailto:draith%40vertafore.com><mailto:dra...@vertafore.com > <mailto:draith%40vertafore.com>> > vertafore.com > > [Non-text portions of this message have been removed] > > ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links