Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query Syntax for Optional Parameter

Posted on 2014-12-12
2
101 Views
Last Modified: 2014-12-12
I have a parameter (@Phrase) that may be filled.  It has properties set to Allow Null.

My issue is with my dataset (query).  I don't know how to make it run if the @Phrase is Null.

Here is my Where clause:

WHERE i.InsuredID in(@ClientID)
AND LossDate between @FromDate and @ToDate
AND co.FileName like '%@Phrase%'  ----- this isn't working when nothing is put into the Parameter.
0
Comment
Question by:Scott Williams
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40496637
AND (co.FileName LIKE '%' + @Phrase + '%' OR @Phrase IS NULL)

To test, copy-paste the below into your SSMS..
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (val varchar(100))

INSERT INTO #tmp (val) 
VALUES ('goo'), ('foo'), ('boo'), ('yabba'), ('dabba'), ('doo')

-- Returns only the values LIKE @par when it's populated
Declare @par varchar(100) = 'g'

SELECT val
FROM #tmp
WHERE (val LIKE '%' + @par + '%'  OR @par IS NULL)  

-- Returns all when it's NULL
SET @par  = NULL

SELECT val
FROM #tmp
WHERE (val LIKE '%' + @par + '%'  OR @par IS NULL)  

Open in new window

0
 

Author Closing Comment

by:Scott Williams
ID: 40496664
Thanks a lot Jim!  That's a great way to prove it works!!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 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