[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Russell Mangel

3/9/2007 11:40:00 AM

Hi, I am using SQL Server 2005 (Standard), and the client is using VS2005
C#.

Question:
Can you improve my stored procedure?
I don't like using @@ROWCOUNT for COMMIT TRANSACTION.
Maybe it would be better to write 3 stored procedures, and use them inside
another SP?
How should this be done?

In my database I have 3 tables that represent Inventory, I wish to create a
single stored procedure that updates all three tables at once, if an error
occurs during insert then all rows are rolled back.

Currently I am doing the following:
-- Input values have been ommited

BEGIN TRANSACTION
DECLARE @RowCount INT
INSERT INTO T1 VALUES(...);
SET @RowCount = @@ROWCOUNT
INSERT INTO T2 VALUES(...);
SET RowCount = @RowCount + @@ROWCOUNT
INSERT INTO T3 VALUES(...);
SET RowCount = @RowCount + @@ROWCOUNT
IF @RowCount = 3
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Insert Failed, 16, 1);
END

Thanks
Russell Mangel
Las Vegas, NV




17 Answers

Dan Guzman

3/9/2007 12:04:00 PM

0

I would not use @@ROWCOUNT to determine success/failure of individual
statements. Instead, you can check @@ERROR or use TRY/CATCH.

Personally, I like to specify SET XACT_ABORT ON in stored procedures that
start explicit transactions. This will abort the entire batch when an error
occurs and avoid leaving a transaction open after client-side attention
events, like timeouts.

For a more thorough discussion on error handling in stored procedures, see
Erland's error handling articles at http://www.somm...

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Russell Mangel" <russell@tymer.net> wrote in message
news:uS5o8%23jYHHA.4368@TK2MSFTNGP06.phx.gbl...
> Hi, I am using SQL Server 2005 (Standard), and the client is using VS2005
> C#.
>
> Question:
> Can you improve my stored procedure?
> I don't like using @@ROWCOUNT for COMMIT TRANSACTION.
> Maybe it would be better to write 3 stored procedures, and use them inside
> another SP?
> How should this be done?
>
> In my database I have 3 tables that represent Inventory, I wish to create
> a single stored procedure that updates all three tables at once, if an
> error occurs during insert then all rows are rolled back.
>
> Currently I am doing the following:
> -- Input values have been ommited
>
> BEGIN TRANSACTION
> DECLARE @RowCount INT
> INSERT INTO T1 VALUES(...);
> SET @RowCount = @@ROWCOUNT
> INSERT INTO T2 VALUES(...);
> SET RowCount = @RowCount + @@ROWCOUNT
> INSERT INTO T3 VALUES(...);
> SET RowCount = @RowCount + @@ROWCOUNT
> IF @RowCount = 3
> COMMIT TRANSACTION;
> ELSE
> BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Insert Failed, 16, 1);
> END
>
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>
>
>

Paul L

12/1/2011 3:28:00 PM

0

On 11/30/2011 8:14 PM, Ray O'Hara wrote:
> "Paul L"<paul@kbtrans.net> wrote in message
> news:jb5lgq$pmo$2@dont-email.me...
>> On 11/29/2011 9:35 PM, Neil X. wrote:
>>> On Nov 29, 8:06 pm, Andrew<amuraw...@gmail.com> wrote:
>>>> On Nov 29, 4:43 pm, "Ray O'Hara"<raymond-oh...@hotmail.com> wrote:
>>>>
>>>>> Do you remember Hershal Walker, brilliant athlete.
>>>>> one of the best college players ever, just like Tebow.
>>>>
>>>> You're right (there's something I've never said to Ray Ray), Herschel
>>>> too was one of the greatest football players ever.
>>>
>>>
>>> No one who was an excellent player for only 3-4 years qualifies as a
>>> "great football player." You've got to be stellar for a lot longer
>>> than that to be great. Feel free to call college players who were
>>> really good "great college football players." But to be a truly great
>>> football player, that requires playing at a high level far longer than
>>> only 4 years.
>>>
>>> Peace,
>>> Neil X.
>>
>> Billy Simms was a great football player who played in 60 NFL games.
>
> Yes Simms was a do it all back. a great runner who was also as good as any
> WR at pass catching. Detroit had a great offense in his day. to bad their
> defense sucked.

Due to injury, Billy Simms "prime" didn't last long enough for today's
casual fan to be aware of what a dominating player he was.

Just Kidding

12/1/2011 5:47:00 PM

0

On Thu, 01 Dec 2011 08:28:07 -0700, Paul L <paul@kbtrans.net> wrote:

