CASE expression with SubQueries Efficiency

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)

CASE expression with SubQueries Efficiency

Author

Topic

Geoffv
Starting Member

1 Post

Posted - 2010-08-27 : 02:11:21

Hi All,

I'm pretty new to SQL Queries, but doing some complex things with it.

I need to make a case statement with subqueries efficient any ideas would be appreiated. As you can see, I need to get a count of records with a @ProjectName passed in, that meets criteria determined by @Field passed in, also the case block is repeated for @Global_or_Rep with only minimal canges to the query.

@ProjectName VARCHAR(50) , --the project name retrieved from wherever
@Global_or_Rep VARCHAR(50) , --are we getting the Global or Reported
@Field VARCHAR(50) -- What field are we after? eg S_SP100....
AS
BEGIN
DECLARE @return INT

-- For Rep return where Activity Level is 1
IF @Global_or_Rep = 'Rep'
SELECT @return = CASE @Field
WHEN 'S_SP010 SP'
THEN ( SELECT COUNT(*)
FROM dbo.Activity_Level_Data
WHERE dbo.Activity_Level_Data.Activity_Type_OPP = 'SP'
AND ProjLevel1_Sched_Name_OPP = @ProjectName
AND Activity_Level_OPP = 1
)
--------------
WHEN 'S_SP020 SP Comp'
THEN ( SELECT COUNT(*)
FROM dbo.Activity_Level_Data
WHERE dbo.Activity_Level_Data.Computed_Status_OPP = 'Complete'
AND dbo.Activity_Level_Data.Activity_Type_OPP = 'SP'
AND ProjLevel1_Sched_Name_OPP = @ProjectName
AND Activity_Level_OPP = 1
)
ELSE '0' -- ERROR
END
FROM dbo.Activity_Level_Data

ELSE
-- For Globals Return All Activity Levels
IF @Global_or_Rep = 'Global'
SELECT @return = CASE @Field
WHEN 'S_SP010 SP'
THEN ( SELECT COUNT(*)
FROM dbo.Activity_Level_Data
WHERE dbo.Activity_Level_Data.Activity_Type_OPP = 'SP'
AND ProjLevel1_Sched_Name_OPP = @ProjectName
)
--------------
WHEN 'S_SP020 SP Comp'
THEN ( SELECT COUNT(*)
FROM dbo.Activity_Level_Data
WHERE dbo.Activity_Level_Data.Computed_Status_OPP = 'Complete'
AND dbo.Activity_Level_Data.Activity_Type_OPP = 'SP'
AND ProjLevel1_Sched_Name_OPP = @ProjectName
)
ELSE '0' -- ERROR
END
FROM dbo.Activity_Level_Data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 01:36:19

most of your subqueries look simlar. I think you can wrap all these statements into single select and populate value of variable.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources