SQL Server Performance Forum – Threads Archive

Dynamic Vs Case

Hi All could any one of you tell me the reasonwhich is better option
for following ALTER PROCEDURE SampleSP
( @Code nvarchar(10),
@Name nvarchar(50),
@InitiatedBy int,
@surveyType int,
@FromPlanStartDate DateTime,
@ToPlanStartDate DateTime,
@FromActualStartDate DateTime,
@ToActualStartDate DateTime,
@FromPlanEndDate DateTime,
@ToPlanEndDate DateTime,
@FromActualEndDate DateTime,
@ToActualEndDate DateTime
)
AS
BEGIN
SELECT Program.[ID]
,Program.

Code:

    ,Program.[Name]
    ,lookup.[value] AS [ProgramInitiator] ,
    'usa' AS [ClientName]
    --,Program.[ProgramTypeID]
    ,lookup.[value] AS SurveyType
    ,Program.[Reason]
    ,[StartDatetime] as PlanDate
,[ApprovedDateTime] as ActualStartDate
    ,[ClosureRequestDatetime] as PlanEndDate
    ,[ClosedDatetime] as ActualEndDate
    ,lookup.[value] AS [SurveyStatus]
    /*,[RequestedUserID]
    ,[ApprovedUserID]
    ,[ClosedByUserID]as ActualStartDate
    ,lookup.[value] AS [SurveyStatus]
    ,lookup.[value] AS [ProgramInitiator]*/
   FROM [EYProgramInfo] Program
   INNER JOIN  EYLookupValue lookup
   ON Program.ProgramInitiator = lookup.[ID]
   AND Program.SurveyTypeID = lookup.[ID]
   AND Program.ProgramInitiator = lookup.[ID]
   AND Program.SurveyStatusID = lookup.[ID]
WHERE
-- Program Code
    Program.[Code] =  CASE WHEN @Code = '' THEN  Program.[Code]
                                   ELSE @Code
                      END
AND
-- Program Name
Program.[Name]= CASE WHEN @Name = '' THEN Program.[Name]
                           ELSE @Name
                      END
AND
-- InitiatedBy
lookup.[value]=CASEWHEN @surveyType = -1 THEN lookup.[value]
ELSE @surveyType
END
AND
-- Survey Type
lookup.[value]=CASEWHEN @InitiatedBy = -1 THEN lookup.[value]
ELSE @InitiatedBy
END
AND
-- Plan Start Date
StartDatetime BETWEEN @FromPlanStartDate AND @ToPlanStartDate
AND
-- Actual Start Date
ApprovedDateTime BETWEEN @FromActualStartDate AND @ToActualStartDate
AND
-- Plan End Date
ClosureRequestDatetime BETWEEN  @FromPlanEndDate AND @ToPlanEndDate
AND
-- Actual End Date
ClosedDatetime BETWEEN  @FromActualEndDate AND @ToActualEndDate
END
[B]Or else I will GO For Dynamic SQL[/B]
Shreyas..

The optimal solution would be to have separate procedures for the different sets of criteria, but of course that can easily turn into a maintenance nightmare. If you prefer to avoid dynamic SQL, you could try using OR instead of CASE here – WHERE (Program.

Code:

 = @Code OR @Code = '')
etc.
I created a small batch script of two simple queries with the CASE and the OR syntax, and the OR syntax scores a lower query cost than the CASE - but not much.
But then as soon as I added a second filter, the CASE syntax scored close to 0%, with the OR syntax over 99% ...
Difficult to predict what will happen with real data.

Check out this article:<a target="_blank" href=http://www.sommarskog.se/dyn-search.html>http://www.sommarskog.se/dyn-search.html</a> In complex search scenarios dynamic sql is mostly the winner, though this isn’t a carte blanche to use it all over the place, of course. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>