Common Mistakes

Before we look at error handling, let's examine some common causes of errors and misconceptions.

Batches and GO

Important

The GO keyword is NOT a T-SQL command. It is actually a command terminator that is interpreted by Query Analyzer (and isql.exe) to indicate the end of a command batch.

For example, consider the following T-SQL batch:

     SELECT * FROM Employees e     SELECT * FROM Customers c     GO     SELECT * FROM Orders o     GO 

The SQL statements between the GO keyword are separate batches. They are sent to SQL Server together, as a group of commands, and the results of these commands are sent back from SQL Server together, as a group of results.

Using the previous batch example, we can highlight the non-command nature of the GO statement. For this, perform the following steps:

1. Connect to SQL Server using Query Analyzer.

2. Click Tools | Options to bring up the Options window, and then click the Connections tab.

3. Change the Batch separator option to something other than GO, such as APRESS.

The screen will look like:

Now, when executing the previous batch query, if we leave the GO statements within the SQL code we will receive an error. If you change this to the value APRESS (that we set above) the batches should execute without any problem:

     SELECT * FROM Employees e     SELECT * FROM Customers c     APRESS     SELECT * FROM Orders o     APRESS 

The Batch separator is never sent to the SQL Server; rather, Query Analyzer uses it to end a batch and then strips it out of the SQL code before sending it to the SQL Server, for processing. Therefore, we cannot include Batch separator within stored procedures. If we do, Query Analyzer will interpret it as being the end of the procedure, and will not include any code after the GO keyword within the stored procedure.

Dynamic SQL in Procedures

In SQL Server, we can use dynamic SQL, which is essentially a collection of SQL statements that we construct at runtime rather than at design time, for example:

     USE Northwind     GO     CREATE PROCEDURE SelectAllFromTable       @TableName VARCHAR(255)     AS       SET NOCOUNT ON       DECLARE @SQLString VARCHAR(8000)       SELECT @SQLString='SELECT * FROM ' + @TableName       EXEC(@SQLString) 

We will run the following statement to execute this procedure, and therefore the dynamic SQL command contained within it:

     EXEC SelectAllFromTable 'Orders' 

This approach appears nice and easy, as it allows us to create generic stored procedures that can query any table, at run time. However, this approach is poor for the following reasons:

• Security
  Dynamic SQL breaks the stored procedure's security model; generally, stored procedures are used to prevent direct access to the base tables and this is enforced with permissions. However, if you choose to use dynamic SQL within your stored procedures, the users must have access to the base tables used within the dynamic statement. This makes stored procedures pointless for security purposes. The security model of stored procedures is discussed in detail in Chapter 8.

• Debugging
  It's difficult to debug code that doesn't exist during design time. Commands built dynamically at run time are a common source of bugs and identifying the cause of such bugs can be complex and time-consuming.

• Optimization
  The execution plan of the dynamic command is not compiled during the first execution of the stored procedure; instead it is generated at run time, and will have to be compiled for every unique occurrence of the dynamic statement.

Contrary to other program languages, such as object -oriented languages like C# and Java, using stored procedures to create generic reusable objects is not considered a good approach. They exist to help you get the best performance and security from your database, and using them in the manner described above doesn't help you achieve this. Instead, a better approach for creating a generic stored procedure is to create many specific (although similar) stored procedures. While this ultimately results in increased code, the end result is more efficient, secure, and easier to debug.

Security

As we mentioned in the previous section, people commonly use stored procedures to protect base tables from direct user interaction. The assumption is that only valid information will be passed to the stored procedure. However, this assumption is flawed. We will have to use some form of validation within the stored procedure to realize this assumption. For example, consider the following stored procedures:

     CREATE PROCEDURE AddNewSale       @CustomerID INT,       @ItemID VARCHAR(255),       @Quantity INT,       @RATE Money     AS     SET NOCOUNT ON       INSERT dbo.Sales(Customer, Item, Quantity, Rate)       VALUES(@Customer, @ItemID, @Quantity, @Rate) 

This procedure in its current format offers no protection to the information contained within the base table, as it will allow any user to modify the underlying data. If this approach is to be useful as a security measure we could include some form of validation within the procedure to check the integrity of the change. For example let us assume our business logic allows anyone in an organisation to enter a sale. However, only sales that have been entered by sales managers get entered directly into the Sales table. Any sales entered by other people get placed into a PendingSales table where they are held until a sales manager approves (at which point it is entered as a sale):

     CREATE PROCEDURE AddNewSale       @CustomerID INT,       @ItemID VARCHAR(255),       @Quantity INT,       @RATE Money     AS     SET NOCOUNT ON       IF IS_MEMBER('CheckIfSalesManager') =1       BEGIN       INSERT dbo.Sales(IDCol,VarCharCol)       VALUES(@IDValue, @VarCharValue)     END     ELSE     BEGIN       INSERT dbo.PendingSales(IDCol,VarCharCol)       VALUES(@IDValue, @VarCharValue)     END 

Now, this procedure uses the IS_MEMBER system function to check if the current user issuing the INSERT request is a member of the Sales Manager database role. If they are, then it allows them to INSERT the new row directly into the sales table, otherwise the INSERT is placed within the PendingSales table.

Important

Data validation rules, such as length, NULL values, lists of possible values, and range checking should be applied directly to a column within the table by using CHECK constraints. If you intend to use stored procedures as a security measure in your application, rules checking the validity of the values passed as parameters should be contained within the stored procedures.