pls critique my stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

pls critique my stored proc

Author

Topic

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-04-29 : 17:52:35

My stored proc:

...
DECLARE @errorsave int

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO dbo.Enrollment (InternetNumber, Company, ContactName, Address, City, StateProv, ZipPostal, Email, Phone, Fax, ContactMe)
VALUES (@icn, @company, @contactname, @address, @city, @stateprov, @zippostal, @email, @phone, @fax, @contactme)

IF @@ERROR <> 0
BEGIN
RETURN @errorsave
ROLLBACK TRAN
END

COMMIT TRAN

SELECT txtUserID, txtPassword
FROM dbo.Account
WHERE txtInternetCompNum = @icn

SET NOCOUNT OFF
GO

Pls critique my stored proc. Are the set nocount on/off statements in their proper places? Can I actually execute this from a .aspx page using a .executenonquery or .executereader?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-29 : 18:01:50

Yes the SET NOCOUNTs are in the proper place, although as a coding style I put SET NOCOUNT ON right after AS:

CREATE PROC Test
AS

SET NOCOUNT ON

You just need to turn the option on before you do any queries. You don't have to turn it off, but I do it at the end for clean-up as a good practice.

Everything looks good in your stored proc except that you aren't putting @@ERROR into @errorsave. So right after the INSERT:

SELECT @errorsave = @@ERROR

IF @errorsave <> 0
...

I can't answer your .aspx question.

Tara

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-04-29 : 18:28:51

Thanks Tara.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-29 : 19:21:04

In ASP.net You can execute the Stored Proc using Execute Reader. The Execute Non-Query will not work becuase you are getting a result set back. If you were not getting a result set back, or only getting output params back, then Execute Non-query is the way to go.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 02:28:10

The transaction doesn't do anything as every sql statement is atomic. Using an explicit transaction here will just hold the locks for longer.
You aren't setting @errorsave.
I tend to always save @@error and @@rowcount after statements just because it's easier to copy.
Doing a select after the insert is a bit odd but maybe you always need it in your app.

DECLARE @error int, @rowcount int

SET NOCOUNT ON

INSERT INTO dbo.Enrollment (InternetNumber, Company, ContactName, Address, City, StateProv, ZipPostal, Email, Phone, Fax, ContactMe)
VALUES (@icn, @company, @contactname, @address, @city, @stateprov, @zippostal, @email, @phone, @fax, @contactme)

select @error = @@error, @rowcount = @@rowcount
IF @error <> 0
BEGIN
RETURN @error
END

SELECT txtUserID, txtPassword
FROM dbo.Account
WHERE txtInternetCompNum = @icn

SET NOCOUNT OFF
GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-04-30 : 02:33:57

And another thing, for your info:

The ROLLBACK TRAN will never be executed, as the procedure will exit after a RETURN statement. So, you'll end up with a transaction sitting there in limbo until the DBMS kills it.

Tim

PGG123
Yak Posting Veteran

55 Posts

Posted - 2004-04-30 : 11:26:08

Thanks for the input folks.

So when do I use the transaction? Only in multiple insert/update statements?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-30 : 13:26:21

Yes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 12:24:32

Yes reverse the order of the ROLLBACK TRAN and RETURN in the IF. You should ROLLBACK then RETURN. And for your example, you don't need transactions since you've only got one statement. But you can use this as a template for multiple DML statements.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources