How to use Case Statement

I tried and came up with no solution.
I need to use Stored procedure and no dynamic sql.
I ma using ms sql 2000 service pack 3
I am using three different tables. How I can make it to run union statements
only for those table where passing values for the variable is 1
Thanks
Tanweer
Declare @.people_list int
Declare @.client_list int
Declare @.joborder_list int
set @.people_list=1
set @.client_list =0
set @.joborder_list =1
select id, code form people_list
union
select id, code form Client_list
union
select id, code form joborder_listselect id, code form people_list
WHERE @.people_list = 1
union
select id, code form Client_list
WHERE @.client_list = 1
union
select id, code form joborder_list
WHERE @.joborder_list = 1
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 16:22:01 -0700, Tanweer
<Tanweer@.discussions.microsoft.com> wrote:

>I tried and came up with no solution.
>I need to use Stored procedure and no dynamic sql.
>I ma using ms sql 2000 service pack 3
>I am using three different tables. How I can make it to run union statement
s
>only for those table where passing values for the variable is 1
>Thanks
>Tanweer
>
>Declare @.people_list int
>Declare @.client_list int
>Declare @.joborder_list int
>set @.people_list=1
>set @.client_list =0
>set @.joborder_list =1
>select id, code form people_list
>union
>select id, code form Client_list
>union
>select id, code form joborder_list
>
>
>|||Try:
SELECT id, code form people_list
WHERE @.people_list = 1
UNION
SELECT id, code form Client_list
WHERE @.client_list = 1
UNION
SELECT id, code form joborder_list
WHERE @.joborder_list =1
Also, consider using UNION ALL instead of UNION unless you need to remove
duplicate rows in the result.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>I tried and came up with no solution.
> I need to use Stored procedure and no dynamic sql.
> I ma using ms sql 2000 service pack 3
> I am using three different tables. How I can make it to run union
> statements
> only for those table where passing values for the variable is 1
> Thanks
> Tanweer
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> union
> select id, code form Client_list
> union
> select id, code form joborder_list
>
>
>|||First of all, CASE is an expression, not a statement, so it has to be used
as a value inside a statement. There is probably some way to get a CASE
expression to help you hear, but you might try something like this instead:
Declare @.people_list int
Declare @.client_list int
Declare @.joborder_list int
set @.people_list=1
set @.client_list =0
set @.joborder_list =1
select id, code form people_list
where @.people_list=1
union
select id, code form Client_list
where @.client_list = 1
union
select id, code form joborder_list
where @.joborder_list =1
So the where clauses will resolve to either WHERE 1=1, which will return all
the rows, or WHERE 0 =1 which will return none of the rows.
HTH
Kalen Delaney, SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>I tried and came up with no solution.
> I need to use Stored procedure and no dynamic sql.
> I ma using ms sql 2000 service pack 3
> I am using three different tables. How I can make it to run union
> statements
> only for those table where passing values for the variable is 1
> Thanks
> Tanweer
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> union
> select id, code form Client_list
> union
> select id, code form joborder_list
>
>
>|||Love you guys
Great help
Thanks very much
Tanweer
"Kalen Delaney" wrote:

> First of all, CASE is an expression, not a statement, so it has to be used
> as a value inside a statement. There is probably some way to get a CASE
> expression to help you hear, but you might try something like this instead
:
>
> Declare @.people_list int
> Declare @.client_list int
> Declare @.joborder_list int
> set @.people_list=1
> set @.client_list =0
> set @.joborder_list =1
> select id, code form people_list
> where @.people_list=1
> union
> select id, code form Client_list
> where @.client_list = 1
> union
> select id, code form joborder_list
> where @.joborder_list =1
> So the where clauses will resolve to either WHERE 1=1, which will return a
ll
> the rows, or WHERE 0 =1 which will return none of the rows.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
> news:86921632-36FA-4AD8-857D-4A639725B574@.microsoft.com...
>
>