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]

Re: Trapping errors


"Walter" <leonf00@hotmai l.com> wrote in message
news:9ad573a5.0 401120846.6d872 f3d@posting.goo gle.com...[color=blue]
> 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[/color]

Have a look at this:

http://www.sommarskog.se/error-handling-II.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]

Re: Trapping errors

"Simon Hayes" <sql@hayes.ch > wrote in message news:<40030a81$ 1_3@news.bluewi n.ch>...[color=blue]
> "Walter" <leonf00@hotmai l.com> wrote in message
> news:9ad573a5.0 401120846.6d872 f3d@posting.goo gle.com...[color=green]
> > 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[/color]
>
> 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[/color]

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