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
