sql parameters which can be optional

Question

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.

Where did @PaymentStatusID come from in your SELECT statement? — Oded
– Oded, Commented Oct 28, 2010 at 18:58

Zephyr · Accepted Answer · 2010-10-28 19:04:15Z

1

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?

answered Oct 28, 2010 at 19:04

Zephyr

7,8634 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pavanred · Accepted Answer · 2010-10-28 19:54:03Z

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)

marc_s · Accepted Answer · 2010-10-28 19:02:39Z

0

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.

answered Oct 28, 2010 at 19:02

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

retired · Accepted Answer · 2010-10-28 20:07:56Z

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.

onedaywhen · Accepted Answer · 2010-10-29 09:07:19Z

0

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.

answered Oct 29, 2010 at 9:07

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

sql parameters which can be optional

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related