sql parameters which can be optional

Question

Hello - I have this problem where I need to set "optional" parameters for my stored procedure to work fine. For example, I have this:

CREATE PROCEDURE [dbo].[Search]
(
 @StartTime datetime = NULL,
 @EndTime datetime = NULL,
 @CustomerEmail nvarchar(255) = NULL,
 @OrderStatusID int
)

Now, in my .net website I have this like an example, please keep in mind that there can be only one parameter or there might be all of them:

commAdvanced.Parameters.Add("@StartTime", SqlDbType.DateTime).Value = startDate;
commAdvanced.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = endDate;
commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = null;
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = null;

And this is the query:

SELECT * FROM Order 
WHERE CreatedOn > CAST(@StartTime as datetime) 
  AND CreatedOn < CAST(@EndTime as datetime)
  AND Order.OrderStatusID = @OrderStatusID 
  AND Order.CustomerEmail = @PaymentStatusID

I am getting no records when I am doing that, can someone please help me what do I need to change.

Answer 1

You need to pass in DBNull.Value for those parameter you want to leave NULL (not just the .NET null) :

commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = DBNull.Value;
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = DBNull.Value;

This should do the trick.

Also: if you specify a parameter of type varchar or nvarchar, I would recommend to always specify its length.

Answer 2

Assuming there is a typo in the select query perhaps try

AND Order.CustomerEmail = ISNULL(@CustomerEmail, CustomerEmail)
And OrderStatusID = ISNULL(@OrderStatusID, OrderStatusID)

Also you don't need to cast @StartTime etc. to datetime. They are already of that type, no?

Answer 3

According to your question, out of 4 parameters either one or all parameters could be passed to your procedure. And your query has all the columns checked for in the where clause. So if I understand you right, currently, you will not get any records until all 4 parameters are passed with valid data.

Try this, I am simply constructing a query and then executing it. I check for each of the parameters for nulls and only the parameter values that are not null are included in the where clause.

declare @sqlstring varchar(1000)

set @sqlstring = 'SELECT * FROM Order WHERE 1=1 '

if @StartTime <> null OR @StartTime <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CreatedOn > CAST(@StartTime as datetime) ' 
END 

if @EndTime <> null OR @EndTime <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CreatedOn < CAST(@EndTime as datetime) ' 
END 

if @OrderStatusID <> null OR @OrderStatusID <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND OrderStatusID = @OrderStatusID ' 
END 

if @CustomerEmail <> null OR @CustomerEmail <> '' 
BEGIN 
set @sqlstring = @sqlstring + 'AND CustomerEmail > @CustomerEmail ' 
END 

print @sqlstring
Exec(@sqlstring)

Answer 4

I think this is simpy down to your query being incorrect.

Lets take the example of supplying only @StartTime. Your query would evaluate to:

SELECT * FROM Order  
WHERE CreatedOn > CAST(@StartTime as datetime)  
  AND CreatedOn < null
  AND Order.OrderStatusID = null
  AND Order.CustomerEmail = null

Assuming ANSI NULLs are ON, there is no value which when compared to null returns a true result, hence your empty result set.

I think Noel's answer is closest - I would suggest:

SELECT * FROM Order  
WHERE (@StartTime is null or CreatedOn > @StartTime)
  AND (@EndTime is null or CreatedOn < @EndTime)
  AND Order.OrderStatusID = isnull(@OrderStatusID, Order.OrderStatusID)
  AND Order.CustomerEmail = isnull(@CustomerEmail, Order.CustomerEmail)

marc_s is also correct - if you explicitly want to set an @ parameter value to SQL null, set it as

commAdvanced.Parameters.Add("@CustomerEmail", SqlDbType.nvarchar).Value = DBNull.Value;          
commAdvanced.Parameters.Add("@OrderStatusID", SqlDbType.Int).Value = DBNull.Value;   

However, because you have supplied default values of null in your stored proc (except @OrderStatusID - typo?), you don't actually need to add these parameters to the command at all.

Answer 5

Be warned: the best solution for maintainability may not be good for performance and scalability (and note the playing field has changed since SQL Server 2008).

This is quite a big topic and I recommend you read Dynamic Search Conditions in T-SQL by Erland Sommarskog.