where case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

where case

Author

Topic

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 16:33:58

hi i need a case statement as follows just not sure of the syntax

WHERE St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%'

CASE @Cat WHEN 0 THEN
AND St_Cat_ID = @Cat
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 16:39:39

Neither do we.

What is the purpose with CASE @Cat check?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-11 : 16:49:49

All you need is simple boolean logic. see: http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 17:28:37

well if the @cat = 0 then i dont want the where clause

if @cat <> 0 then i need the where clause

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:31:01

[code]select *
from ...
WHERE St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%' AND @Cat <> 0
OR @Cat = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-11 : 17:36:54

I think he wants:

WHERE St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%' and (st_cat_id = @cat or @cat = 0)

Peso -- be careful mixing ANDs and ORs -- always use parenthesis, even if you know how it will be evaluated so that it is clear what you intend....

- Jeff
http://weblogs.sqlteam.com/JeffS

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 17:39:42

no that wouldnt work what i need in asp is

if @cat = 0 then
where st_cat_id = @cat
else
nothing here
end if

but need to convert it to sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 17:40:12

Point taken.

Maybe this is want OP wants?

select *
from   ...
WHERE  (St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%' AND @Cat = st_cat_id)
       OR (@Cat = 0)

E 12°55'05.25"
N 56°04'39.16"

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 17:57:06

no that wouldnt work what i need in asp is

if @cat = 0 then
where st_cat_id = @cat
else
nothing here
end if

but need to convert it to sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:04:50

And we have done!

WHERE St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%' and (st_cat_id = 0 and @cat = 0 or @cat <> 0)

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-12-11 : 18:11:03

WHERE (St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%') AND ((@cat=0 and st_cat_id=0 ) or (@cat<>0) )

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 18:13:04

well that dont work!!

i need where St_Cat_ID = @Cat

only if @cat <> 0

and if it does = 0 then i want no where clause at all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:20:16

Not even the @Artist thingy?

E 12°55'05.25"
N 56°04'39.16"

craigmac999
Starting Member

14 Posts

Posted - 2007-12-11 : 18:25:27

ok can i do something like this

ALTER PROCEDURE [omacca].[GetStock]
-- Add the parameters for the stored procedure here
@Artist varchar (50),
@Title varchar (50),
@Cat INT
AS

CASE WHEN @CAT=0 THEN
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT *
FROM omacca.Stock_Table
WHERE St_Code LIKE @Artist + '%' AND St_Description LIKE @Title + '%'
END
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 18:45:29

Jeff nailed it already at 11/2007 : 17:36:54

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources