SQL Server - Optional where clause conditions - Asked By asif hameed on 16-Mar-10 02:49 AM

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

samjayander thiagarajan replied to asif hameed on 16-Mar-10 03:08 AM
Hi,

In your coding pass the value for @name in SP to null, if the user does not pass any values.

and change you SP like this.

and
name = COALESCE(@name,name)


using Coalesce, you will match the value for column name with the same value if it is null, so you will get the desired result.

Regards,
Sam
Appari NRao replied to asif hameed on 16-Mar-10 03:39 AM
try like this

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

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

Null the Parameters - Adam Houldsworth replied to Appari NRao on 16-Mar-10 04:34 AM

I use this method too, but I do the following with the parameters:

@studentId int = null,
@name varchar(50) = null,
@email varchar(50) = null

I also tend to put the null check first:

WHERE (@name is null or Name = @name) ...

Though this is more from habit - I was originally doing it because of parameter short-circuiting, but I soon learned that short-circuiting is not guaranteed, but does sometimes occur.

Adam
Jonathan VH replied to asif hameed on 16-Mar-10 07:07 AM
I suggest not using a stored procedure but instead building a parameterized text SQL command in your middle tier.

If the application will only be used with SQL Server 2008SP1CU5 or later, the above method (Name = @name OR @name IS NULL) will work efficiently with indexed columns if you use the OPTION(RECOMPLIE) hint.  But it may not work as required with any nullable columns, e.g. if you want to filter the result set to those rows where the email address is unknown.

Erland Sommarsog has written a full treatment of this subject http://www.sommarskog.se/dyn-search.html.