How can I use optional parameters in a T-SQL stored procedure?

by

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland’s article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END

More Related Contents:

  1. SQl CASE Statement
  2. How to calculate age in T-SQL with years, months, and days
  3. Convert multiple rows into one with comma as separator [duplicate]
  4. How to get sp_executesql result into a variable?
  5. T-SQL Decimal Division Accuracy
  6. Selecting a Record With MAX Value
  7. How to convert Seconds to HH:MM:SS using T-SQL
  8. T-SQL: Selecting rows to delete via joins
  9. Combining INSERT INTO and WITH/CTE
  10. What is a “batch”, and why is GO used?
  11. Getting the minimum of two values in SQL
  12. TSQL divide by zero encountered despite no columns containing 0
  13. T-SQL loop over query results
  14. How to insert default values in SQL table?
  15. How to Round a Time in T-SQL
  16. T-SQL CASE Clause: How to specify WHEN NULL
  17. t-sql get all dates between 2 dates [duplicate]
  18. Return a value if no rows are found in Microsoft tSQL
  19. Check if a varchar is a number (TSQL)
  20. Using Aliases in Where Clause or an Alternative Option?
  21. Extract the first word of a string in a SQL Server query
  22. T-SQL – function with default parameters
  23. Search for one value in any column of any table inside a database
  24. Check if a varchar is a number (T-SQL)
  25. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  26. Convert unix epoch timestamp to TSQL datetime
  27. T-SQL Between Dates Confusion
  28. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  29. String.Format like functionality in T-SQL?
  30. How to parse string into date?

Leave a Comment