Solved

SQL Server Optional Parameters

Posted on 2014-09-11
1
175 Views
Last Modified: 2014-09-11
I am writing a stored proc for an SSRS report.  The user wants the report to bring back all the data on the first run and then allow the user to set 4 parameters.  I initially created the report to show nothing until the user selected parameters, but they want all data returned and then filter it out.  

I have four parameters  @LiveDate datetime = Null, @Status bit = Null,@CarType varchar(30) = NULL,@somethingelse varchar(30) = NULL

Then I have a select statement.  It the WHERE clause that I am confused about.  If the parameters are NULL, then I want to return everything  and if not then return according to the parameter values.

Can someone help me with the WHERE clause here.  

Thanks.
0
Comment
Question by:sherbug1015
1 Comment
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
You can use the ISNULL function to handle this:
WHERE <Your Field> = ISNULL(@YourParam, <Your Field>)

Another method is:
WHERE (@YourParam IS NULL OR <Your Field> = @YourParam)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now