Re:Switch case within WHERE clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Re:Switch case within WHERE clause

Author

Topic

expectomas
Starting Member

3 Posts

Posted - 2011-09-09 : 04:50:47

Hi,

I want to do the following:

CREATE PROCEDURE
@CategoryId bigint
@BrandId bigint
@Type bigint

SELECT * FROM ViewAllProdHir VAPH
WHERE
(
CASE @TYPE
WHERE 1 THEN VAPH.BrandId = @BrandId
WHERE 2 THEN VAPH.CategoryId = @CategoryId
)

Currently I am using HUGE IF @TYPE=something ELSE statements where each condition contains the same expression just that the WHERE clause is.

Anybody knows how to resolve this?

Best Regards
Ethan Lim
www.ethanlim.net

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:05:09

Dynamic SQL - ideally by using a parameterised query via sp_ExecuteSQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:16:27

[code]SELECT * FROM ViewAllProdHir VAPH
WHERE
( @TYPE = 1 AND VAPH.BrandId = @BrandId)
OR
(@TYPE = 2 AND VAPH.CategoryId = @CategoryId)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 10:04:01

Depending on the O/P's definition of "huge"

in "I am using HUGE IF @TYPE=something ELSE" I expect either CASE or "(X AND Y) OR ..." will suffer from lousy performance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-09-09 : 10:28:34

quote:
Originally posted by Kristen

Depending on the O/P's definition of "huge" in "I am using HUGE IF @TYPE=something ELSE" I expect either CASE or "(X AND Y) OR ..." will suffer from lousy performance

Agree 100% on this. They are very likely to turn into full table scans with bad performance.

I assume this is in a stored procedure, so there is really no reason not to use dynamic SQL. With versions earlier than SQL 2005, the user executing the query had to have permission to the underlying objects when you used dynamic SQL. With the addition of the EXECUTION AS option for stored procedures in SQL 2005, you can give the stored procedure permission to the underlying objects without granting them directly to the user.

CODO ERGO SUM

expectomas
Starting Member

3 Posts

Posted - 2011-09-10 : 05:10:44

Thanks for the advice.

I think I will try to compare (X AND Y) with dynamic SQL and monitor for any improvements performance

Best Regards
Ethan Lim
www.ethanlim.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-10 : 07:49:42

And remember this HUGE thingy is selecting from a view, which by itself can have bad performance...

1) Either rewrite code as this

SELECT	*
FROM	dbo.ViewAllProdHir
WHERE	CASE
		WHEN @Type = 1 AND BrandID = @BrandID THEN 1
		WHEN @Type = 2 AND CategoryID = @CategoryID THEN 1
		ELSE 0
	END = 1

Or make sure than only one parameter can be non-NULL.

2) If so, your code can look cleaner like this

-- Prepare user supplied parameter values
SELECT	@BrandID = CASE WHEN @Type = 1 THEN @BrandID ELSE NULL END,
	@CategoryID = CASE WHEN @Type = 2 THEN @CategoryID ELSE NULL END

-- Get the data
SELECT	*
FROM	dbo.ViewAllProdHir
WHERE	(BrandID = @BrandID OR @BrandID IS NULL)
	AND (CategoryID = @CategoryID OR @CategoryID IS NULL)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-10 : 07:52:43

3) Or a hybrid of both the above

SELECT	*
FROM	dbo.ViewAllProdHir
WHERE	(@Type = 1 AND BrandID = @BrandID OR @Type <> 1)
	AND (@Type = 2 AND BrandID = @BrandID OR @Type <> 2)

N 56°04'39.26"
E 12°55'05.63"

expectomas
Starting Member

3 Posts

Posted - 2011-09-14 : 06:31:06

Question: How about @type=0 which stands for SELECT all regardless of brand,category? how do you integrate into the statement below?

quote:
Originally posted by SwePeso

3) Or a hybrid of both the above
SELECT *
FROM dbo.ViewAllProdHir
WHERE (@Type = 1 AND BrandID = @BrandID OR @Type <> 1)
AND (@Type = 2 AND BrandID = @BrandID OR @Type <> 2)

N 56°04'39.26"
E 12°55'05.63"

Best Regards
Ethan Lim
www.ethanlim.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:24:02

[code]
SELECT *
FROM dbo.ViewAllProdHir
WHERE (@Type = 1 AND BrandID = @BrandID OR @Type <> 1)
AND (@Type = 2 AND BrandID = @BrandID OR @Type <> 2)
OR @type=0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources