Evaluate Stored procedure parameter using case when else....

[malaygal]

I am writing a sql server stored procedure that get passed multiple parameters.
For one of the parameter, I have this pseudo-code

.....
where (@param1 is null or col1 = @param1)
and (@param2 is null or col2 = @param2)
and (@param3 is null or (case when @param3 = 1 then
col3 = 'var1'
when @param1 = 2 then
not(col3 = 'var1)
when @param3 = 3 then
col3 = @param3
else
col3 = 'var2'
end)
)

This errors out on the @param3.
Any help will be greatly appreciated.
I created a dynamic SP that worked fine, but was requested to re-write as non-dynamic.

 

[bborissov]

I doubt that col3 is numeric AND char at the same time:

when @param3 = 3 then
      col3 = @param3 --- Here Col3 is Numeric because @param3 = 3
else
      col3 = 'var2'--- Here Col3 is some kind of char
end)

Borislav Borissov
VFP9 SP2, SQL Server

 

[malaygal]

My fault, by not being careful with my pseudo-code, but the problem I am having is evaluating different columns depending on the value of @param3.

I believe this will work,

....
AND (@param3 is null or col3 = case when @param3 = 1 then 2
else 3
end)


but when you need to evaluate different columns depending on the value of @param3 like below
....
AND (@param3 is null or case when @param3 = 1 then
col3 = 3
else
col2 = 2
end

 

[ecobb]

I always caution people against doing things like this in a stored procedure:
[pre]@param1 is null[/pre]
You are trying to get SQL Server to base its execution plan on something that has noting to do with the database or data in it. This logic is better off in an IF statement than a WHERE clause. You will wind up getting a sub-optimal execution plan, because SQL Server has no idea what it's actually going to be doing at execution time.

Kimberly Tripp does an excellent job explaining why this is a bad idea in her Building High Performance Stored Procedures article. She refers to them as "One Size Fits All" (OSFA) procedures.


Hope This Helps!

Eric Cobb | SQL Server Database Administrator
MCSE: Data Platform
MCSE: Data Management and Analytics

http://www.sqlnuggets.com

"My work is a game, a very serious game." - M.C. Escher

 

[bborissov]

where col1 = ISNULL(@param1, col1)
  and col2 = (ISNULL(@param2, col2) AND CASE WHEN ISNULL(@param3,1) <> 1 THEN 2 ELSE Col2 END)
  and col3 = (ISNULL(@param3, col3) AND CASE WHEN INSULL(@param3,99) =  1 THEN 3 ELSE Col3 END)

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server