How to build special where clause based on parameter value?

[GISql]

Hi,

I have a parameter called @Type. This parameter can have different values, like 'STOP', 'START', etc.

Based on this value I have to built different where clauses

LIKE when 'STOP' I have to set: "WHERE STATUS = 0", while when it's 'START' I have to set: "WHERE CHECKED_OUT = 0"

What is the best way to solve this problem?

Thanks in advance,

G.I.

 

[r937]

IF @Type = 'STOP'
begin
set @sql = @sql + ' WHERE STATUS=0'
end
IF @Type = 'START'
begin
set @sql = @sql + ' WHERE CHECKED_OUT=0'
end


r937.com | rudy.ca

 

[GISql]

Ok, this is how I do it now. I just want to get rid of the whole "Let's create a nice query in a string and execute this in a procedure as if we can not do this in .Net code"

I want a nice stored procedure that is easy to maitain, debug, etc. Not building a sql string.

That's what I want a solution for.

But thanks for the input.

 

[r937]

if you have a stored proc, with parameters, and the paremeters have an effect on the sql to be run, such as for example to affect which columns are selected, then i really don't see a better way to build the sql string than by, you know, building the sql string

otherwise, with let's say 3 different parameters each of which can take on 2 values, you are looking at 2³=8 different queries

IF @p1='FOO' AND @p2='BAR' AND @p3='FAP'
SELECT .... /* 1st of 8 queries */
END
IF @p1='FOO' AND @p2='BAR' AND @p3='QUX'
SELECT .... /* 2nd of 8 queries */
END
IF @p1='FOO' AND @p2='GOR' AND @p3='FAP'
SELECT .... /* 3nd of 8 queries */
END

etc.


r937.com | rudy.ca

 

[bborissov]

W/o any dynamic SQLs

SELECT ...
WHERE (@Type='STOP'  AND STATUS      = 0) OR
      (@Type='START' AND CHECKED_OUT = 0)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[GISql]

Ok, then i think that last option is easier to maintain and debug, but what about performance?

 

[bborissov]

What about it?
According to all books I've read it should be faster than any dynamic selects.
But you could test it of course.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.