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.
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.