>On 11/30/2011 8:14 PM, Ray O'Hara wrote:
>> "Paul L"<paul@kbtrans.net> wrote in message
>> news:jb5lgq$pmo$2@dont-email.me...
>>> On 11/29/2011 9:35 PM, Neil X. wrote:
>>>> On Nov 29, 8:06 pm, Andrew<amuraw...@gmail.com> wrote:
>>>>> On Nov 29, 4:43 pm, "Ray O'Hara"<raymond-oh...@hotmail.com> wrote:
>>>>>
>>>>>> Do you remember Hershal Walker, brilliant athlete.
>>>>>> one of the best college players ever, just like Tebow.
>>>>>
>>>>> You're right (there's something I've never said to Ray Ray), Herschel
>>>>> too was one of the greatest football players ever.
>>>>
>>>>
>>>> No one who was an excellent player for only 3-4 years qualifies as a
>>>> "great football player." You've got to be stellar for a lot longer
>>>> than that to be great. Feel free to call college players who were
>>>> really good "great college football players." But to be a truly great
>>>> football player, that requires playing at a high level far longer than
>>>> only 4 years.
>>>>
>>>> Peace,
>>>> Neil X.
>>>
>>> Billy Simms was a great football player who played in 60 NFL games.
>>
>> Yes Simms was a do it all back. a great runner who was also as good as any
>> WR at pass catching. Detroit had a great offense in his day. to bad their
>> defense sucked.
>
>Due to injury, Billy Simms "prime" didn't last long enough for today's
>casual fan to be aware of what a dominating player he was.

Sims.

mr.rapidan

12/1/2011 5:59:00 PM

0

Seth Maxwell . . .

Andrew

12/1/2011 8:11:00 PM

0

On Nov 30, 8:19 pm, frndthdevl <frndthd...@aol.com> wrote:
> On Nov 30, 7:22 pm, "Neil X." <nei...@yahoo.com> wrote:
>
> > > Andrew wrote:
>
> > > To me, Gale Sayers, for instance, is a vastly superior player to, say,
> > > Emmitt Smith. Not even in the same ballpark. Then again, I think Bill
> > > Walton is more or less the equal of Kareem Abdul-Jabaar.
>
> > OK then.  I'll bear that in mind next time I read your posts about
> > sports.
>
> > Yeesh,
> > Neil X.
>
> So which are you disagreeing Neil? I am old enough to remember seeing
> Gayle Sayers live, and he was all that. Not sure how much live Gayle
> you saw even living in Chicago. Emmitt Smith and Kareem's longevity
> certainly adds a little more weight in over all greatness, but as far
> as moments in time, hard to argue with what Andrew said. Of course you
> and Andrew tend to the opposite at times.  Anybody under estimating
> the greatness that was Bill Walton, well probably did not see much
> live Walton either.

Walton's 1977 season has never been surpassed. Walton 1977 is the
basketball equivalent of 4/8/72 Dark Star.

Andrew

12/1/2011 8:21:00 PM

0

On Dec 1, 3:25 am, marcman <marcmanstud...@gmail.com> wrote:
> On Nov 30, 10:23 pm, "Neil X." <nei...@yahoo.com> wrote:
>
>
>
> > On Nov 30, 11:27 am, Paul L <p...@kbtrans.net> wrote:
>
> > > On 11/29/2011 9:35 PM, Neil X. wrote:
>
> > > > On Nov 29, 8:06 pm, Andrew<amuraw...@gmail.com>  wrote:
> > > >> On Nov 29, 4:43 pm, "Ray O'Hara"<raymond-oh...@hotmail.com>  wrote:
>
> > > >>> Do you remember Hershal Walker, brilliant athlete.
> > > >>> one of the best college players ever, just like Tebow.
>
> > > >> You're right (there's something I've never said to Ray Ray), Herschel
> > > >> too was one of the greatest football players ever.
>
> > > > No one who was an excellent player for only 3-4 years qualifies as a
> > > > "great football player."  You've got to be stellar for a lot longer
> > > > than that to be great.  Feel free to call college players who were
> > > > really good "great college football players."  But to be a truly great
> > > > football player, that requires playing at a high level far longer than
> > > > only 4 years.
>
> > > > Peace,
> > > > Neil X.
>
> > > Billy Simms was a great football player who played in 60 NFL games.
>
> > No, he wasn't.
>
> > Peace,
> > Neil X.
>
> Agree with X.

It's amazing to me that Deadheads are so quick to throw away flashes
of greatness in favor of longevity at a lesser level. Sure, the GD had
longevity, but I don't think anybody loves the GD because they were
really good for a long time. They love the GD because they had moments
and stretches of pure genius. If the GD had ceased to exist after the
2/27/69-3/2/69 run, they still go down as one of the greatest bands
ever, IMO.

