This seems like a really complicated way of saying "AND ((ProductGroupID = @productgroupid) OR (@productgroupid = 0))". Am I missing something?
-----Original Message----- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 7:31 AM To: CF-Talk Subject: Re: Conditional SQL On 6/13/07, James Smith <[EMAIL PROTECTED]> wrote: > > AND ProductGroupID = CASE WHEN @productgroupid > 0 THEN > > @productgroupid ELSE ProductGroupID END > > This is the simplest to read but has a drawback, it the stored > ProductGroupID is null then the statement becomes AND ProductGroupID = > ProductGroupID and for some reason NULL does not equal NULL so the row > isn't returned, is there a workaround for this? > AND COALESCE(ProductGroupID,0) = CASE WHEN @productgroupid > 0 THEN @productgroupid ELSE COALESCE(ProductGroupID,0) END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:280976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
