Sql-server – What are the best practices in writing a sql stored procedure

sql-serverstored-procedurestsql

I found that SQL stored procedures are very interesting and useful. I have written stored procedures but i want to write well crafted, good performance tuned and concise SPs for any sort of requirement and also would love to learn about any tricks or good practices for stored procedures. How do i move from the beginner to the advanced stage in writing stored procedures?

Update: Found from comments that my question should be more specific.
Everyone has some tricks upon their sleeves and I was expecting such tricks and practices for SPs which they use in their code which differentiates them from others and more importantly spruce up the productivity in writing and working with stored procedures.

Best Solution

Here are my stored procedure error-handling guidelines.

  • Call each stored procedure using its fully qualified name to improve performance: that's the server name, database name, schema (owner) name, and procedure name.
  • In the script that creates each stored procedure, explicitly specify which roles are allowed to execute the procedure ,eg public or whatever.
  • Use sysmessage, sp_addmessage, and placeholders rather than hard-coded error messages.
  • When using sp_addmessage and sysmessages, always use error message number of 50001 or greater.
  • With RAISERROR, always supply a severity level <= 10 for warning messages.
  • With RAISERROR, always supply a severity level between 11 and 16 for error messages.
  • Remember that using RAISERROR doesn't always abort any batch in progress, even in trigger context.
  • Save @@error to a local variable before using it or interrogating it.
  • Save @@rowcount to a local variable before using it or interrogating it.
  • For a stored procedure, use the return value to indicate success/failure only, not any other/extra information.
  • Return value for a stored procedure should be set to 0 to indicate success, non-zero to indicate failure.
  • Set ANSI_WARNINGS ON - this detects null values in any aggregate assignment, and any assignment that exceeds the maximum length of a character or binary column.
  • Set NOCOUNT ON, for many reasons.
  • Think carefully about whether you want XACT_ABORT ON or OFF. Whichever way you go, be consistent.
  • Exit on the first error - this implements the KISS model.

  • When executing a stored procedure, always check both @@error and the return value. For example:

    EXEC @err = AnyStoredProc @value
SET  @save_error = @@error
-- NULLIF says that if @err is 0, this is the same as null
-- COALESCE returns the first non-null value in its arguments
SELECT @err = COALESCE( NULLIF(@err, 0), @save_error )
IF @err <> 0 BEGIN 
    -- Because stored proc may have started a tran it didn't commit
    ROLLBACK TRANSACTION 
    RETURN @err 
END
  • When executing a local stored procedure that results in an error, do a rollback because it's possible for the procedure to have started a transaction that it didn't commit or rollback.
  • Don't assume that just because you haven't started a transaction, there isn't any active transaction - the caller may have started one.
  • Ideally, avoid doing rollback on a transaction that was started by your caller - so check @@trancount.
  • But in a trigger, always do rollback, as you don't know whether the caller initiated an active transaction (because @@trancount is always >= 1).

  • Always store and check @@error after the following statements:

    INSERT, DELETE, UPDATE
SELECT INTO
Invocation of stored procedures
invocation of dynamic SQL
COMMIT TRANSACTION
DECLARE and OPEN CURSOR
FETCH from cursor
WRITETEXT and UPDATETEXT
  • If DECLARE CURSOR fails on a process-global cursor (the default), issue a statement to deallocate the cursor.
  • Be careful with an error in a UDF. When an error occurs in a UDF, execution of the function is aborted immediately and so is the query that invoked the UDF - but @@error is 0! You may want to run with SET XACT_ABORT ON in these circumstances.
  • If you want to use dynamic SQL, try to have only a single SELECT in each batch because @@error only holds the status of the last command executed. The most likely errors from a batch of dynamic SQL are syntax errors, and these aren't taken care of by SET XACT_ABORT ON.
Related Question