Billy Sims, Gale Sayers, Bill Walton, Earl Campbell, Bo Jackson. They
may not have had the longevity, but anyone who wants to pretend that
they didn't attain greatness is operating under a drastically
different set of parameters than I am.

Neil X

12/2/2011 3:59:00 AM

0

On Dec 1, 3:21 pm, Andrew <amuraw...@gmail.com> wrote:
> On Dec 1, 3:25 am, marcman <marcmanstud...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Nov 30, 10:23 pm, "Neil X." <nei...@yahoo.com> wrote:
>
> > > On Nov 30, 11:27 am, Paul L <p...@kbtrans.net> wrote:
>
> > > > On 11/29/2011 9:35 PM, Neil X. wrote:
>
> > > > > On Nov 29, 8:06 pm, Andrew<amuraw...@gmail.com>  wrote:
> > > > >> On Nov 29, 4:43 pm, "Ray O'Hara"<raymond-oh...@hotmail.com>  wrote:
>
> > > > >>> Do you remember Hershal Walker, brilliant athlete.
> > > > >>> one of the best college players ever, just like Tebow.
>
> > > > >> You're right (there's something I've never said to Ray Ray), Herschel
> > > > >> too was one of the greatest football players ever.
>
> > > > > No one who was an excellent player for only 3-4 years qualifies as a
> > > > > "great football player."  You've got to be stellar for a lot longer
> > > > > than that to be great.  Feel free to call college players who were
> > > > > really good "great college football players."  But to be a truly great
> > > > > football player, that requires playing at a high level far longer than
> > > > > only 4 years.
>
> > > > > Peace,
> > > > > Neil X.
>
> > > > Billy Simms was a great football player who played in 60 NFL games.
>
> > > No, he wasn't.
>
> > > Peace,
> > > Neil X.
>
> > Agree with X.
>
> It's amazing to me that Deadheads are so quick to throw away flashes
> of greatness in favor of longevity at a lesser level. Sure, the GD had
> longevity, but I don't think anybody loves the GD because they were
> really good for a long time. They love the GD because they had moments
> and stretches of pure genius. If the GD had ceased to exist after the
> 2/27/69-3/2/69 run, they still go down as one of the greatest bands
> ever, IMO.
>
> Billy Sims, Gale Sayers, Bill Walton, Earl Campbell, Bo Jackson. They
> may not have had the longevity, but anyone who wants to pretend that
> they didn't attain greatness is operating under a drastically
> different set of parameters than I am.


Hey, I didn't say Gale Sayers wasn't great, but as someone who grew up
as a Bears fan, absolutely LOATHING the Cowboys, your assertion that
Sayers was greater than Emmit Smith is beyond ridiculous to me. How
far are you going to take this logic? In your perspective 3 years is
enough to be a "great" player. Why is 3 the magic number. How about
1 year? Or one great game? Was Harvey Haddix a "great" baseball
player because his 13 inning perfect game was beyond sublime? Was
Akili Smith a "great" football player because he threw 32 TD passes
in one season at Oregon? Longevity is clearly a criteria in
greatness. You've got to be able to perform at a stellar level over
many years to be truly great.

Peace,
Neil X.

Andrew

12/2/2011 4:38:00 AM

0

On Dec 1, 7:59 pm, "Neil X." <nei...@yahoo.com> wrote:
> On Dec 1, 3:21 pm, Andrew <amuraw...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Dec 1, 3:25 am, marcman <marcmanstud...@gmail.com> wrote:
>
> > > On Nov 30, 10:23 pm, "Neil X." <nei...@yahoo.com> wrote:
>
> > > > On Nov 30, 11:27 am, Paul L <p...@kbtrans.net> wrote:
>
> > > > > On 11/29/2011 9:35 PM, Neil X. wrote:
>
> > > > > > On Nov 29, 8:06 pm, Andrew<amuraw...@gmail.com>  wrote:
> > > > > >> On Nov 29, 4:43 pm, "Ray O'Hara"<raymond-oh...@hotmail.com>  wrote:
>
> > > > > >>> Do you remember Hershal Walker, brilliant athlete.
> > > > > >>> one of the best college players ever, just like Tebow.
>
> > > > > >> You're right (there's something I've never said to Ray Ray), Herschel
> > > > > >> too was one of the greatest football players ever.
>
> > > > > > No one who was an excellent player for only 3-4 years qualifies as a
> > > > > > "great football player."  You've got to be stellar for a lot longer
> > > > > > than that to be great.  Feel free to call college players who were
> > > > > > really good "great college football players."  But to be a truly great
> > > > > > football player, that requires playing at a high level far longer than
> > > > > > only 4 years.
>
> > > > > > Peace,
> > > > > > Neil X.
>
> > > > > Billy Simms was a great football player who played in 60 NFL games.
>
> > > > No, he wasn't.
>
> > > > Peace,
> > > > Neil X.
>
> > > Agree with X.
>
> > It's amazing to me that Deadheads are so quick to throw away flashes
> > of greatness in favor of longevity at a lesser level. Sure, the GD had
> > longevity, but I don't think anybody loves the GD because they were
> > really good for a long time. They love the GD because they had moments
> > and stretches of pure genius. If the GD had ceased to exist after the
> > 2/27/69-3/2/69 run, they still go down as one of the greatest bands
> > ever, IMO.
>
> > Billy Sims, Gale Sayers, Bill Walton, Earl Campbell, Bo Jackson. They
> > may not have had the longevity, but anyone who wants to pretend that
> > they didn't attain greatness is operating under a drastically
> > different set of parameters than I am.
>
> Hey, I didn't say Gale Sayers wasn't great, but as someone who grew up
> as a Bears fan, absolutely LOATHING the Cowboys, your assertion that
> Sayers was greater than Emmit Smith is beyond ridiculous to me.  How
> far are you going to take this logic?  In your perspective 3 years is
> enough to be a "great" player.

