Stored procedures using old-style error handling and savepoints may not work as intended when they are used together with TRY … CATCH blocks. I will provide some examples. This post continues the series on defensive database programming.   Avoid calling old-style stored procedures from TRY blocks.   Stored procedures ...

In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider the following table, sample data, and stored procedure:  CREATE TABLE Data.Messages(Subject VARCHAR(30), Body VARCHAR(100)); GO INSERT INTO Data.Messages(Subject, Body) SELECT 'Next release delayed', 'Still fixing bugs' ...

If you do not qualify columns in your query, which means that you do not specify from which tables your columns come, you may have problems if the database schema changes. For example, consider the following sample tables and a select query: CREATE TABLE Data.Shipments(Barcode VARCHAR(30), SomeOtherData VARCHAR(100))GOINSERT INTO ...

The behavior of NOT IN clause may be confusing and as such it needs some explanations. Consider the following query: SELECT LastName, FirstName FROM Person.Contact WHERE LastName NOT IN('Hedlund', 'Holloway', NULL)   Although there are more than a thousand distinct last names in AdventureWorks.Person.Contact, the query returns ...