Can I use CASE into a WHERE clause ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Can I use CASE into a WHERE clause ?

Author

Topic

fabianCastle
Starting Member

1 Post

Posted - 2003-10-13 : 11:26:45

Can I use the CASE into a where clause ?

This is the query

declare @obj_name as varchar(255)
declare @FilterFrom as datetime
declare @FilterTo as datetime
declare @signature as varchar(100)

select @obj_name='myPix'
select @FilterFrom = '1'
select @FilterTo = '100000'
select @signature =1

SELECT sum(qty) as Quantity, obj_name, IDS_IP_From
FROM v_Events_IDS_ShortTerm
where obj_name = @obj_name
and doe BETWEEN @FilterFrom and @FilterTo and IDS_IP_From is not null

and Case @signature
WHEN null THEN IDS_Signature = @signature
ELSE IDS_Signature BETWEEN @SignatureFrom and @SignatureTo
END,

group by obj_name, IDS_IP_From
ORDER BY quantity DESC

I'm getting this error

Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '='.

Basically what I want is the query to change when I have different pareameters values.

In this case when @signature is null , I want the query to be:

SELECT sum(qty) as Quantity, obj_name, IDS_IP_From
FROM v_Events_IDS_ShortTerm
where obj_name = @obj_name
and doe BETWEEN @FilterFrom and @FilterTo and IDS_IP_From is not null

and IDS_Signature = @signature

group by obj_name, IDS_IP_From
ORDER BY quantity DESC

when @signature has a value, I want the query to be:

SELECT sum(qty) as Quantity, obj_name, IDS_IP_From
FROM v_Events_IDS_ShortTerm
where obj_name = @obj_name
and doe BETWEEN @FilterFrom and @FilterTo and IDS_IP_From is not null

and IDS_Signature BETWEEN @SignatureFrom and @SignatureTo

group by obj_name, IDS_IP_From
ORDER BY quantity DESC

Fabian Castillo
Information System Analyst

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-13 : 12:03:44

3 things...

1...you can't say "where IDS_Signature = @signature" where @signature is null...it has to be worded "IDS_Signature IS NULL".

2...if the @signature has only 2 values of interest you could use an IF condition.
IF @signature is null
begin
select a,b,c,etc
end
else
begin
select a,b,c,d, etc
end

3....you could use "dynamic sql"...but this adds extra problems....different security model, poorer performance, etc...
search here for dynamic sql...the topic comes up a lot....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources