Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Avoiding IF/ELSE Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 5:52 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:01 PM
Points: 643, Visits: 3,695
--I believe the DDL and my commented out notes and questions should explain what I am hoping to achieve

CREATE TABLE #Companies (CompanyID int)
INSERT INTO #Companies (CompanyID) VALUES (1)
INSERT INTO #Companies (CompanyID) VALUES (2)
INSERT INTO #Companies (CompanyID) VALUES (3)

CREATE TABLE #CompanyGroups (CompanyGroupID char(1), CompanyID int)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',1)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',2)

DECLARE @CompanyID int
DECLARE @CompanyGroupID char(1)

--Scenario 1
--@CompanyID is populated, CompanyGroupID is NULL

SET @CompanyID = 1
SET @CompanyGroupID = NULL

IF @CompanyID IS NOT NULL
BEGIN
SELECT * FROM #Companies WHERE CompanyID = @CompanyID
END

--Scenario 2
--@CompanyID is NULL, CompanyGroupID is populated

SET @CompanyID = NULL
SET @CompanyGroupID = 'A'

IF @CompanyID IS NULL
BEGIN
SELECT * FROM #Companies WHERE CompanyID IN(Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID)
END

--So then I can do if else
IF @CompanyID IS NOT NULL
BEGIN
SELECT * FROM #Companies WHERE CompanyID = @CompanyID
END
ELSE
BEGIN
SELECT * FROM #Companies WHERE CompanyID IN(Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID)
END

--SELECT * FROM #Companies
--SELECT * FROM #CompanyGroups
DROP TABLE #Companies
DROP TABLE #CompanyGroups


--The question: My select statement is complicated and I'd prefer not to duplicate it in each part of the if/else statement.
--Can I build a single SELECT statement that will determine if @CompanyID IS NOT NULL and select the appropriate records??

Post #1385405
Posted Thursday, November 15, 2012 8:22 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:39 AM
Points: 818, Visits: 2,485
It's a bit ugly ... but you could do something like

;with companyNotNull as (
SELECT * FROM #Companies WHERE CompanyID = @CompanyID
),
companyNull as (
Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID
)
SELECT *
FROM companyNotNull
WHERE @companyID is not null
UNION ALL
SELECT *
FROM companyNull
WHERE @companyID is null

Of course this replicates the if ... then ... else structure. If both values are set then the companyID takes precedence.
Post #1385435
Posted Thursday, November 15, 2012 8:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:05 PM
Points: 3,594, Visits: 5,104
In addition to MickyT's suggestion, you could do a catch all query or the dynamic SQL method suggested in this article by Gail Shaw:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Watch out for SQL injection if the parameter values come from the UI if you use the dynamic SQL method though.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385436
Posted Thursday, November 15, 2012 10:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, March 13, 2014 3:40 AM
Points: 880, Visits: 668
PLS TRY BELOW CODE
CREATE TABLE #Companies (CompanyID int)
INSERT INTO #Companies (CompanyID) VALUES (1)
INSERT INTO #Companies (CompanyID) VALUES (2)
INSERT INTO #Companies (CompanyID) VALUES (3)

CREATE TABLE #CompanyGroups (CompanyGroupID char(1), CompanyID int)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',1)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',2)
DECLARE @CompanyID int
DECLARE @CompanyGroupID char(1)
--SET @CompanyID = 1
--SET @CompanyGroupID = NULL
SET @CompanyID = NULL
SET @CompanyGroupID = 'A'

SELECT * FROM #Companies WHERE CompanyID = (CASE WHEN @CompanyID IS NULL THEN CompanyID ELSE @CompanyID END)
AND CompanyID IN(Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = CASE WHEN @CompanyGroupID IS NULL THEN CompanyGroupID ELSE @CompanyGroupID END)





Post #1385467
Posted Thursday, November 15, 2012 10:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:05 PM
Points: 3,594, Visits: 5,104
mickyT (11/15/2012)
It's a bit ugly ... but you could do something like

;with companyNotNull as (
SELECT * FROM #Companies WHERE CompanyID = @CompanyID
),
companyNull as (
Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID
)
SELECT *
FROM companyNotNull
WHERE @companyID is not null
UNION ALL
SELECT *
FROM companyNull
WHERE @companyID is null

Of course this replicates the if ... then ... else structure. If both values are set then the companyID takes precedence.


It occurred to me on lookback that this is going to fail if #Companies has more than one column or that column has a data type that is incompatible with CompanyID from #CompanyGroups.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385471
Posted Thursday, November 15, 2012 10:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:05 PM
Points: 3,594, Visits: 5,104
I think this is the "normal" catch all query form for this.

SELECT * 
FROM #Companies
WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID) AND
(@CompanyGroupID IS NULL OR
CompanyID IN(Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID))





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1385473
Posted Thursday, November 15, 2012 11:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 12:05 AM
Points: 412, Visits: 74
Additional questions. What results should be for this?

CREATE TABLE #Companies (CompanyID int)
INSERT INTO #Companies (CompanyID) VALUES (1)
INSERT INTO #Companies (CompanyID) VALUES (2)
INSERT INTO #Companies (CompanyID) VALUES (3)

CREATE TABLE #CompanyGroups (CompanyGroupID char(1), CompanyID int)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',1)
INSERT INTO #CompanyGroups (CompanyGroupID,CompanyID) VALUES ('A',2)

a.
SET @CompanyID = 1
SET @CompanyGroupID = 'A'
b.
SET @CompanyID = 1
SET @CompanyGroupID = 'B'
c.
SET @CompanyID = 3
SET @CompanyGroupID = 'A'
d.
SET @CompanyID = 3
SET @CompanyGroupID = 'E'
e.
SET @CompanyID = NULL
SET @CompanyGroupID = NULL
Post #1385483
Posted Thursday, November 15, 2012 11:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 12:05 AM
Points: 412, Visits: 74
You could think about something like this:

SELECT Comp.*
FROM #Companies AS Comp
LEFT JOIN #CompanyGroups AS Gr ON Comp.CompanyID = Gr.CompanyID
WHERE Comp.CompanyID = @CompanyID
OR GR.CompanyGroupID = @CompanyGroupID

Post #1385484
Posted Friday, November 16, 2012 6:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 03, 2014 4:01 AM
Points: 63, Visits: 277
SELECT *
FROM
#Companies
WHERE CompanyID = @CompanyID
OR
(
CompanyID IS NULL AND
CompanyID IN(Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID)

)
Post #1385662
Posted Friday, November 16, 2012 11:10 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:01 PM
Points: 643, Visits: 3,695
>>Additional questions. What results should be for this?

The parameters will be validated through an user interface so I don't expect any of those conditions to exist.
Post #1385795
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse