Trapping errors

[Walter]

Hi,
I have a stored proc StoredProc1 =
{
INSERT INTO Table1
SELECT *
FROM View1
Return @@ERROR
}

StoredProc1 is used in another sp StoredProcMain =
{
(some code before)...
EXEC @iResult = StoredProc1
If @iResult <> 0
BEGIN
ROLLBACK TRANSACTION
Return @iResult
END
.... (continue)
}

So I want to rollback if StoredProc1 is not successful.

Then I ran into a problem. I added a column to Table1 but forgot to
update View1 to add the equivalent column. When I executed
StoredProc1, I got the "Insert Error: Column name or number of
supplied values does not match table definition." But the error is
NOT trapped. It seems the instruction "Return @@ERROR" returns 0 and
StoredProcMain goes on as if there wasn't an error.

How can I trap this error?

Thanks
Walter

[Simon Hayes]

"Walter" <le*****@hotmail.com> wrote in message
news:9a**************************@posting.google.c om...

Hi,
I have a stored proc StoredProc1 =
{
INSERT INTO Table1
SELECT *
FROM View1
Return @@ERROR
}

StoredProc1 is used in another sp StoredProcMain =
{
(some code before)...
EXEC @iResult = StoredProc1
If @iResult <> 0
BEGIN
ROLLBACK TRANSACTION
Return @iResult
END
... (continue)
}

So I want to rollback if StoredProc1 is not successful.

Then I ran into a problem. I added a column to Table1 but forgot to
update View1 to add the equivalent column. When I executed
StoredProc1, I got the "Insert Error: Column name or number of
supplied values does not match table definition." But the error is
NOT trapped. It seems the instruction "Return @@ERROR" returns 0 and
StoredProcMain goes on as if there wasn't an error.

How can I trap this error?

Thanks
Walter

Have a look at this:

http://www.sommarskog.se/error-handl....html#SP-check

This is describing your situation - the INSERT error means that "the
invocation of the procedure as such fails", as Erland puts it, so that the
return code is not set, and @iResult keeps its previous value. That is
presumably 0 in your case, or perhaps NULL if you didn't initialize the
variable. In either case, Erland's solution should work correctly for you.

Simon

[Walter]

"Simon Hayes" <sq*@hayes.ch> wrote in message news:<40**********@news.bluewin.ch>...

"Walter" <le*****@hotmail.com> wrote in message
news:9a**************************@posting.google.c om...

Hi,
I have a stored proc StoredProc1 =
{
INSERT INTO Table1
SELECT *
FROM View1
Return @@ERROR
}

StoredProc1 is used in another sp StoredProcMain =
{
(some code before)...
EXEC @iResult = StoredProc1
If @iResult <> 0
BEGIN
ROLLBACK TRANSACTION
Return @iResult
END
... (continue)
}

So I want to rollback if StoredProc1 is not successful.

Then I ran into a problem. I added a column to Table1 but forgot to
update View1 to add the equivalent column. When I executed
StoredProc1, I got the "Insert Error: Column name or number of
supplied values does not match table definition." But the error is
NOT trapped. It seems the instruction "Return @@ERROR" returns 0 and
StoredProcMain goes on as if there wasn't an error.

How can I trap this error?

Thanks
Walter

Have a look at this:

http://www.sommarskog.se/error-handl....html#SP-check

This is describing your situation - the INSERT error means that "the
invocation of the procedure as such fails", as Erland puts it, so that the
return code is not set, and @iResult keeps its previous value. That is
presumably 0 in your case, or perhaps NULL if you didn't initialize the
variable. In either case, Erland's solution should work correctly for you.

Simon

Many thanks, your information was really good. And the link: right to
the point! Wow!