Dynamic WHERE clause Using Case Stmt

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)

Dynamic WHERE clause Using Case Stmt

Author

Topic

mj76
Starting Member

8 Posts

Posted - 2002-04-12 : 10:02:42

I am trying to create a dynamic where clause in a sproc, based upon optional parameters passed to the sproc. All of the params accept null values and I need to create the where clause based on this.

I tried using a CASE statement in the Where Clause, but I got a syntax error. Below is a sample of the code. I would greatly appreciate any feedback I could get.

Thanks

mj

/**************************

where

[year]=@Year

Case @Bnkr
when @Bnkr not null then
and bnkr = @bnkr
else
and bnkr not null
end

and Prob >= isnull(@Prob,.1) and (NewClient = @NewClientLook or NewClient = @NewClientLook2)

***********************/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 10:14:45

You actually had it, just need a little rewrite:

where [year]=@Year
AND bnkr=IsNull(@Bnkr, bnkr)
AND Prob >= isnull(@Prob,.1)
AND (NewClient = @NewClientLook or NewClient = @NewClientLook2)

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-04-12 : 10:18:52

CREATE PROCEDURE spSelectSomething
@Year INT = NULL,
@Bknr INT = NULL

as

SELECT * FROM yourTalbe
WHERE Year = ISNULL(@Year,Year) AND BkNr = ISNULL(@bknr,Bknr)

When you use the = NULL behind the declaration of the parameter, the parameter get's the default value 'NULL' when it's not supplied. By using the ISNULL function you check if the parameter is null (and not supplied), if it's NULL you check the Year column to itself (and returning all the records)

mj76
Starting Member

8 Posts

Posted - 2002-04-12 : 10:21:58

Rob,

will your response allow me to do the following....

@bnkr is passed a legit value:
Where clause will look only for values where the bnkr column = @bnkr
@bnkr is not passed a value:
all rows will be returned, regardless of bnkr column value

I searched this site, (after I posted, sorry) and found another possible solution, could you please comment on the following, and tell me if it will work just as well

Thanks for your time

mj

/***************************

where

[year]=@Year

and (@Bnkr is null or Bnkr = @Bnkr)

and Prob >= isnull(@Prob,.1)

and (@Loc is null or Location = @loc)

and (@dept is null or [Dept] = @Dept)

and (NewClient = @NewClientLook or NewClient = @NewClientLook2)

*************************/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 10:30:30

If the @bnkr variable is not passed, or is null, this expression:

WHERE bnkr=IsNull(@bnkr, bnkr) will evaluate to WHERE bnkr=bnkr

Since bnkr=bnkr is always true, it will return all the rows. Now, if you DO pass something through @bnkr (is not null):

WHERE bnkr=IsNull(@bnkr, bnkr) will evaluate to WHERE bnkr=@bnkr

And it will only return bnkr rows matching the @bnkr value. (Peter said the same thing in his post)

This is just a shorthand way of doing the CASE statement you had before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources