Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to build special where clause based on parameter value?

Status
Not open for further replies.

GISql

Programmer
May 23, 2006
12
NO
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.
 
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
 
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.
 
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 23=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
 
W/o any dynamic SQLs
Code:
SELECT ...
WHERE (@Type='STOP'  AND STATUS      = 0) OR
      (@Type='START' AND CHECKED_OUT = 0)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
 
Ok, then i think that last option is easier to maintain and debug, but what about performance?
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top