StanleyGu's Blog

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

Nice article but you can make your SQL faster by using COALESCE in the where clause:

WHERE LastName=COALESCE(@LastName,LastName) and FirstName = COALESCE(@FirstName,FirstName)

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

No, you can not. Sorry to say, but learn the basics before making comments.

LastName = COALESCE (@LastName, LastName) should trigger a table scan because COALESCE (@LastName, LastName) must be evaluated for EVERY SINGLE LINE.

WHERE (LastName = @LastName OR @LastName IS NULL)

does not trigger this as you have EITHER a constant true on the right side (@LastName IS NULL) OR a constant false there and a simple expression that can utilize an index.

Beginner mistake. Well, at least you have the guts to put that into a public comment, Paul.

One thing seriously missing is the explanation of the HUGH downside unless one creates the stored procedure "with recompile" - which is that the FIRST run decides the execution plan, and that is being reused. If I pass in null = @LastName, soemthing in @FirstName on the first run, this decides the execution plan... also being used for something in @LastName, null in @FirstName.

To avoid this, the SP must be recompiled on every execution. Especially with something like given in this example this may be significant bad performance otherwise.

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

Stanley,

I think that the problem on this solution is because when you use the statement ([LASTNAME] = @LASTNAME OR @LASTNAME IS NULL) in the WHERE clause and the column LASTNAME has a INDEX the SqlServer does not use the INDEX to improve performance on the query!

# links for 2010-02-09 « dstelow notes…

Pingback from  links for 2010-02-09 «  dstelow notes…

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

Well this is OK but one more interesting way to do so is use it like

WHERE LastName=ISNULL(@LastName,LastName)

and FirstName=ISNULL(@FirstName,FirstName)

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

helpful content related to <a href="www.mindstick.com/.../37dd5217-1856-418c-84af-a344fc353e0c Stored Procedure">stored procedure</a>

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

Nice comment amsneuton exactly what solved my issue.  simple and succinct.  Made my day!

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

Hey Thomas Tomiczek, you strike me as a guy who knows a lot about SQL Server, and who also happens to be a jerk.  Congrats on the first one.  Work on the second.

# re: Solution 5 : Implementing Optional Parameters in T-SQL Stored Procedures

how to write sql query withoptional parameter