I've got the a SQL Server stored procedure with the following T-SQL code contained within:
insert into #results ([ID], [Action], [Success], [StartTime], [EndTime], [Process])
select
'ID' = aa.[ActionID],
'Action' = cast(aa.[Action] as int),
'Success' = aa.[Success],
'StartTime' = aa.[StartTime],
'EndTime' = aa.[EndTime],
'Process' = cast(aa.[Process] as int)
from
[ApplicationActions] aa with(nolock)
where
0 = case
when (@loggingLevel = 0) then 0
when (@loggingLevel = 1 and aa.[LoggingLevel] = 1) then 0
end
and
1 = case
when (@applicationID is null) then 1
when (@applicationID is not null and aa.[ApplicationID] = @applicationID) then 1
end
and
2 = case
when (@startDate is null) then 2
when (@startDate is not null and aa.[StartTime] >= @startDate) then 2
end
and
3 = case
when (@endDate is null) then 3
when (@endDate is not null and aa.[StartTime] <= @endDate) then 3
end
and
4 = case
when (@success is null) then 4
when (@success is not null and aa.[Success] = @success) then 4
end
and
5 = case
when (@process is null) then 5
when (@process is not null and aa.[Process] = @process) then 5
end
It's that "dynamic" WHERE clause that is bothering me. The user doesn't have to pass in every parameter to this stored procedure. Just the ones that they are interested in using as a filter for the output.
How would I go about using SQL Server Studio or Profiler to test whether or not this store procedure is recompiling every time?