Solved

SQL How to ignore string parameter

Posted on 2010-09-23
3
335 Views
Last Modified: 2012-05-10
Hi Experts

I have a select statement, where I pass in a parameter (@Specialty) this comes in as a comma separated list of values and I convert these to a table variable and use it like so.

Where Specialty in (Select Value From dbo.fnParmsToList(@Specialty)).

What I want is to ignore this value if it is null, (or empty string I can force it to either value in the calling code)

I usually use coalesce to return the first non null value, and this works great for single parameter values, but I cant get it to work with the above.

So my question is how can I ignore the parameter @Specialty and return ALL values unless I specify the parameter.

If I might just add, this is not the only value in the where clause, there are 5 others, where I use coalesce in 3.

Andy
0
Comment
Question by:Andy Green
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33741876
what about:

Where ( @Specialty IS NULL OR Specialty in (Select Value From dbo.fnParmsToList( @Specialty)) )

Open in new window

0
 

Expert Comment

by:madhav_ghatole
ID: 33741910
try this query

SELECT * FROM TableName
      WHERE (CASE WHEN ISNULL(@Specialty, '') = '' THEN 1
                    WHEN @Specialty IS NOT NULL AND Specialty IN (Select Value From dbo.fnParmsToList(@Specialty))
                    ELSE 0) = 1
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 33742026
Thanks again angelIII
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

760 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

18 Experts available now in Live!

Get 1:1 Help Now