Stored Procedure Style - 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)

Stored Procedure Style

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-04-02 : 17:17:05

Question#1
Is this "good" or "GREAT" style for a stored proc w/transactions:

BEGIN TRANSACTION GetMyData
Update ...@topicID
IF @@ERROR <> 0
GOTO ErrorsHandler

COMMIT TRANSACTION GetMyData
RETURN 0
ErrorsHandler:
ROLLBACK TRANSACTION GetMyData
RETURN @@ERROR

Is that correct?

Question#2
When should I use SET NOCOUNT ON and when should I use 'RETURN', is it better performance to use RETURN?

gpl
Posting Yak Master

195 Posts

Posted - 2004-04-02 : 17:47:18

Its close enough to the style I use, however - @@ERROR is reset after every TSQL statement, which means you cant return the value, just testing it will set it back to 0!

I normally Set @MyError = @@ERROR
then test that.

I normally set NoCount On when I have sprocs that dont return data sets, the count values from the various queries executed can upset the client program.

Im not too sure what you mean by your last question, better performance than what ?

Graham

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-02 : 17:50:29

I always SET NOCOUNT ON after the AS:

CREATE PROC SomeProc
(@SomeVar1 INT)
AS

SET NOCOUNT ON

...

If you want to know how many rows were affected by the query, use @@ROWCOUNT, not NOCOUNT OFF option.

Tara

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-04-02 : 18:17:25

at the end of stored procs, ive seen some people do a:
RETURN 0

is that cleaner/faster?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-02 : 18:18:38

Cleaner/faster than what? What are you comparing it to?

RETURN 0 is usually used to indicate success of a stored procedure. It doesn't have to though. You can use it for anything.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-02 : 19:52:34

It all depends what you are trying to accomplish though really. We've started using an error_handle table that will actually log to a table errors for some of our important procedures.

The procedures have this piece in the beginning:

--Setup error handling.
DECLARE
@util_error_setup_key INT,
@host_name VARCHAR(256),
@user_name VARCHAR(256),
@app_name VARCHAR(256),
@error_message VARCHAR(2500)

SELECT
@util_error_setup_key = (
SELECT util_error_setup_key
FROM util_error_setup WITH(NOLOCK)
WHERE
util_error_setup_database_name = (SELECT DB_NAME())
AND util_error_setup_object_name = (SELECT OBJECT_NAME(@@PROCID))
AND util_error_setup_object_type = (SELECT xtype FROM sysobjects WITH(NOLOCK) WHERE id = @@PROCID)
AND util_error_setup_is_on = 1),
@host_name = HOST_NAME(),
@user_name = USER_NAME(),
@app_name = APP_NAME(),
@error_message = (SELECT OBJECT_NAME(@@PROCID)) + '::'

Before each commit/rollback you have this:

--Detect errors.
IF @@ERROR <> 0
BEGIN
SELECT @error_message = @error_message + 'Error inserting into @los_loans table.'
GOTO ERROR_CHECK
END

The end of the procedure will return 0 if there have been no errors:

--Returns successfully if procedure finished without errors.
RETURN 0

After the RETURN 0, the error check logs errors to a table if the option is turned on. Either way, it returns -1 as the error and raises an informational error message.

--Processes errors that have occurred. If error_logging is on, logs error to table.
ERROR_CHECK:

RAISERROR(@error_message,16,1)
ROLLBACK

IF @util_error_setup_key <> 0
BEGIN
EXEC UTILErrorLogPut @util_error_setup_key, @host_name, @user_name, @app_name, @error_message
END

RETURN -1

--You really don't have a lot of overhead because you're only writing if the util_error_setup has that procedures error handling on and an error is encountered.

--It's all personal style though. This is one of two styles I use. Tara does something entirely different.

--The RETURN doesn't affect performance enough to warrant not using it. If your application isn't set up to use it though, it's kind of useless.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources