You can do the following
<cfparam name="queryParams.productgroupid" default="-10001">
and then run the query as
<cfquery>
DECLARE @productgroupid int;
SET @productgroupid = #queryParams.productgroupid#;
SELECT Title, ProductID
FROM aTable
WHERE
Stock > 0
AND
m.ProductGroupID = coalesce(nullIf(@productgroupid,-10001),m.ProductGroupID)
</cfquery>
What that will do is the following:
if @productgroupid is -10001 it will set it to null, and then the coalesce
will set the right side of the where clause to m.ProductGroupID which will mean
the where clause says
AND m.ProductGroupID = m.ProductGroupID
which is always true and therefore not limiting. If however its not -10001,
then it will run the actual query as you want it to.
>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 MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280797
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
