Conditional query - 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

Conditional query

Author

Topic

tingu
Starting Member

4 Posts

Posted - 2007-03-15 : 07:45:19

I have a table having CusId, CusName, LocId and TvId among other fields.
I have 3 dropdown list which are are populated with cusid, locid and tovid.

I have added "Please Select" in all the 3 ddl with value -1.
Now i have written a sql statement as :

declare @CusId int
declare @LocId int
declare @TvId int

select CusId, CusName, LocId and TvId
From CustDetails
where CusId = @CusId
and LocId = @LocId
and TvId = @TvId

All i want is if any of the three variables has a value -1 then query should be modified. say @CusId has a value of -1 selected in dropdown list then query should become

select CusId, CusName, LocId and TvId
From CustDetails
where CusId IS NOT NULL
and LocId = @LocId
and TvId = @TvId

Please help me on this as i am totally new to sql.

Regards
Tingu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 07:51:32

Something like this?

select CusId, CusName, LocId, TvId 
From CustDetails
where CusId = ISNULL(NULLIF(@CusId, -1), cusid)
and LocId = ISNULL(NULLIF(@LocId, -1), LocId)
and TvId = ISNULL(NULLIF(@TvId, -1), TvId)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 07:52:25

[code]SELECT CustID,
CustName,
LocID,
TvID
FROM CustDetails
WHERE @CustID IN (-1, CustID)
AND @LocID IN (-1, LocID)
AND @TvID IN (-1, TvID)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 07:53:02

Same logic, different methods

But I think mine will benefit with existing indexes.

Peter Larsson
Helsingborg, Sweden

tingu
Starting Member

4 Posts

Posted - 2007-03-15 : 08:08:56

Wow,
So fast replies.
thanks for your help.
I know i will love this forum and will have long association with it.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 08:13:05

That can be the case, Peter.

Although in my test, both methods resulted in CL Index Scan and both were equal in terms of cost. But on large test data, the output may be different.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources