Microsoft SQL Server 2005 Stored Procedures guidelines

Read this article in your language IT | EN | DE | ES

Here I was looking at Microsoft SQL Server 2005 and it occurred to me that maybe there might be a blog to write in all of this.

First and foremost, naming of stored procedures and other objects in SQL should be taken seriously, too often do I see sp_garbagenameofsorts.

1. The name does not need to be prefixed and should you feel the urge to do so do not use "sp_", I use "su_" Stored procedure User.
   From Microsoft (http://support.microsoft.com/kb/263889)
   
   Note If your stored procedure name begins with the "sp_" prefix and is not in the master database, you see SP:CacheMiss before the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules (the "preferred" location is in the master database). The names of user-created stored procedures should not begin with "sp_."
   
2. Have a descriptive name, I generally use the Object Name followed by a narrowing type, if there is one, and finally the action.
   eg: UsersByRegionSelect, UserInsert, UserDelete, UserSave, UserByIDSelect, UserByIDUpdate... You get the idea.
   This allows me to "group" the store procedures by object and thus finding them easier, notice the use of camel case.
   
3. Make use of the TRY / CATCH close, mind you this should be used everywhere. If you do not already, make it a New Year's resolution.
   Example:

   		CREATE PROCEDURE [user].[TableSave] 
   		@propertyName nvarchar(255),    @propertyName varchar(128),     ... ,    @ID int OUT ASBEGIN    SET NOCOUNT ON        BEGIN TRY        IF EXISTS(SELECT ID FROM [user].[Table] WHERE ID = @ID)            BEGIN                UPDATE [user].[Table]                 SET [fieldName] = @propertyName, [fieldName] = @propertyName, ...                WHERE [ID]=@iD                IF @@ROWCOUNT = 0                BEGIN                    RAISERROR('Concurrent update error. Updated aborted.', 16, 2)                END            END        ELSE            BEGIN                INSERT INTO [user].[Table] ([fieldName], [fieldName], ...)                VALUES (@propertyName, @propertyName, ...)                SET @ID = SCOPE_IDENTITY()            END    END TRY    BEGIN CATCH        EXEC RethrowError;    END CATCH        SET NOCOUNT OFFEND
   		

   
   
   For your reading pleasure, this is the code for "RethrowError" as generated by the Microsoft Service Factory
   
   

   		ALTER PROCEDURE RethrowError AS    /* Return if there is no error information to retrieve. */    IF ERROR_NUMBER() IS NULL        RETURN;    DECLARE        @ErrorMessage    NVARCHAR(4000),        @ErrorNumber     INT,        @ErrorSeverity   INT,        @ErrorState      INT,        @ErrorLine       INT,        @ErrorProcedure  NVARCHAR(200);     /* Assign variables to error-handling functions that       capture information for RAISERROR. */    SELECT        @ErrorNumber = ERROR_NUMBER(),        @ErrorSeverity = ERROR_SEVERITY(),        @ErrorState = ERROR_STATE(),        @ErrorLine = ERROR_LINE(),        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');     /* Building the message string that will contain original       error information. */    SELECT @ErrorMessage =         N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +          'Message: '+ ERROR_MESSAGE();     /* Raise an error: msg_str parameter of RAISERROR will contain       the original error information. */    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,        @ErrorNumber,    /* parameter: original error number. */        @ErrorSeverity,  /* parameter: original error severity. */        @ErrorState,     /* parameter: original error state. */        @ErrorProcedure, /* parameter: original error procedure name. */        @ErrorLine       /* parameter: original error line number. */        );
   		

4. Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
   
5. Keep the name of your table fields across the application, so if you use "Name" as a table field, in your stored procedure your field input / output will be called @Name. It's easier to read and find errors later.
   
6. Avoid any reserved words, and if you can't make use of square brackets as in the [user].[ObjectSave] example above.
   

As you may have noticed the [user].[ObjectSave]  has both INSERT and UPDATE commands in it, I find it more practical this way as my DataLayer / Business Object only has a save. It's my preference and is certainly not a standard, though it is a recommendation.