Avoiding IF/ELSE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Avoiding IF/ELSE

15 posts, Page 1 of 2

1 2 »»

Avoiding IF/ELSE

Rate Topic

Display Mode

Topic Options

Author

Message

Chrissy321

Posted Thursday, November 15, 2012 5:52 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 552, Visits: 2,995

--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

mickyT

Posted Thursday, November 15, 2012 8:22 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 300, Visits: 1,126

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

dwain.c

Posted Thursday, November 15, 2012 8:41 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1385436

subbareddy542

Posted Thursday, November 15, 2012 10:14 PM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 4:17 AM
Points: 832, Visits: 613

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

dwain.c

Posted Thursday, November 15, 2012 10:22 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

Post #1385471

dwain.c

Posted Thursday, November 15, 2012 10:42 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,345, Visits: 3,191

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))

Post #1385473

elgatitto

Posted Thursday, November 15, 2012 11:35 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:37 PM
Points: 214, Visits: 45

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
Unsure

Post #1385483

elgatitto

Posted Thursday, November 15, 2012 11:36 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:37 PM
Points: 214, Visits: 45

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

Sony Francis @EY

Posted Friday, November 16, 2012 6:40 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:07 AM
Points: 62, Visits: 227

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

)

Post #1385662

Chrissy321

Posted Friday, November 16, 2012 11:10 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 552, Visits: 2,995

>>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 »

15 posts, Page 1 of 2

1 2 »»

Permissions