Typical Errors


You should keep the following issues in mind when you are writing your code and testing Transact-SQL programs:

  • Handling nul

  • Assignment of variable from the result set

  • No records affected

  • Wrong size or data type

  • Default length

  • Rollback of triggers

  • Warnings and lower-priority errors

  • Return codes vs. Raiserror

  • Nested comments

  • Deferred name resolution

  • Cursors

  • Overconfidence

Handling Null

Many errors are a result of the inadequate treatment of null values in Transact-SQL code. Developers often forget that local variables or table columns might contain null. If such a value becomes part of any expression, the result will also be null.

The proper way to test the value of an expression for null is to use the Is Null or Is Not Null clause. Microsoft SQL Server treats the use of = Null as another way to type Is Null (when Set Ansi_Nulls is set to Off), but <> Null is not the equivalent of Is Not Null. The result of such an expression is always simply null. It will never be true, and stored procedures will always skip statements after the If statement when you use the <> Null clause:

      If @intInventoryId IS NULL      . . .      If @intInventoryId = NULL      . . .      If @intInventoryId IS NOT NULL      . . .      If @intInventoryId <> NULL -- WRONG!!!      . . . 

Assignment of Variable from the Result Set

Earlier, I discussed assigning the value for a variable using the result set of the Select statement. This technique is fine when the result set returns only one record. However, if the result set returns more than one record, the variable is assigned using the value from the last record in the recordset—not perfect, but in some cases, you can live with it. It is sometimes difficult to predict which record will be returned last in the recordset. It depends on both the query and the index that SQL Server has used.

A more serious problem occurs when the recordset is empty. The values of the variables are changed in this case and the code is vulnerable to several mistakes. If you do not expect the result set to be empty, your stored procedure will fail. If you expect the values of the variables to be null, your stored procedure will function correctly only immediately after it is started (that is, in the first iteration of the process). In such a case, the local variables are not yet initialized and will contain null. Later, when variables are initialized, their values will remain unchanged. If you are testing the contents of the variables for null to find out if the record was selected, you will just process the previous record again.

No Records Affected

Developers sometimes assume that SQL Server will return errors if a Transact-SQL statement affects no records. Unfortunately, this error is semantic rather than syntactic and SQL Server will not detect it.

To identify this type of error, use the @ @rowCount function rather than the @ @error function:

      declare @intRowCount int      declare @intErrorCode int      update Inventory      Set StatusId = 3      where InventoryID = -11      select @intRowCount = @@rowCount,             @intErrorCode = @@error      if @intRowCount = 0      begin           select 'Record was not updated!'           return 50001      end 

Wrong Size or Data Type

I can recall one occasion when a colleague of mine spent two days going through a complicated data conversion process to find out why his process was consistently failing. In one of the nested stored procedures, I had declared the variable as tinyint instead of int. During the testing phase of the project, everything worked perfectly because the variable was never set to a value higher than 255. However, a couple of months later in production, the process started to fail as values climbed higher.

Similar problems can occur if you do not fully understand the differences between similar formats (for example, char and varchar or money and smallmoney), or if you fail to synchronize the sizes of data types (for instance, char, varchar, numeric, and other data types of variable size).

Default Length

A similar problem can occur when a developer does not supply the length of the variable data type and SQL Server assigns a default length.

For example, the default length of the varchar data type is 30. Most of the time, SQL Server reports an error if the length is omitted, but not always. In the Convert() function, for example, the user needs only to specify the data type:

      Convert(varchar, @intPropertyld) 

If the resulting string is short enough, you will not have any problems. I recall a colleague who employed this method for years without any problems, and then.

Unfortunately, other statements and functions behave as expected. If you declare a variable and assign it like so:

      Declare @test varchar      Set @test = '123456789012345678901234567890'      Select datalength(@test), @test 

SQL Server will allocate just one byte to the string and return the following:

 ----------- ---- 1           1 (1 row(s) affected) 

Rollback of Triggers

In different versions of SQL Server, triggers react differently in rollback transaction statements. When a trigger is rolled back in SQL Server 2005, 2000, or 7.0, the complete batch that initiated the trigger fails and the execution continues from the first statement of the next batch. Version 4.2 behaves in a similar manner. In version 6.0, processing continues in the trigger but the batch is canceled. In version 6.5, the processing continues in both the trigger and the batch. It was the responsibility of the developer to detect errors and cascade out of the process (in other words, go out of all nested procedures and triggers).

Warnings and Lower-priority Errors

Warnings do not stop the execution of a stored procedure. In fact, you cannot even detect them from within the SQL Server environment.

Low-level errors, which are detectable using the @@ error function, do not abort the execution either. Unfortunately, there are also errors that abort processing completely, so that the error handlers in stored procedures do not process the error.

Return Codes vs. Raiserror

I recommend that you decide whether you are going to use return values or Raiserror to notify middleware that something has gone wrong, and then stick to that decision. You do not want your middleware to be expecting Raiserror while you are trying to send it an error code or a status using your return code.

Some people even recommend that you use Raiserror exclusively, since middleware has to be able to handle errors raised directly from the SQL Server engine. In this way, you can be sure that an application developer will not forget to check the return code.

Nested Comments

Only single-line comments (––) can be nested. Nested multiline comments (/* */) may be treated differently by different client tools.

I recommend that you put one or two stars (**) at the beginning of each line that is commented out. In this manner, the problem will be obvious if the comments are nested and SQL Server starts to compile part of the code that you consider to be commented out:

      /**********************************************      **    select *      **    from #Properties      *********************************************** 

Deferred Name Resolution

It is possible (in Microsoft SQL Server 2005, 2000, and 7.0) to create database objects (such as stored procedures and triggers) that refer to other database objects that do not yet exist within the database. In previous versions, this would have been treated as a syntax error. This feature helps tremendously when you need to generate a database structure and objects using script. Unfortunately, this introduces a number of risks. If, as in the following example, you make a typo in the name of the table from which you want to retrieve records, SQL Server will not report a syntax error during compilation but will report a runtime error during execution.

      Create Procedure dbo.ap_DeferredNameResolution      As          set nocount on          select 'Start'          select * from NonExistingTables          select 'Will execution be stopped?'      return 

If you attempt to run this stored procedure, SQL Server will return the following:

      ----      Start      Server: Msg 208, Level 16, State 1,      Procedure ap_DeferredNameResolution, Line 5      Invalid object name 'NonExistingTable'. 

The execution will be stopped. Even an error handler written in Transact-SQL will not be able to proceed at this point.

Cursors

Be very cautious when you use cursors: Test the status after each fetch; place error handling after each command; do not forget to close and deallocate the cursor when you do not need it anymore. There are many rules and regulations for using cursors and some of them might seem trivial, but even the smallest mistake can halt the execution of your code.

Overconfidence

The overconfidence that comes with routine may be your worst enemy. If you perform the same or similar tasks over and over again, you can lose focus and skip basic steps. Do not put code into production before it is thoroughly tested; do not place bug fixes directly into production; use error handling even if the code seems straightforward and the chance for error slight.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net