[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