q; exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
follows:
Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1
Can I end Sp1 if I catch an error in Sp3Jim
CREATE PROCEDURE BigOne
AS
DECLARE @.err integer
BEGIN TRANSACTION
EXEC @.err = sp1
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp2
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp3
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp4
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> follows:
> Exec Sp1
> Inserts, updates
> Exec Sp2
> Inserts, upadtes
> Exec Sp3
> If error Exit Exec Sp1
> Can I end Sp1 if I catch an error in Sp3
>|||Hi Uri,
Thank you very much for your help. I am currently getting Deadlock message
from time to time, I was wondering how this commit rollback will effect it.
Since there will not be auto-commit for each update (I have many updates
before calling SP2, SP3, and one single commit for all the update, will this
increase the chance of deadlock or decrease it?
"Uri Dimant" wrote:
> Jim
> CREATE PROCEDURE BigOne
> AS
> DECLARE @.err integer
> BEGIN TRANSACTION
> EXEC @.err = sp1
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp2
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp3
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp4
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> COMMIT TRANSACION
> GO
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> > follows:
> >
> > Exec Sp1
> > Inserts, updates
> > Exec Sp2
> > Inserts, upadtes
> > Exec Sp3
> > If error Exit Exec Sp1
> >
> > Can I end Sp1 if I catch an error in Sp3
> >
>
>|||JIM
Read this article
http://www.sql-server-performance.com/deadlocks.asp
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> Hi Uri,
> Thank you very much for your help. I am currently getting Deadlock message
> from time to time, I was wondering how this commit rollback will effect
> it.
> Since there will not be auto-commit for each update (I have many updates
> before calling SP2, SP3, and one single commit for all the update, will
> this
> increase the chance of deadlock or decrease it?
>
> "Uri Dimant" wrote:
>> Jim
>> CREATE PROCEDURE BigOne
>> AS
>> DECLARE @.err integer
>> BEGIN TRANSACTION
>> EXEC @.err = sp1
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp2
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp3
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp4
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> COMMIT TRANSACION
>> GO
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
>> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
>> > follows:
>> >
>> > Exec Sp1
>> > Inserts, updates
>> > Exec Sp2
>> > Inserts, upadtes
>> > Exec Sp3
>> > If error Exit Exec Sp1
>> >
>> > Can I end Sp1 if I catch an error in Sp3
>> >
>>|||Thanks Uri, yes I was reading that article, since the commit will be
performed for a long transaction I will probably get more deadlocks in this
case. Do you agree with me?
"Uri Dimant" wrote:
> JIM
> Read this article
> http://www.sql-server-performance.com/deadlocks.asp
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> > Hi Uri,
> >
> > Thank you very much for your help. I am currently getting Deadlock message
> > from time to time, I was wondering how this commit rollback will effect
> > it.
> > Since there will not be auto-commit for each update (I have many updates
> > before calling SP2, SP3, and one single commit for all the update, will
> > this
> > increase the chance of deadlock or decrease it?
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> CREATE PROCEDURE BigOne
> >> AS
> >> DECLARE @.err integer
> >> BEGIN TRANSACTION
> >> EXEC @.err = sp1
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp2
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp3
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp4
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> COMMIT TRANSACION
> >> GO
> >>
> >>
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> >> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> >> > follows:
> >> >
> >> > Exec Sp1
> >> > Inserts, updates
> >> > Exec Sp2
> >> > Inserts, upadtes
> >> > Exec Sp3
> >> > If error Exit Exec Sp1
> >> >
> >> > Can I end Sp1 if I catch an error in Sp3
> >> >
> >>
> >>
> >>
>
>