• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

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

0
Tom Knowlton
Asked:
Tom Knowlton
  • 4
  • 3
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now