Re: Conditional SQL

[Gert Franz]

You could use the CASE statement instead.

Or you could write a stored procedure which does exactly the thing you want.

DECLARE @productgroupid bigint;
SET @productgroupid = 5; 
IF @productgroupid > 0 BEGIN
        SELECT Title, ProductID
        FROM aTable
        WHERE   Stock > 0
          AND   m.ProductGroupID = @productgroupid
END ELSE BEGIN
        SELECT Title, ProductID
        FROM aTable
        WHERE   Stock > 0
          AND   SOMTETHINGELSE
END


Greetings / Grüsse
Gert Franz
Customer Care
Railo Technologies GmbH
[EMAIL PROTECTED]
www.railo.ch

Join our Mailing List / Treten Sie unserer Mailingliste bei:
deutsch: http://de.groups.yahoo.com/group/railo/
english: http://groups.yahoo.com/group/railo_talk/



James Smith schrieb:
> I currently have a (very complex) query in the format...
>
> SELECT Title, ProductID
> FROM aTable
> WHERE Stock > 0
> <cfif len(trim(queryParams.productgroupid)) GT 0>
>   AND ProductGroupID = #val(productgroupid)#
> </cfif>
>
> I wish to move this query into MSSQL server for performance reasons, how do
> I go about running the conditional code?  I have tried...
>
> DECLARE @productgroupid bigint;
> SET @productgroupid = 5; 
> SELECT Title, ProductID
> FROM aTable
> WHERE Stock > 0
> IF @productgroupid > 0
>   AND m.ProductGroupID = @productgroupid
>
> But just get syntax errors, is there a way to get the IF...ELSE into the sql
> or do I have to reformat it into...
>
> IF @productgroupid > 0
>       SELECT fields FROM tables WHERE someCondition AND anotherCondition
> ELSE
>       SELECT fields FROM tables WHERE someCondition
>
> Because that is going to make for some really unreadable and LONG code once
> all my conditions are coded for!
>
> --
> Jay
>
>
>
> 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion 8 beta – Build next generation applications today.
Free beta download on Labs
http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280800
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4