Nah. 1 game is enough to be a "great" player.

> Why is 3 the magic number.

I dunno. I never mentioned 3.

> How about
> 1 year?

Sure.

> Or one great game?

Hell yeah, assuming that game is actually great and not just a result
of other factors combining to produce a good outcome.

> Was Harvey Haddix a "great" baseball
> player because his 13 inning perfect game was beyond sublime?

(1) Who the hell is Harvey Haddix and (2) I'm no more able to consider
greatness in baseball than I can determine greatness in darts or
bowling or pin-the-tail-on-the-donkey... Really, who cares?

> Was
> Akili Smith a "great" football player because he threw  32 TD passes
> in one season at Oregon?

Akili Smith wasn't even the greatest QB in the Pac-10 that year. Akili
Smith was a really good football player. Greatness to me requires a
mastery of whichever artform we're talking about. Just as importantly,
people deal with all sorts of limitations in attempting to master
their artform. For Doug Flutie, to take an extreme example, to become
a great QB (which he undoubtedly was, to my perspective at least)
required more than for Ryan Leaf to do so. In judging those two, I'd
give Flutie more of the benefit of the doubt and I'm not going to ding
him if physical limitations prevented him from having success at the
highest level of the games, much like I'm not going to ding Sayers or
Sims for the physical limitations that they came up against.

> Longevity is clearly a criteria in
> greatness.

For you.

> You've got to be able to perform at a stellar level over
> many years to be truly great.

I appreciate your take. To me, I'm perfectly fine with calling a
shooting star great. If you can approach perfection at your given
artform for a year, a game, an hour, a minute - fuck yeah. And I'd
much rather see a player, an artist, a musician - whatever - get as
close to perfection as possible for a brief amount of time, than see
somebody do something at a slightly lower quality for longer times.

Ken Kesey wrote two great books and more or less disappeared from the
landscape of literature (overstating the case, but certainly all other
books besides SAGN and OFOTCN were minor works at best). In no way am
I going to ding him (or F. Scott Fitzgerald or Harper Lee) for their
limited output.

I'll take quality over quantity in anything.

mr.rapidan

12/2/2011 4:42:00 AM

0

On Thursday, December 1, 2011 10:59:05 PM UTC-5, Neil X. wrote:
> Hey, I didn't say Gale Sayers wasn't great, but as someone who grew up
> as a Bears fan, absolutely LOATHING the Cowboys, your assertion that
> Sayers was greater than Emmit Smith is beyond ridiculous to me. How
> far are you going to take this logic? In your perspective 3 years is
> enough to be a "great" player. Why is 3 the magic number. How about
> 1 year? Or one great game? Was Harvey Haddix a "great" baseball
> player because his 13 inning perfect game was beyond sublime? Was
> Akili Smith a "great" football player because he threw 32 TD passes
> in one season at Oregon? Longevity is clearly a criteria in
> greatness. You've got to be able to perform at a stellar level over
> many years to be truly great.

There is strong truth in your words, Neil - but, to keep the debate going, there are a few players who are recognizably transcendent - and Gale Sayers was one of them.

I would absolutely consider Sayers > Emmitt.

I suppose you and Andrew need to establish the rmgd football greatness rating system. Emmitt had the good fortune to find himself on a great team with many weapons and a great line. He had awesome longevity and was a great running back. But he was no Gale Sayers.

marcman

12/2/2011 5:19:00 AM

0

On Dec 1, 11:41 pm, "dr.narcolepsy" <jmi...@gmail.com> wrote:

> But he was no Gale Sayers.

Eh. A name is just a label.