Robert McLaws: FunWithCoding.NET

Hey Rob, which one has the better execution plan, the one that you shared or this:


CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*

FROM
TestTable

WHERE col1 = ISNull( @Param1,col1 )
AND col2 = ISNull( @Param2,col2 )
AND col3 = ISNull( @Param3,col3 )

Another variation to consider is anyone's doing execution plans:

WHERE @Param1 IN (Col1, NULL) ...

Shouldn't is be something like the following?

CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*

FROM
TestTable

WHERE
((@Param1 is null) OR (col1 = @Param1)) AND
((@Param1 is null) OR (col2 = @Param2)) AND
((@Param1 is null) OR (col3 = @Param3))

Or am I missing out here????

Doing the 1=1 will always return all rows if I am correct. So basicly you could just run

SELECT *
FROM TestTable

Which will have the best execution plan I think :)

This makes no sense, did you actually test it?

The option Darren offers works, but only if there are no NULL values in the columns.

(you would need ISNULL also on the column, and COALESCE in the where statement)

Ramon's sql is what I've always seen and used. I don't know if its "best", but it sure beats yours, which doesn't work. :)

Sorry... I copy/paste @Param1 two times too many :)

CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT
*

FROM
TestTable

WHERE
((@Param1 is null) OR (col1 = @Param1)) AND
((@Param2 is null) OR (col2 = @Param2)) AND
((@Param3 is null) OR (col3 = @Param3))

I don't know what you want to accomplish with your query yours will definitaly will always return all rows.

WHERE
(col1 = @Param1 OR 1=1) AND
(col2 = @Param2 OR 1=1) AND
(col3 = @Param3 OR 1=1)

Is just the same as

WHERE
TRUE AND
TRUE AND
TRUE

Thus every record will be returned. And I don't think that is what you are after.

@Paul Wilson: This search query will work perfectly and based on 100% on hands experience :)

The only problem that I have with optional parameters for search storedprocedures is that they will degrade your query performance dramatically if you use a lot of paramters. It is just better build the query in some sort of dataaccess component. Because SQL server will save the execution plan performance won't suffer from this. Only problem with this is that some architects think this is wrong.. If that is the case then just pass a varchar with the storedprocedures that you execute with

declare @query nvarchar(4000)
set @query = 'SELECT * FROM testtable'
exec sp_executesql @query

:)

My usual approach is:

WHERE
(@Param1 IS NULL OR col1 = @Param1) AND
(@Param2 IS NULL OR col2 = @Param2) AND
(@Param3 IS NULL OR col3 = @Param3)

This is a must read:

http://www.algonet.se/~sommar/dyn-search.html

Ramon smits has the right solution.

You can rework it to:
CREATE PROCEDURE TestProc
(
@Param1 varchar(50) = NULL,
@Param2 varchar(50) = NULL,
@Param3 varchar(50) = NULL
)
AS
SELECT *
FROM TestTable
WHERE col1 = COALESCE(@Param1, col1) AND
col2 = COALESCE(@Param2, col2) AND
col3 = COALESCE(@Param3, col3)

However, if you compare execution times of these kind of stored procs with dynamic sql, you'll be shocked as the stored proc is very slow.

I did some testing back then:
code: http://weblogs.asp.net/fbouma/articles/7049.aspx
and the article:
http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx

Like Ramon, this is how I would do it:

create procedure testproc
( @Param1 varchar(50) = NULL, @Param2 varchar(50) = NULL, @Param3 varchar(50) = NULL )

as

select *
from testable
where ((@Param1 is null) or (col1 = @Param1))
and ((@Param1 is null) or (col2 = @Param2))
and ((@Param1 is null) or (col3 = @Param3))

The way Rob did will optimize to a simple select * from testable

Frans - regarding the changes you suggest, this technique does not work where col1, col2 or col3 are null when ANSI_NULLS is ON. AFAIK Ramon's code does handle null columns properly.

Thanks for the info Frans. I wish I could do dynamic SQL, but unfortunately I can't. I'm pretty anal about security, and I don't allow direct operations against my tables.

Thanks William for the link... I'll look into sp_executesql.

And thanks Ramon for a working script. It seems to do what I need it to.

Joseph:
hmm.. you're probably right. Too bad, as COALESCE is a bit faster than the IS NULL OR .. constructs (as far as I can tell from the execution plan)

This is a classic situation of the differnce between the easy solution and the right solution.

The solution above results in far from optimum query plans. William's link to http://www.algonet.se/~sommar/dyn-search.html covers all the points and is the best coverage of this issue I have read, and is a must read for any serious SQL person.

Simon,

Unfortunately the right solution is the wrong solution because it opens up an unacceptable attack vector on my application. I do not allow any direct operations on my tables. The link you provided stated the security risk up front, and said it may not be acceptable for that very reason. Given the requirements, your option is not valid.

Recomendaciones para manejo de par

Pingback from  Twitter Trackbacks for                 Optional Parameters in SQL Stored Procedures - Robert McLaws: FunWithCoding.NET         [asp.net]        on Topsy.com

Pingback from  SQL: Procedimiento almacenado con par??metros opcionales en b??squedas « Bo Guerra

Pingback from  Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL | Gioorgi.com