Wednesday, March 12, 2008

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.

No comments: