Using IF in WHERE Clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using IF in WHERE Clause

Author

Topic

rip2000
Starting Member

2 Posts

Posted - 2009-09-10 : 13:46:25

Hi
Can someone tell me how to include one or more IF statments in a WHERE clause please?

I want to query the table based on one of the parameters passed to the query as follows.

create procedure dbo.GetData
@studentID nvarchar(10) = NULL,
@Lastname nvarchar(20) = NULL,
@FirstName nvarchar(20) = NULL

AS
SELECT * FROM tblStudent
WHERE

(
IF @studentID is not null then studentid=@studentid

IF @Lastname is not null then lastname=@lastname

IF @Firstname is not null then firstname=@firstname
)

Can someone tell me the correct method/syntax to achive this please?
Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-10 : 13:53:22

One way is:

where studentid = coalesce(@studentid, studentid)
and lastname = coalesce(@lastname, lastname)
ect...

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-10 : 22:21:25

[code]
where
(
@studentid is null
or studentid = @studentid
)
and
(
@lastname is null
or lastname = @lastname
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rip2000
Starting Member

2 Posts

Posted - 2009-09-11 : 05:52:52

Many thanks for your answers. 2nd Method works fine.

Thanks again.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-09-11 : 06:35:14

Hi TG

Below is it possible.....

where studentid = ISNULL(@studentid, studentid)
and lastname = ISNULL(@lastname, lastname)

-------------------------
R...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 10:04:29

Yes, in this case ISNULL and COALESCE are equivalent.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources