CASE in WHERE clause with parameter

[codrutza]

Hello

Please help me with this

CREATE PROCEDURE testp
@Choose char(8)
AS

SELECT
TbA.Number,
TbB.Name,
....

FROM
TbA INNER JOIN TbB ON TbA.Number=TbB.Number

WHERE
…
CASE @Choose=
WHEN @Choose='C' THEN (TbA.Number='123' or TbB.Name='Caaaaa')
WHEN @Choose='S' THEN (TbA.Number='431' or TbB.Name='Ssss')
ELSE everything else
END

How to write that CASE?
Thank you

 

[bborissov]

TbA.Number = CASE WHEN @Choose='C' 
                      THEN '123'
                 WHEN @Choose='S' 
                      THEN '431'
                 ...
            END OR
TbB.Name=  CASE WHEN @Choose='C' 
                      THEN 'Caaaaa'
                WHEN @Choose='S' 
                     THEN 'Ssss'
                ...
           END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

try this:

Where  (@Choose = 'C' And (TBA.Number = '123' Or TBB.Name = 'Caaaaa'))
       Or
       (@Choose = 'S' And (TBA.Number = '431' Or TBB.Name = 'Ssss'))
       Or 
       (Coalesce(@Choose, '') Not In ('C','S'))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Axworthy]

I would write it like this and not use a case statement:

Where 
  (@Choose='C' and (TbA.Number='123' or TbB.Name='Caaaaa'))
  or (@Choose='S' and (TbA.Number='431' or TbB.Name='Ssss'))
  or (@Choose <> 'C' and @Choose <> 'S')

I am assuming that if @Choose is not C or S you want to return all rows.

 

[BrendanBlackford]

Thank you all.

I have to write as well

...
or (@Choose = 'SeaF' And (TbA.Via= 'Sea' And Tba.Type like 'F'))

or (@Choose='AllOthers') then bring all the remaining rows

 

[codrutza]

Sorry, all. I loged in with another user....
Sorry, Brendan.

So, my question was:

I have to write as well

...
or (@Choose='S' and (TbA.Number='431' or TbB.Name='Ssss'))
or (@Choose = 'SeaF' And (TbA.Via= 'Sea' And Tba.Type like 'F'))
or (@Choose='AllOthers') then bring all the remaining rows

How do I do that?

 

[gmmastros]

Have you tried that?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

you mean that part:
Or (Coalesce(@Choose, '') Not In ('C','S'))

I don't know how to write:
or @Choose='AllOthers' then retrieve all the rows that are not satisfying neither a criteria. I have to say @Choose='AllOthers' because I use the parameter @Choose in a report built with Crystal Reports.

My english is not very good and I am new in SP, so if you want to ask something, ask me.

 

[gmmastros]

What I'm saying is... this should work:

or (@Choose='S' and (TbA.Number='431' or TbB.Name='Ssss'))
or (@Choose = 'SeaF' And (TbA.Via= 'Sea'   And  Tba.Type like 'F'))
or (@Choose='AllOthers')

That is, of course, assuming you pass 'AllOthers' for the @Choose parameter.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

It's something strange. When I change something in the procedure, it's not working.

 

[bborissov]

Something" sounds scary
What EXACTLY you changed and what is the error message?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Oops - I thought I posted my question, but probably was interrupted.

Can you post just the beginning of your SP, I suspect you hit this problem

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-stored-procedure-with-nvarcha

 

[codrutza]

Hmmmm

I modified
(@Choose = 'SeaF' And (TbA.Via= 'Sea' And Tba.Type like 'F%'))
with
(@Choose = 'SeaF' And (TbA.Via= 'Sea' And Tba.Type like 'F%') and @Choose<>'S')

I don't have SQL Server here, only at work, so I wrote what I remember...

Practically, I have to exclude, from each "group" the others "groups" and what is left goes in "AllOthers". The thing is, the "groups" are not based all on tba.number or tbb.name; they are tba.type, tba.via, etc. I don't know if is possible to group like that.

 

[bborissov]

That is redundant:
(@Choose = 'SeaF' AND .... AND @Choose<>'S')

If @Choose = 'SeaF' it IS already @Choose <> 'S'

Unless of course you didn't specify the length of the parameter (like markros point) or use some fixed size types.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[BrendanBlackford]

Hi all
Excuse me for not replying sooner.

Now in the procedure is:

(
(@Choose = 'C' And (Tba.CAccNumber='123' or Tbb.SName like 'C%'))
Or
(@Choose = 'T' And (Tba.CAccNumber ='234' or Tbb.SName like 'T%'))
Or
(@Choose = 'Air' And (Tba.Via= 'Air' and not(@Choose = 'C' or @Choose = 'T' or @Choose = 'Exp' )))
Or
(@Choose = 'Exp' And (Tba.Type= 'Exp'))
Or
(@Choose = 'SeaF' And (Tba.Via= 'Sea' And Tba.Goods like 'F%'))
Or
(@Choose = 'SeaL' And (Tba.Via= 'Sea' And Tba.Goods like 'L%'))
Or
(@Choose='AllOthers' And @Choose Not In ('T','C', 'Air', 'Exp','SeaF','SeaL')
)


This it’s not working; brings Tba.Type='Imp' too:

(@Choose = 'Air' And (Tba.Via= 'Air' and not(@Choose = 'C' or @Choose = 'T' or @Choose = 'Exp' )))

What I do wrong?

The second question:
@Choose char(15)

Please help me