Robert McLaws: FunWithCoding.NET

Public Shared Function BrainDump(ByVal dotNet As String) As [Value]

News

<script type="text/javascript"><!-- google_ad_client = "pub-4330602465258980"; google_hints = "ASP.NET, VB.NET, C#, C#.NET, WindowsForms, .NET Framework, VS2005, Visual Studio, XAML, WinFX, Windows Workflow, WPF, WCF, Atlas, NetFX3, Visual Studio Orcas"; google_ad_width = 120; google_ad_height = 240; google_ad_format = "120x240_as"; google_ad_type = "text_image"; google_ad_channel ="4997399242"; google_color_border = "B6C9E7"; google_color_bg = "EFEFEF"; google_color_link = "0000FF"; google_color_text = "000000"; google_color_url = "002C99"; //--></script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<!--
-->

You should feel free to challenge me, disagree with me, or tell me I'm completely nuts in the comments section of each blog entry, but I reserve the right to delete any comment for any reason whatsoever. That said, I will most likely only delete abusive, profane, rude, or annonymous comments, so keep it polite, please.

Blogroll

Cool .NET Articles

My .NET Tools

My Builder.com Articles

My MSKB Articles

Optional Parameters in SQL Stored Procedures

Want to create a stored procedure that searches for specific, but optional, values? I had to do it today for a web project I'm working on (I hate not being able to talk about projects I'm working on) and Terry Denham, the SQL Guru without a blog, came to my rescue yet again. Because the WHERE clause has secluded OR statements buttressed by a "trueism" (Terry's term) of 1 = 1 (does anyone know of a case where one DOESN'T equal one?), the statement will always execute, even if you don't pass anything in. Pretty cool, huh? Thanks Terry! Saved my butt again.

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))

Results in:

exec TestProc
exec TestProc I
exec TestProc I, Love
exec TestProc I, Love, SPROCs

UPDATE: The procedure above was updated to correct a logical flaw in Terry's example.You cannot use a “trueism” because you will always get back all rows no matter what parameters you pass in.While not as performant as Dynamic SQL (blah blah blah) it does the trick in situations like mine where direct table operations are not allowed for security reasons.

Comments

Darren Neimke said:

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 )
# February 18, 2004 4:41 AM

Paul Bartlett said:

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

WHERE @Param1 IN (Col1, NULL) ...
# February 18, 2004 5:02 AM

Ramon Smits said:

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 :)

# February 18, 2004 5:24 AM

Peter said:

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)
# February 18, 2004 5:28 AM

Paul Wilson said:

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. :)
# February 18, 2004 5:33 AM

Ramon Smits said:

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

:)
# February 18, 2004 5:57 AM

Mauricio Feijo said:

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
# February 18, 2004 6:29 AM

JosephCooney said:

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.
# February 18, 2004 11:33 AM

Frans Bouma said:

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)
# February 18, 2004 12:01 PM

Robert McLaws said:

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.
# February 18, 2004 12:19 PM