TSQL - if variable passed in is null do one thing, otherwise do something else

WHERE scc.CallNumber LIKE '%' + @vs_QueueNumber + '%'

I want to do the LIKE part only if @vs_QueueNumber is not null.

Also, I have a few other parameters coming in ... can you AND together IF..THEN..ELSE statements in a where clause?


My current stored proc:

CREATE PROCEDURE [dbo].[DHD_GetTicketQueue]
    @vs_QueueNumber VARCHAR(50),				--SHAgents.PrefFullName (later on will use SCCalls.GroupName?)
    @vs_Priority VARCHAR(15),					--SCPriorities.Priority
    @vs_PriorityRank SMALLINT,					--SCPriorities.Rank
    @vs_TicketStatus VARCHAR(255),				--SCCallStatusTypes.Description
    @vs_TicketNumber VARCHAR(255),				--SCCalls.CallNumber
    @vs_TicketCallType VARCHAR(50),				--SCCallTypes.Description
    @vs_AssignedTo VARCHAR(50),					--SHAgents.PrefFullName
    @vs_CustomerNumber VARCHAR(32),				--ARCustomers.CustomerNumber
    @vs_CustomerName VARCHAR(128),				--ARCustomers.CustomerName
    @vs_DateTime DATETIME						--does not correspond to a field ... is passed in to obtain delta in records   
  
AS

SELECT 
	scc.CallNumber		AS TicketNumber,		--SCCalls.CallNumber
	scp.Priority,								--SCPriorities.Priority
	scp.Rank			AS PriorityRank,		--SCPriorities.Rank	
	scc.Date			AS DateEntered,			--SCCalls.Date
	scc.Description		AS TicketDescription,	--SCCalls.Description
	arc.CustomerName,							--ARCustomers.CustomerName
	arc.CustomerNumber,							--ARCustomers.CustomerNumber
	scc.Caller			AS CallerName,			--SCCalls.Caller or if contact record look there first
	scc.AddressCity		AS Location,			--SCCalls.AddressCity
	scs.Description		AS Status,				--SCCallStatusTypes.Description
	scy.Description		AS CallType,			--SCCallTypes.Description
	sbc.Description		AS BillCode,			--SCBillCodes.Description
	sha.PrefFullName	As AssignedTo,			--ShAgents.PrefFullName
	scc.ReqDate			AS RequiredDate,		--SCCalls.ReqDate
	swo.WorkOrderNumber							--SCWorkOrders.WorkOrderNumber
	
	FROM SCCalls scc
	INNER JOIN SCPriorities scp ON scp.PriorityID = scc.PriorityID
	INNER JOIN SCTechnicians sct ON sct.TechnicianID = scc.TechnicianID
	INNER JOIN ShAgents sha ON sha.AgentID = sct.TechnicianID
	INNER JOIN ARCustomers arc ON arc.CustomerID = scc.CustomerID
	INNER JOIN SCCallStatusTypes scs ON scs.Type = scc.Status
	INNER JOIN SCCallTypes scy ON scy.CallTypeID = scc.CallTypeID
	INNER JOIN SCBillCodes sbc ON sbc.BillCodeID = scc.BillCodeID
	INNER JOIN SCWorkOrders swo ON swo.WorkOrderID = scc.WorkOrderID

	WHERE scc.CallNumber LIKE '%' + @vs_QueueNumber + '%'

GO

Open in new window

LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>WHERE scc.CallNumber LIKE '%' + @vs_QueueNumber + '%'
>I want to do the LIKE part only if @vs_QueueNumber is not null.

SELECT blah, blah, blah
FROM your_stuff
WHERE ( scc.CallNumber LIKE '%' + @vs_QueueNumber + '%' OR @vs_QueueNumber IS NULL)
0
Tom KnowltonWeb developerAuthor Commented:
and if there are several of these ...  how do I and them together?  Like this?

 ( scc.CallNumber LIKE '%' + @vs_QueueNumber + '%' OR @vs_QueueNumber IS NULL) AND   ( scc.Foo LIKE '%' + @vs_Bar + '%' OR @vs_Bar IS NULL)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
yep.  Keep in mind that the more LIKE comparisons used, the slower the query is going to be.

WHERE
 ( scc.CallNumber LIKE '%' + @vs_QueueNumber + '%' OR @vs_QueueNumber IS NULL)  AND  
 ( scc.Foo LIKE '%' + @vs_Bar + '%' OR @vs_Bar IS NULL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tom KnowltonWeb developerAuthor Commented:
Ways to optimize?
0
Tom KnowltonWeb developerAuthor Commented:
Excellent work.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -JIm
0
Tom KnowltonWeb developerAuthor Commented:
Thank you, Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.

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.