CODEWITHSUNDEEP
t-sqlsql-server-2008
user812824
user812824

Reputation: 83

How to write a SQL Server 2008 query based on parameter passed at run time

I have 4 parameters that are being passed to the query. My requirement is to return result even though any of them are null. Do we have to write a if condition in the where clause or write a dynamic query? The parameter has a datetime being one of them and also datetime being a range variable.

Thanks in advance.

Upvotes: 0

Views: 1992

Answers (2)

Lucent Fox
Lucent Fox

Reputation: 1795

I think what you're asking for is "SQL Optional Parameters":

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

However there is a performance problem with this query; as elegant as it looks, it will prevent SQL Server from correctly using your indexes. If performance is a problem you're better off using sp_executesql to dynamically create a string and execute it as a query.

See: http://www.davewentzel.com/content/optional-sql-parameters-and-performance

Upvotes: 0

ibram
ibram

Reputation: 4569

If I understand you correctly you need to prepare a where clause which handles a NULL parameter value too.

So, this could be a solution:

declare @myDate datetime = NULL;
declare @myRangeFrom datetime = NULL;
declare @myRangeUntil datetime = NULL;

SELECT * FROM MyTable
WHERE (@myDate IS NULL OR tblDate = @myDate)
AND (@myRangeFrom IS NULL OR tbl2ndDate >= @myRangeFrom)
AND (@myRangeUntil IS NULL OR tbl2ndDate <= @myRangeUntil)

Upvotes: 1

Related Questions