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

Q: SP4

Hello,
I just installed SQL Server 2000, I noticed that there is SP4, do I need to
install SP3 and then SP4 or I just directly jump to SP4 without installing
SP3.
Thanks,Jim
Service Packs are cumulated , so you don'yt need to intstall SP3
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:E9671444-4D27-465F-9D75-08F5894794E0@.microsoft.com...
> Hello,
> I just installed SQL Server 2000, I noticed that there is SP4, do I need
> to
> install SP3 and then SP4 or I just directly jump to SP4 without installing
> SP3.
> Thanks,
>|||Thanks for the reply. Is this the case for all application of Microsoft? I am
just wondering.
"Uri Dimant" wrote:
> Jim
> Service Packs are cumulated , so you don'yt need to intstall SP3
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:E9671444-4D27-465F-9D75-08F5894794E0@.microsoft.com...
> > Hello,
> >
> > I just installed SQL Server 2000, I noticed that there is SP4, do I need
> > to
> > install SP3 and then SP4 or I just directly jump to SP4 without installing
> > SP3.
> > Thanks,
> >
>
>|||Jim
I think yes , however I was talking about SQL Server.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:49DB4A2A-5BBF-4384-9E9A-C6FF0DB8DF2E@.microsoft.com...
> Thanks for the reply. Is this the case for all application of Microsoft? I
> am
> just wondering.
> "Uri Dimant" wrote:
>> Jim
>> Service Packs are cumulated , so you don'yt need to intstall SP3
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:E9671444-4D27-465F-9D75-08F5894794E0@.microsoft.com...
>> > Hello,
>> >
>> > I just installed SQL Server 2000, I noticed that there is SP4, do I
>> > need
>> > to
>> > install SP3 and then SP4 or I just directly jump to SP4 without
>> > installing
>> > SP3.
>> > Thanks,
>> >
>>|||Thanks Uri.
"Uri Dimant" wrote:
> Jim
> I think yes , however I was talking about SQL Server.
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:49DB4A2A-5BBF-4384-9E9A-C6FF0DB8DF2E@.microsoft.com...
> > Thanks for the reply. Is this the case for all application of Microsoft? I
> > am
> > just wondering.
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> Service Packs are cumulated , so you don'yt need to intstall SP3
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:E9671444-4D27-465F-9D75-08F5894794E0@.microsoft.com...
> >> > Hello,
> >> >
> >> > I just installed SQL Server 2000, I noticed that there is SP4, do I
> >> > need
> >> > to
> >> > install SP3 and then SP4 or I just directly jump to SP4 without
> >> > installing
> >> > SP3.
> >> > Thanks,
> >> >
> >>
> >>
> >>
>
>|||JIM.H. wrote:
> Thanks Uri.
> "Uri Dimant" wrote:
>> Jim
>> I think yes , however I was talking about SQL Server.
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:49DB4A2A-5BBF-4384-9E9A-C6FF0DB8DF2E@.microsoft.com...
>> Thanks for the reply. Is this the case for all application of Microsoft? I
>> am
>> just wondering.
>> "Uri Dimant" wrote:
>> Jim
>> Service Packs are cumulated , so you don'yt need to intstall SP3
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:E9671444-4D27-465F-9D75-08F5894794E0@.microsoft.com...
>> Hello,
>> I just installed SQL Server 2000, I noticed that there is SP4, do I
>> need
>> to
>> install SP3 and then SP4 or I just directly jump to SP4 without
>> installing
>> SP3.
>> Thanks,
>>
>>
If you took the time to read a few lines about SP4 on
http://www.microsoft.com/downloads/details.aspx?FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
, you'd see -
"SP4 is a cumulative Service Pack containing all of the fixes from
previous service packs, including MS03-031 security bulletin."
It would have taken you less time than going to this newsgroup and post
the question.
Regards
Steen