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!
>

IDENTITY column in SQL 2000 and linked tables in MS Access

Please help

We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?

Regards Anna-LenaIt all depends

How is the application written...

Do all of your controls have a data source property that is a table in SQL Server?

Or do you use unbound controls...

You might be better off posting here though

http://www.dbforums.com/forumdisplay.php?f=84

I doubt it's a problem with IDENTITY though|||The problem is that Access does not work will with large numbers of users. Anything above six to ten simultaneous users can cause problems.
I assume you have a form linked to a table or a view in your SQL Server database. Well, Access likes to copy down the ENTIRE recordset so that you can step through the results. When you have several people who each have loaded their own local copies of the same recordset and then try inserting a new record, I'd guess it plays havoc with the locking.
You might try having each user download a filtered subset of the data, reducing the number of copies of the same record that are spread over multiple terminals.|||The only way this works is to have all unbound controls, using rs.whatever and write code to fill in the controls, and to perform dml back to the database|||Which, in my opinion, takes away most of the advantages of using MS Access. So the application may as well be written on a more robust platform.|||Which, in my opinion, takes away most of the advantages of using MS Access. Apart from it being a one stop shop GUI\ report generator that can be distributed with xcopy you mean? A disconnected Access app is by no means the best solution in many situations but I would use one over a linked Access version any day**. And I would use Access over (for example) .NET in many circumstances too. In fact I do.

EDIT - ** Actually I got over excited and fibbed there. A disconnected app might be overdesigned for many applications (e.g. those that are accessed by a very small number of people).|||I use Access for rapid application development of apps with low user counts, and for that ADP files work fine. I never use linked tables, though.