use:
IF @ChoiceA IS NULL
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: sebastian palmigiani [mailto:[EMAIL PROTECTED]]
Sent: 08 March 2001 15:49
To: CF-Talk
Subject: Testing for NULL in stored procedures
I am working on a poll which allows multiple selections.
How do I get this strored procedure to work? I am passing null values from
the <cfstoredproc> tag and want to conditionally run a query depending n
whether the value of the variable is NULL or not. I have tried <> NULL, NOT
NULL, !=. With <> nothing happens, and with NOT NULL and != I get syntax
errors.
--------------------------------------------------------------------
CREATE PROCEDURE spMyVote (@PollID VARCHAR(25), @PollType CHAR(3), @ChoiceA
CHAR(1), @ChoiceB CHAR(1), @ChoiceC (1))
AS
IF @PollType = 'MS'
BEGIN
IF @ChoiceA <> NULL
BEGIN
UPDATE PollMultipleChoice
SET AVote = AVote + 1
WHERE PollID = @PollID
END
IF @ChoiceB <> NULL
BEGIN
UPDATE PollMultipleChoice
SET BVote = BVote + 1
WHERE PollID = @PollID
END
IF @ChoiceC <> NULL
BEGIN
UPDATE PollMultipleChoice
SET CVote = CVote + 1
WHERE PollID = @PollID
END
END
----------------------------------------------------
Here is the stored procedure tag:
<cfstoredproc procedure="spMyVote"
datasource="arica">
<cfprocparam type="In"
cfsqltype="CF_SQL_VARCHAR"
dbvarname="@PollID"
value="#TRIM(FORM.PollID)#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@PollType"
value="#TRIM(FORM.PollType)#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@ChoiceA"
value="#Trim(FORM.ChoiceA)#"
null="#IIf(FORM.ChoiceA IS '', 1, 0)#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@ChoiceB"
value="#Trim(FORM.ChoiceB)#"
null="#IIf(FORM.ChoiceB IS '', 1, 0)#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@ChoiceC"
value="#Trim(FORM.ChoiceC)#"
null="#IIf(FORM.ChoiceC IS '', 1, 0)#">
</cfstoredproc>
--------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
