Try IS NOT NULL
Craig
> -----Original Message-----
> From: sebastian palmigiani [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, March 08, 2001 10:49 AM
> 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
