case statements

[Pattycake245]

I looked but could not find an existing thread which matches my problem. Basically I have a filtering app that has many variables passed to a stored proc. Some of the variables passed are actual arithmetic operators, =, >=, <=
and so on. Currently I have conditions in my where clause of my query like so:

if(@rsrch_ni_con <> '')
begin
select @SQLString = @SQLString + ' and a.per_rsch_ni ' + @rsrch_ni_con + ' ' + @rsrch_ni_no + ' '
end

Which, if printed out would look like this:

and a.per_rsch_ni = 90 as an example.

I am trying to rewrite this query using case statements instead of dynamic strings. If I was not passing the arithmetic parameter this would be easy, something like:

and a.per_rsch_ni = case when @rsrch_vg_no <> '' then @rsrch_vg_no else a.per_rsch_ni end

this is assuming the query is using "=".

Is it even possible to write case statements for aritmetic operators? Hopefully this makes sense.

Tim

 

[donutman]

Why do you want to do this with SQL? Wouldn't it be a whole lot cleaner to do it in the app? Think about the poor souls who will have to maintain this code after you leave!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Pattycake245]

How do you mean do it in the app? This is a Cold Fusion Application which passes all these fields to the SQL stored proc. The fields which contain the =, >=, <= may not be used so it will be passing a blank. I don't know how doing the verification on the Cold Fusion side is of any benefit. I'm certainly not going to put the SQL code in the App as that is a performance hit.

Tim

 

[donutman]

Sorry, don't know anything about ColdFusion. I was thinking it was a VB or C# app. However, 3 equations you should be aware of:
Dynamic SQL + Web = Bigtime Security problem
Dynamic SQL = Performance hit
Dynamic SQL in SP = No Performance Gain
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Pattycake245]

Yeah, I've been trying to get the syntax to work all morning now with no avail, I may have to redesign this application.

thanks Anyways,

Tim