insertion problem

[mimi2]

hello, @reason is a list of checkboxes from a coldfusion form (example: '1,2,5' these values go from 1 to 16)
sometimes, none of the checkboxes is checked. in this case i set reason to "".
what i am trying to do with this stored procedure is to insert as many rows as i have elements in my list (3 rows for this example)
my problem is that a zero ( 0 ) is inserted in my table when none of the checkboxes is checked.i just don't see from where this zero comes from.
thanks for any advice.

set @CommaLocation= charindex(',',@reason)


IF @Reason <> ''
BEGIN
WHILE @CommaLocation > 0
BEGIN
set @NewInsert= left(@reason,@CommaLocation - 1)
Insert into table1 (t_ID,ReasonID)
values (@D1,@NewInsert)
set @reason = substring(@reason,@CommaLocation+ 1,50)
set @CommaLocation= charindex(',',@reason)
END
END

 

[Sqlsa]

Hi Mimi,
If you can't debug stored procedure's code from ColdFusion, then use PRINT statement in the Stored Procedure and check..

--add the following line to check for value of @reason
PRINT @reason

Add some other PRINT statements to check whether the control is going inside IF and While Loop. (You have to execute this from SQL Query Analyzer so that you can see the results.)

Make sure that if a check box is not checked then, @reason should be set to '' not &quot;&quot;.(You wrote that you set @reason to &quot;&quot; when no check box is checked)

SELECT @reason=''

IF @reason is char datatype, use rtrim to trim any spaces padded to right.

You can also check this..

Select charindex(',','') ----> returns 0
Select charindex(',','sddfsdfsf') ----> also returns 0

Your code seems to be ok. But there is some mistake occuring when you set the value of @reason.

Send different values for @reason from Query Analyzer and check the operation of Stored Procedure.

Hope it helps. If this does not help, please explain more.

Sreenivas
avnsr@hotmail.com
-----------------

 

[mimi2]

thanks a lot Sreenivas, i realized thati put my if statement at the wrong place!!
have a nice day.