SQL Server 2008 : T-SQL Stored Procedure Coding Guidelines

Transact-SQL (T-SQL) stored procedures should be treated just like reusable application code. You should follow these suggested guidelines to ensure that your stored procedures are solid and robust:

• Check all parameters for valid values and return an error message if a problem exists.
  
  
• Be sure that the parameter data types match the column data types they are compared against to avoid data type mismatches and poor query optimization.
  
  
• Check the @@error system function after each SQL statement, especially insert, update, and delete, to verify that the statements executed successfully. Return a status code other than 0 if a failure occurs.
  
  
• Be sure to comment your code so that when you or others have to maintain it, the code is self-documenting.
  
  
• Consider using a source code management system, such as Microsoft Visual Studio SourceSafe, CVS, or Subversion, to maintain versions of your stored procedure source code.
  
  

You should avoid using select * in your stored procedure queries. If someone were to add columns to or remove columns from a table, the stored procedure would generate a different result set, which could cause errors with the applications.

Whenever using INSERT statements in stored procedures, you should always provide the column list associated with the values being inserted. This allows the procedure to continue to work if the table is ever rebuilt with a different column order or additional columns are added to the table. Listing 1 demonstrates what happens if the column list is not provided and a column is added to the referenced table.

Listing 1. Lack of Column List in INSERT Statement Causing Procedure to Fail

use bigpubs2008 go IF EXISTS ( SELECT * FROM sys.procedures WHERE schema_id = schema_id('dbo') AND name = N'insert_publishers') DROP PROCEDURE dbo.insert_publishers GO create proc insert_publishers @pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2), @country varchar(30) as INSERT INTO bigpubs2008.dbo.publishers VALUES(@pub_id, @pub_name, @city, @state, @country) if @@error = 0 print 'New Publisher added' go exec insert_publishers '9950', 'Sams Publishing', 'Indianapolis', 'IN', 'USA' go New Publisher added alter table publishers add street varchar(80) null go exec insert_publishers '9951', 'Pearson Education', 'Indianapolis', 'IN', 'USA' go Msg 213, Level 16, State 1, Procedure insert_publishers, Line 7 Insert Error: Column name or number of supplied values does not match table definition.

A stored procedure cannot directly create schemas, views, triggers, defaults, rules, aggregates, functions, or stored procedures. You can, however, execute dynamic SQL that creates the object:

