optional where clause conditions

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

optional where clause conditions

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-03-16 : 02:49:11

Hi all,

I have a stored procedure which searches results from student database based on different parameters. like userid name email address. user may or may not pass some/all values. When i dont pass studentid( e.g), it doenst show any result. It should show all students. How can i do this ?

create procedure getstudents
(
@studentid int,
@name varcahr(50),
@email varchar(50)
)

begin
select * from students
where
studentid = @studentid
and
name = @name
and
email = @email
end

Regards,
Asif Hameed

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-16 : 03:02:41

try this
create procedure getstudents
(
@studentid int,
@name varcahr(50)=null,
@email varchar(50)=null
)

begin
select * from students
where
studentid = @studentid
and
(name = @name
or
email = @email)
end

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-16 : 03:03:17

create procedure getstudents
(
@studentid int = NULL,
@name varcahr(50) = NULL,
@email varchar(50) = NULL
)

begin
select * from students
where
(@studentid IS NULL OR studentid = @studentid)
and
(@name IS NULL OR name = @name)
and
( @email IS NULL OR email = @email)
end

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-16 : 03:04:02

Try this

select * from students where (@studentid is null or studentid =@studentid )
and (nullif(@name ,'') is null or name = @name)
and (nullif(@email,'') is null or name = @email)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-16 : 03:04:36

[code]select *
from students
where studentid = COALESCE(@studentid, studentid)
and name = COALESCE(@name, name)
and email = COALESCE(@email, email)[/code]

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-16 : 03:05:31

Yikes!! 4 answers in less than 2 minutes...that must be a record!

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources