Case Statement and variables passed to stored procedures

Case Statement and variables passed to stored procedures

Post by Joe Hartma » Fri, 17 Nov 2000 04:00:00



I am trying to use a stored procedure to return data based on a parameter
that is passed to the procedure.  I effectivly have the following 3 cases
(CurrentStatus is the parameter passed into the procedure):

CurrentStatus = "OPEN"
    Select
      PCMSNumber, SubSystem, Status
    From
      spn W
    Where
        Status in ('IN_REVIEW','PENDING_APPROVAL')

CurrentStatus = "CLOSED"
    Select
      PCMSNumber, SubSystem, Status
    From
      spn W
    Where
        Status in not in ('IN_REVIEW','PENDING_APPROVAL')

--Could changed the above to be in ('Choice 3','Choice 4')

CurrentStatus = "ALL"
    Select
      PCMSNumber, SubSystem, Status
    From
      spn W

Due to other reasons, I don't want to just repeat the query three times.  Is
there some way to do something like this in a single query.

 
 
 

Case Statement and variables passed to stored procedures

Post by sgcummi.. » Fri, 17 Nov 2000 04:00:00


You could do something like this: (caution: syntax may be slightly off,
but the idea is there)



                 'Where Status in (''IN_REVIEW'',''PENDING_APPROVAL'')'
                    else
                        null
                    end;

  execute immediate 'Select PCMSNumber, SubSystem, Status From spn W '

Experiment with the CASE statement I think you'll find a way to do what
you are looking to do.

HTH,
Steve Cummings



Quote:> I am trying to use a stored procedure to return data based on a
parameter
> that is passed to the procedure.  I effectivly have the following 3
cases
> (CurrentStatus is the parameter passed into the procedure):

> CurrentStatus = "OPEN"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W
>     Where
>         Status in ('IN_REVIEW','PENDING_APPROVAL')

> CurrentStatus = "CLOSED"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W
>     Where
>         Status in not in ('IN_REVIEW','PENDING_APPROVAL')

> --Could changed the above to be in ('Choice 3','Choice 4')

> CurrentStatus = "ALL"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W

> Due to other reasons, I don't want to just repeat the query three
times.  Is
> there some way to do something like this in a single query.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Case Statement and variables passed to stored procedures

Post by Tony Rogerso » Fri, 17 Nov 2000 04:00:00


Personally, I'd use the EXEC( '.....' ) statement to execute a previously
built sql string.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group]


Quote:> I am trying to use a stored procedure to return data based on a parameter
> that is passed to the procedure.  I effectivly have the following 3 cases
> (CurrentStatus is the parameter passed into the procedure):

> CurrentStatus = "OPEN"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W
>     Where
>         Status in ('IN_REVIEW','PENDING_APPROVAL')

> CurrentStatus = "CLOSED"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W
>     Where
>         Status in not in ('IN_REVIEW','PENDING_APPROVAL')

> --Could changed the above to be in ('Choice 3','Choice 4')

> CurrentStatus = "ALL"
>     Select
>       PCMSNumber, SubSystem, Status
>     From
>       spn W

> Due to other reasons, I don't want to just repeat the query three times.
Is
> there some way to do something like this in a single query.

 
 
 

Case Statement and variables passed to stored procedures

Post by Doo » Fri, 17 Nov 2000 04:00:00


EXEC sp_executesql &SQLString will perform better since it has a better
chance of being cached.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group


> Personally, I'd use the EXEC( '.....' ) statement to execute a previously
> built sql string.

> --
> Tony Rogerson SQL Server MVP
> Torver Computer Consultants Ltd
> www.sql-server.co.uk [UK SQL Server User Group]



> > I am trying to use a stored procedure to return data based on a
parameter
> > that is passed to the procedure.  I effectivly have the following 3
cases
> > (CurrentStatus is the parameter passed into the procedure):

> > CurrentStatus = "OPEN"
> >     Select
> >       PCMSNumber, SubSystem, Status
> >     From
> >       spn W
> >     Where
> >         Status in ('IN_REVIEW','PENDING_APPROVAL')

> > CurrentStatus = "CLOSED"
> >     Select
> >       PCMSNumber, SubSystem, Status
> >     From
> >       spn W
> >     Where
> >         Status in not in ('IN_REVIEW','PENDING_APPROVAL')

> > --Could changed the above to be in ('Choice 3','Choice 4')

> > CurrentStatus = "ALL"
> >     Select
> >       PCMSNumber, SubSystem, Status
> >     From
> >       spn W

> > Due to other reasons, I don't want to just repeat the query three times.
> Is
> > there some way to do something like this in a single query.

 
 
 

Case Statement and variables passed to stored procedures

Post by Joe Celk » Fri, 17 Nov 2000 04:00:00


Quote:>> I am trying to use a stored procedure to return data based on a