CREATE PROC create_other_proc AS
  EXEC ('CREATE PROC get_au_lname AS
        SELECT au_lname from authors
        RETURN')

Tip

If you are using dynamic SQL to create objects in stored procedures, be sure to qualify each object with the name of the object schema if users other than the stored procedure owner will be executing the stored procedure.

You can create tables in stored procedures. Generally, only temporary tables are created in stored procedures. Temporary tables created in stored procedures are dropped automatically when the procedure terminates. Global temporary tables, however, exist until the connection that created them terminates.

If you don’t qualify object names within a stored procedure, they default to the schema of the stored procedure. It is recommended that objects in stored procedures be qualified with the appropriate schema name to avoid confusion.

You cannot drop a table and re-create another table with the same name within the procedure unless you use dynamic SQL to execute a string that creates the table.

A stored procedure cannot issue the USE statement to change the database context in which it is running; the database context for execution is limited to a single database. If you need to reference an object in another database, you should qualify the object name with the database name in your procedure code.

Calling Stored Procedures from Transactions

Stored procedures can be called from within a transaction, and they can also initiate transactions. SQL Server notes the transaction nesting level, which is available from the @@trancount function, before calling a stored procedure. If the value of @@trancount when the procedure returns is different from the value of @@trancount when it was executed, SQL Server displays error message 266: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. This message indicates that transaction nesting is out of balance. Because a stored procedure does not abort the batch on a rollback transaction statement, a rollback transaction statement inside the procedure could result in a loss of data integrity if subsequent statements are executed and committed.

A rollback transaction statement rolls back all statements to the outermost transaction, including any work performed inside nested stored procedures that have not been fully committed. A commit tran within the stored procedure decreases the value of @@trancount by only one. Because the transaction is not fully committed until @@trancount returns to zero, the work can be completely rolled back at any time prior to that. Essentially, the nested transaction inside the stored procedure is largely ignored. The modifications within the procedure are committed or rolled back based on the final action taken for the outermost transaction.

To avoid transaction nesting issues, you need to develop a consistent error-handling strategy for failed transactions or other errors that occur in transactions within your stored procedures and implement that strategy consistently across all procedures and applications. Within stored procedures that might be nested, you need to check whether the procedure is already being called from within a transaction before issuing another begin tran statement. If a transaction is already active, you can issue a save tran statement so that the procedure can roll back only the work that it has performed and allow the calling procedure that initiated the transaction to determine whether to continue or abort the overall transaction.

To maintain transaction integrity when calling procedures that involve transactions, follow these guidelines:

• Make no net change to @@trancount within your stored procedures.
  
  
• Issue a begin tran only if no transaction is already active.
  
  
• Set a savepoint if a transaction is already active so that a partial rollback can be performed within the stored procedure.
  
  
• Implement appropriate error handling and return an error status code if something goes wrong and a rollback occurs.
  
  
• Issue a commit tran only if the stored procedure issued the begin tran statement.
  
  

Listing 2 provides a template for a stored procedure that can ensure transactional integrity whether it is run as part of an ongoing transaction or independently.

Listing 2. Template Code for a Stored Procedure That Can Run as Part of a Transaction or Run as Its Own Transaction

/* proc to demonstrate no net change to @@trancount ** but rolls back changes within the proc ** VERY IMPORTANT: return an error code ** to tell the calling procedure rollback occurred */ create proc ptran1 as declare @trncnt int select @trncnt = @@trancount — save @@trancount value if @trncnt = 0 — transaction has not begun begin tran ptran1 — begin tran increments nest level to 1 else — already in a transaction save tran ptran1 — save tran doesn't increment nest level /* do some processing */ if (@@error != 0) — check for error condition begin rollback tran ptran1 — rollback to savepoint, or begin tran return 25 — return error code indicating rollback end /* more processing if required */ if @trncnt = 0 — this proc issued begin tran commit tran ptran1 — commit tran, decrement @@trancount to 0 — commit not required with save tran return 0 /* successful return */

Listing 3 provides a template for the calling batch that might execute the stored procedure shown in Listing 2. The main problem you need to solve is handling return codes properly and responding with the correct transaction handling.

Listing 3. Template Code for a Calling Batch or Stored Procedure That Might Execute a Stored Procedure Built with the Template in Listing 2

/* Retrieve status code to determine if proc was successful */ declare @status_val int, @trncnt int select @trncnt = @@trancount — save @@trancount value if @trncnt = 0 — transaction has not begun begin tran t1 — begin tran increments nest level to 1 else — otherwise, already in a transaction save tran t1 — save tran doesn't increment nest level /* do some processing if required */ if (@@error != 0) — or other error condition begin rollback tran t1 — rollback to savepoint,or begin tran return — and exit batch/procedure end execute @status_val = ptran1 —exec procedure, begin nesting if @status_val = 25 — if proc performed rollback begin — determine whether to rollback or continue rollback tran t1 return end /* more processing if required */ if @trncnt = 0 — this proc/batch issued begin tran commit tran t1 — commit tran, decrement @@trancount to 0 return — commit not required with save tran

Handling Errors in Stored Procedures

SQL Server 2008 provides the TRY...CATCH construct, which you can use within your T-SQL stored procedures to provide a more graceful mechanism for exception handling than was available in previous versions of SQL Server by checking @@ERROR (and often the use of GOTO statements) after each SQL statement.

A TRY...CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a T-SQL statement inside a TRY block, control is immediately passed to a CATCH block, where the error is processed. T-SQL statements in the TRY block that follow the statement that generated the error are not executed.

If an error occurs and processing is passed to the CATCH block, after the statements in the CATCH block are executed, control is then transferred to the first T-SQL statement that follows the END CATCH statement. If there are no errors inside the TRY block, control is passed to the statement immediately after the associated END CATCH statement, essentially skipping over the statements in the CATCH block.

A TRY is initiated with the BEGIN TRY statement and ended with the END TRY statement and can consist of one or more T-SQL statements between the BEGIN TRY and END TRY statements. The TRY block must be followed immediately by a CATCH block. A CATCH block is indicated with the BEGIN CATCHEND CATCH statement and can consist of one or more SQL statements. In SQL Server, each TRY block can be associated with only one CATCH block. statement and ended with the

The syntax of the TRY...CATCH construct is as follows:

BEGIN TRY
    one_or_more_sql_statements
END TRY
BEGIN CATCH
    one_or_more_sql_statements
END CATCH

When in a CATCH block, you can use the following error functions to capture information about the error that invoked the CATCH block:

• ERROR_NUMBER()— Returns the error number

• ERROR_MESSAGE()— Returns the complete text of the error message

• ERROR_SEVERITY()— Returns the error severity

• ERROR_STATE()— Returns the error state number

• ERROR_LINE()— Returns the line number inside the procedure that caused the error

• ERROR_PROCEDURE()— Returns the name of the stored procedure or trigger where the error occurred

Unlike @@error, which is reset by each statement that is executed, the error information retrieved by the error functions remains constant anywhere within the scope of the CATCH block of a TRY...CATCH construct. Error functions can also be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed within a CATCH block. This allows you to modularize the error handling into a single procedure so you do not have to repeat the error-handling code in every CATCH block. Listing 4 shows an example of an error-handling procedure that you can use in your CATCH blocks.

Listing 4. An Example of a Standard Error-Handling Procedure

create proc dbo.error_handler as begin Declare @errnum int, @severity int, @errstate int, @proc nvarchar(126), @line int, @message nvarchar(4000) -- capture the error information that caused the CATCH block to be invoked SELECT @errnum = ERROR_NUMBER(), @severity = ERROR_SEVERITY(), @errstate = ERROR_STATE(), @proc = ERROR_PROCEDURE(), @line = ERROR_LINE(), @message = ERROR_MESSAGE() -- raise an error message with information on the error RAISERROR ('Failed to add new publisher for the following reason: Error: %d, Severity: %d, State: %d, in proc %s at line %d, Message: "%s"', 16, 1, @errnum, @severity, @errstate, @proc, @line, @message) Return end

Listing 5 provides an example of the TRY...CATCH construct in a stored procedure, modifying the insert_publishers procedure created in Listing 1. Note that this CATCH block uses the dbo.error_handler procedure defined in Listing 4.

Listing 5. Using a TRY...CATCH Construct for Error Handling in a Stored Procedure

Code View: Scroll / Show All use bigpubs2008 go alter proc insert_publishers @pub_id char(4), @pub_name varchar(40), @city varchar(20), @state char(2), @country varchar(30) as BEGIN TRY INSERT INTO bigpubs2008.dbo.publishers (pub_id, pub_name, city, state, country) VALUES(@pub_id, @pub_name, @city, @state, @country) -- if no error occurs, we should see this print statement print 'New Publisher added' END TRY BEGIN CATCH -- invoke the error_handler procedure exec error_handler -- return a non-zero status code RETURN -101 END CATCH -- if successful execution, return 0 RETURN 0 go exec insert_publishers '9951', 'Pearson Education', 'Indianapolis', 'IN', 'USA' exec insert_publishers '9950', 'Sams Publishing', 'Indianapolis', 'IN', 'USA' go New Publisher added Msg 50000, Level 16, State 1, Procedure insert_publishers, Line 18 Failed to add new publisher for the following reason: Error: 2627, Severity: 14, State: 1, in proc insert_publishers at line 8, Message: "Violation of PRIMARY KEY constraint 'UPKCL_pubind'. Cannot insert duplicate key in object 'dbo.publishers'."

If you want to capture and handle any errors that may occur within a CATCH block, you can incorporate another TRY...CATCH block within the CATCH block itself.

Also note that some errors with severity 20 or higher that cause SQL Server to close the user connection cannot be handled by the TRY...CATCH construct. However, severity level 20 or higher errors that do not result in the connection being closed are captured and handled by the CATCH block. Any errors with a severity level of 10 or less are considered warnings or informational messages and not really errors and thus are not handled by the TRY...CATCH construct. Also, any compile errors (such as syntax errors) or object name resolution errors that happen during deferred name resolution also do not invoke a CATCH block. These errors are returned to the application or batch that called the error-generating routine.

Using Source Code Control with Stored Procedures

When you can, it’s generally a good idea to use source code control for your stored procedure scripts. Stored procedures are as much a part of an application as the application code itself and should be treated as such. When using source code control, you can link versions of your procedures and other object creation scripts with specific versions of your applications. Using source code control systems also provides a great way to keep track of the changes to your stored procedures and other object creation scripts, enabling you to go back to a previous version if the modifications lead to problems with the applications or data.

SQL Server Management Studio (SSMS) provides a feature similar to Visual Studio that lets you organize your SQL scripts into solutions and projects. A project is a collection of one or more script files stored in the Windows file system, usually in a folder with the same name as the project. A solution is a collection of one or more projects.

In addition to providing a way to manage and organize your scripts, SSMS can also integrate with source code control software if the source code control system provides a compatible plug-in. If you are using Visual Studio, it’s likely that you are also using Visual SourceSafe. Visual SourceSafe provides a one-to-one mapping between SSMS projects and Visual SourceSafe projects. After you create an SSMS solution, you can check the entire SSMS solution into Visual SourceSafe and then check out individual script files or projects. You can also specify that a solution be added to source code control when you create a new solution. In SSMS, you select File, New and then select New Project. In the New Project dialog, you can specify the name for the project and solution, and you can also specify whether to add the solution to source code control, as shown in Figure 1.

Figure 1. Creating a new project/solution and adding it to source control.

When you add a solution to Visual SourceSafe, it prompts you for the login ID and password to use to access Visual SourceSafe. After you provide that information, Visual SourceSafe then prompts you for the Visual SourceSafe project to add the SMSS project to, or it allows you to create a new project in Visual SourceSafe.

Within a project, you can specify the database connection(s) for the project and add SQL script files to the Queries folder. After creating a new script file, you can add it into the source code control system by right-clicking the script file in the Solutions Explorer and selecting Check In (see Figure 2).

Figure 2. Checking in a new script file.

After you check in a script file, you can right-click the file and perform source code control tasks such as checking out the script for editing, getting the current version, comparing versions, and viewing the check-in history. If you check out the script for editing, you can then open it in a new query window, where you can make changes to the script and then execute it in the database. When you are satisfied with the changes, you can check the new version back into the source code control system.