[thelist] Conditionals with a SQL Server 2000 Stored Procedure
Scott Brady
evolt at scottbrady.net
Tue Jun 4 17:55:07 CDT 2002
I'm working on converting queries I've written (this is all in ColdFusion 5.0) into Stored Procedures on a SQL Server 2000 db. In the current select statment, I have the following code in CF:
WHERE
pc.category_id = c.category_id
<cfif attributes.category_id NEQ 0>
AND pc.category_id = <cfqueryparam value="#attributes.category_id#" cfsqltype="CF_SQL_INTEGER">
</cfif>
I'm trying to replicate that in the SP with a parameter and have tried both an IF and a CASE:
WHERE
pc.category_id = c.category_id
CASE
@category_id > 0
THEN
AND p.category_id = @category_id
END
<<< or >>>
WHERE
pc.category_id = c.category_id
IF @category_id > 0
AND p.catalog_id = @category_id
In checking the syntax, SQL Server chokes on them with "Incorrect syntax" messages. (@category_id is defined at the top of the SP).
Considering this is my first ever SP (nothing like starting simple), I'm guessing one of you DB gurus will have a pretty elementary answer for me.
Thanks!
Scott
More information about the thelist
mailing list