eliminating where clause if variable not supplied

Question

Post reply

eliminating where clause if variable not supplied

Darren Welch

SSC Eights!

Points: 973
More actions
August 3, 2006 at 6:16 am

#169903

Hi,
I'm trying to create a procedure that will allow me to eliminate part of the where clause if specific variables are not supplied.
Here is a simplified version of the procedure:
CREATE PROC dbo.test
@surname varchar(25) = NULL
,@forename varchar(20) = NULL
AS
SELECT   RTRIM(surname) + ', ' + RTRIM(forename) AS [full_name]
             ,company_name
FROM      pfo_both_people_search
WHERE    surname = isnull(null, @surname)
AND        forename = isnull(null, @forename)
So for example, if the application does not supply @forename is there a way to eliminate the 'and' clause. Or does anyone have a better way of doing this?
Thanks in advance.
Darren

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Well first of all that solution doesn't work. Here's the working version :

SELECT RTRIM(surname) + ', ' + RTRIM(forename) AS [full_name]

,company_name

FROM pfo_both_people_search

WHERE surname = isnull(@surname, surname)

AND forename = isnull(@forename, forename)

so 'RgRus' = isnull(null, 'RgRus') >> true, so the row isn't filtered out.

You might want to add a check to see that at least one param is supplied so that you don't return the whole table to the user .

As for a better way, it's hard to say. There are always a bunch of ways to do this type of procs, but if absolute performance is not a must, then my version should do just fine. Just make sure you have and index that includes both those columns and you should get descent speed.

Deepak R-352629 Newbie Points: 4 More actions · Answer 2

You can form final version of sql string based on if condition, i.e. if @forename is not null

sql = sql + 'and forname = ' + @forename

and can execute the sql statement finally to arrive the results

Darren Welch SSC Eights! Points: 973 More actions · Answer 3

Thanks RGR'us, Deepak!

I had tried the string concatenation but it was a bit messy and I couldn't quite get it to work!

RGR'us, that solution is perfect.

Thanks

Darren

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

This is not a risk I would take for such a simple search operation. The risk of sql injection is too high to save a few ms (assuming some are saved).

Check out this MVP Article :

http://www.sommarskog.se/dynamic_sql.html