T-SQL: Optional Parameters in Select Query (Using ISNULL)

Suppose, you have to make search in the database table having optional parameters. For example you have some application with advanced search UI where user does not have to fill all search criteria. We can make it with one small trick - using ISNULL function.

CREATE PROC Search
(
    @param1 INT = NULL
   ,@param2 INT = NULL
)
AS
BEGIN

   SELECT column1
     FROM table1
    WHERE column1 = ISNULL(@param1, column1)
      AND column2 = ISNULL(@param2, column2)

END

If the 1st param in ISNULL function is null - it returns 2nd parameter.

Update: syntax 2005-03-26

Related Posts:

• MSSQL Tip: Get Primary Table in Relationship
• MSSQL Tip: Get Table Primary Key Column(s)
• ROWNUM [ PL/SQL ] Analog for T-SQL
• MSSQL Tip: Get Connected Columns in Related Tables
• How To Shrink Transaction Log in MSSQL 2000
• T-SQL: Output Parameter in sp_executesql
• MSSQL: Change tables owner to dbo with sp_changeobjectowner
• MSSQL: Recompile Stored Procedures, Views, Functions
• MSSQL Tip: Rebuild Clustered Indexes
• T-SQL: Aggregate Values Multiplication Like SUM Aggregates Values Sum

For those from Rio Linda, the Type = NULL makes it optional
if you define the parameters with as they are required

This doesn't work if column1 or column2 are actually null in the table. unless you need to turn the SET_ANSI_NULL value....