Query Syntax for Optional Parameter

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.
Scott WilliamsData AnalystAsked:
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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

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'

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

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

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

Open in new window

Scott WilliamsData AnalystAuthor Commented:
Thanks a lot Jim!  That's a great way to prove it works!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.