So the other day I was working on a T-SQL query that I was using to take data from generic data types and insert it into a strongly typed table.  One of the columns was moving from an nvarchar(255) to an decimal data type.  As some of the rows had character data in it, I wrote something like this:

CASE ISNUMERIC(Col1)
    WHEN 1 THEN CAST(Col1 as decimal(9,4))
    ELSE NULL
END as Col1

However when I ran the query, I received this error:

Error converting data type nvarchar to numeric.

It took me awhile to figure this one out as you can see I”m using ISNUMERIC to check before I convert it to a number, seems pretty straight forward, so what’s the bid deal?

The big deal is that I needed to read the manual…according to the ISNUMERIC function in BOL:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.

It turned out I had a row that had a value of a single decimal point and no numbers which caused the problem.  So remember, understand what the functions you use actually do, not what you think they do and test!

The Transact-SQL debugger in SQL Server Management Studio enables you to find errors in Transact-SQL scripts, stored procedures, triggers, and functions by observing their run-time behavior. You can start the debugger when you are using the Database Engine Query Editor window.

By using the Transact-SQL debugger, you can do the following:

• Step through the Transact-SQL statements in the editor line by line, or set breakpoints to stop at specific lines.
• Step into or over Transact-SQL stored procedures, functions, or triggers that are run by the code in the editor window.
• Watch the values that are assigned to variables, and observe system objects such as the call stack and threads. 

Check out a screencast about the new T-SQL Debugger in SQL Server 2008 via MSN Video below, or if you prefer Youtube or download.

Common data scenarios require developers to write and maintain logic to determine whether to insert, update or delete information (sometimes called upsert) such as:

   1: IF FOUND 

   2:     THEN UPDATE 

   3: ELSE 

   4:     INSERT 

SQL Server 2008 provides the new MERGE statement within the SQL language to enable developers to provide this functionality with a single statement. This allows for code reduction and easier maintainability.

Check out a screencast about the new MERGE Statement in SQL Server 2008 via MSN Soapbox below or youtube or download here.

Read more about this in the SQL Server 2008 BOL ‘Inserting, Updating, and Deleting Data with MERGE’.