If Else If condition failing

This is only the second stored procedure that I've written, and I'm
having some issues with a conditional statement that I can't figure
out. The statement has a conditional If statement with two Else If's
that checks for a passed parameter's value (an integer I pass when
executing the SP via ASP). Here is basically what I have:
-- @.SET STATUS has 3 valid values:
-- 1 - Don't set status
-- 2 - Set inactive
-- 3 - Set active
CREATE PROCEDURE dbo.sp_SomeProcedure
@.SET_STATUS int
AS
SET NOCOUNT ON
IF @.SET_STATUS = 1
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE
BEGIN
INSERT STATEMENT
END
ELSE IF @.SET_STATUS = 2
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE IF @.SET_STATUS = 3
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
GO
When @.SET_STATUS is set to either 1 or 2, the sequel statements run
fine and the corresponding row gets updated or inserted accordingly.
If however the @.SET_STATUS is passed as 3, the procedure executes fine,
but the update statement is not run.
I've manually plugged in the "exists" condition and the update
statement for this part of the procedure and they fire off correctly
when run in QA. I'm at a loss. I've pretty much determined that it is
failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
why since it passes the syntax check.
Am I missing something here? Thanks in advance!Here's how I recommend the structure:
IF @.SET_STATUS = 1
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 2
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 3
BEGIN
.. do stuff ...
END
There's no need for ELSE, and you should always wrap the result of an IF
statement in BEGIN/END.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||The ELSE IF @.SET_STATUS = 3 is the else of the IF EXISTS() under
@.SET_STATUS = 2
So it's never actually getting to the IF @.SET_STATUS = 3 statement.
Add BEGIN..END around the IF EXISTS() under each ELSE IF
e.g.
ELSE IF @.SET_STATUS = 2
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
ELSE IF @.SET_STATUS = 3
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
tsigler@.gmail.com wrote:
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||Aaron, you're a rock star! It must not have liked the second "else if"
and after updating like you suggested, everything works like a champ.
Thanks!!!|||Thanks Trey ;)|||Depending on what you're doing in the 3 updates, you may be able to combine
these into 1 statement.
> IF @.SET_STATUS = 1 AND NOT EXISTS (SELECT STATEMENT)
> BEGIN
> INSERT STATEMENT
> END
ELSE
> BEGIN
> UPDATE STATEMENT
> END
Explain what the 3 updates do in your procedure and maybe we can suggest a
better way.
In this part:
> ELSE IF @.SET_STATUS = 2 (or 3)
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
I don't think that you need to check IF EXISTS unless you are performing an
action if this is FALSE.
Your update statement should affect 0 rows if it doesn't exist.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>

If conditional problem in T-Sql

I encounter a T-Sql problem related to if conditional processing:

The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.

Could anyone offer some advice?

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.

|||

Well the code that you have written is fine it should work perfectly.

Can you provide the code that you are using for droping the column of the table ?

|||

Alternatively if you want to check for existence of a column you could query the syscoumns table:

IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))
Begin
 --Do your insert
End

|||

Hi,

Thanks for your alternative way of querying system table for column existence.

However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.

This is a very strange phenomena.

- Yubo

|||Can you repost your new query and the error message pls?|||

From your earlier which I am copy pasting here:

**************************************************

IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO

***************************************************

It shows that ReportPreferences is the name of your table while the column name isReportTitle

While if you have just copy pasted the querry from ndinakar which is :

***************************************************

IF

EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))

Begin

-- Do your insert

End

***************************************************

The sequence of the name of the table is wrong.

Please try this instead and I am sure your problem would be solved :) .

If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End

And if this post does answer your question please dont hesitate to mark it as Answer.

Regards,

sql