parameter
that is passed to the procedure.  I effectively have the following 3
cases
(current_status is the parameter passed into the procedure): <<

Could I suggest that you need to format your code so a human being can
read it; this silliness of one word per line is something we did in the
days of punch cards.  But only at the shops which were so poor that
they could not afford a keypunch machine for the programmers.  Instead
of using an editor or repunching cards, we re-arranged our decks of
cars.

SELECT pcmsnumber, subsystem, status
  FROM SPN
 WHERE status
       = CASE WHEN current_status = 'all' THEN status
              WHEN current_status = 'open' THEN 'in_review'
              WHEN current_status = 'open' THEN 'pending_approval'
              WHEN current_status = 'closed'
              THEN CASE WHEN status = 'in review THEN '???'
                        WHEN status = 'pending approval' THEN '???' END
          END;

I assume that '???' will never match a status code, but  you could use
a NULL instead.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
WHEN posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut AND pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Case Statement and variables passed to stored procedures

Post by Tony Rogerso » Sat, 18 Nov 2000 15:44:22


Yep - agree with you there. Habit of a life time hard to break :)

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group]


> EXEC sp_executesql &SQLString will perform better since it has a better
> chance of being cached.

> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group



> > Personally, I'd use the EXEC( '.....' ) statement to execute a
previously
> > built sql string.

> > --
> > Tony Rogerson SQL Server MVP
> > Torver Computer Consultants Ltd
> > www.sql-server.co.uk [UK SQL Server User Group]



> > > I am trying to use a stored procedure to return data based on a
> parameter
> > > that is passed to the procedure.  I effectivly have the following 3
> cases
> > > (CurrentStatus is the parameter passed into the procedure):

> > > CurrentStatus = "OPEN"
> > >     Select
> > >       PCMSNumber, SubSystem, Status
> > >     From
> > >       spn W
> > >     Where
> > >         Status in ('IN_REVIEW','PENDING_APPROVAL')

> > > CurrentStatus = "CLOSED"
> > >     Select
> > >       PCMSNumber, SubSystem, Status
> > >     From
> > >       spn W
> > >     Where
> > >         Status in not in ('IN_REVIEW','PENDING_APPROVAL')

> > > --Could changed the above to be in ('Choice 3','Choice 4')

> > > CurrentStatus = "ALL"
> > >     Select
> > >       PCMSNumber, SubSystem, Status
> > >     From
> > >       spn W

> > > Due to other reasons, I don't want to just repeat the query three
times.
> > Is
> > > there some way to do something like this in a single query.

 
 
 

Case Statement and variables passed to stored procedures

Post by Paul Thornet » Sun, 19 Nov 2000 10:28:49



Quote:> Could I suggest that you need to format your code so a human being
can
> read it; this silliness of one word per line is something we did in
the
> days of punch cards.

Are you referring to the technology used (at least) in Florida for
registering votes?
 
 
 

1. Trouble passing variable in SELECT statement in Stored Procedure

I am passing a variable value to a stored procedure to SQL Server 7.0
via an ASP page on IIS 5.0. The code on the ASP page is as follows:
<%
Function DoSomething(strVar)
SQL = "sp_Foo '" & strVar & "'"
set rs = dBConnection.DBQuery(SQL)
     while not rs.EOF
       Response.write("<OPTION value=""" & trim(rs("item")) & """> " &
trim(rs("item")) & "</OPTION>")
     rs.MoveNext
     Wend
End Function
%>
<SELECT id=select3 name=variable1>
<OPTION selected value="">Any</OPTION>
<%
'***********************************************
'Pass the variablefield as the argument to the
'DoSomething funtion
'***********************************************
DoSomething("variable1")
%>
</SELECT>
<SELECT id=select3 name=variableN>
<OPTION selected value="">Any</OPTION>
<%
'***********************************************
'Pass the variablefield as the argument to the
'DoSomething funtion
'***********************************************
DoSomething("variableN")
%>
</SELECT>

The Stored Procedure looks like:
CREATE PROCEDURE sp_Foo

AS

NOT NULL

The record set only returns the strVar value in the drop down menu that
is created in the ASP page. When I place the variable directly in
stored procedure, it runs fine.

How can I code the stored procedure so that it will accept the
variable? What am I doing wrong? Any help would be appreciated.

Thanks

Sent via Deja.com
http://www.deja.com/

2. bool / vacuum full bug followup part 2

3. Build SQL Statement in Stored Procedure by Passing Variables

4. Debugging plpgsql functions

5. Executing a sql statement stored as a variable in a stored Procedure

6. Cursor

7. Pass variable from VBA to stored procedure

8. Printing Forms 4.5 canvases/pages

9. Passing Variables to Stored Procedures

10. OpenRowset - Passing Variables/Parameters into Stored Procedure

11. Passing a varying array to a stored procedure variable

12. Passing DTS Global Variables to Stored Procedures

13. Passing VB Variable to Stored Procedure