Very Computer

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.

                 '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.

--
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.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group

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

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.

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

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.