Development : Intermediate SQL

Unstringing a comma delimited column

mark_wills — Wed, 27 May 2009 18:07:00 GMT

Ever had a column which had a pile of information delimited by commas that you want to unpack ?

Well, it is not that hard so long as the information is pretty short, and tightly controlled...

-- step 1 create a sample table used as a datasource...

if object_id('tempdb..#tmp_colors','U') is not null drop table #tmp_colors

create table #tmp_colors (Date datetime, Name varchar(60), Choices varchar(100))
insert #tmp_colors
select '01 Mar 2009','John S','Red,Blue,Yellow' union all
select '02 Mar 2009','Jane D','Green, Orange, Pink, Purple' union all
select '01 Mar 2009','Mark W','Yellow, Blue, Black, Dark Blue' union all
select '02 Mar 2009','Brandon G','Dark Blue, Navy, Yellow, Gray, Purple' union all
select '01 Mar 2009','Tim C','Blue, Cyan, Yellow, Gray' union all
select '02 Mar 2009','Bob J','Blue'

-- now lets run a query on that and return the results.

SELECT name, date, ltrim(SUBSTRING(choices, n, CHARINDEX(',', choices + ',',n) - n)) AS colours
FROM #tmp_colors
CROSS JOIN (SELECT number n FROM master..spt_values WHERE type = 'P') AS Numbers
WHERE SUBSTRING(',' + choices, n, 1) = ','
AND n < LEN(choices) + 1

Two things I love about Management Studio 2008

TimChapman — Sat, 04 Apr 2009 01:34:00 GMT

I've been using the SQL Server product for quite some time now, and I almost always use the IDE programs that ship with the database engine. Enterprise Manager and Query Analyzer certainly had their quicks, but were overall nice tools. Management Studio 2005 was a great improvement over these tools because it integrated Enterprise Manager and Query Analyzer into one tool, and it added some neat bells and whistles. Now that Management Studio 2008 is out, there are two really neat new features I'd like you to take a look at if you haven't already.

First, is the ability to expand and collapse code blocks. This is a subtle tool, but so useful when you really just want to focus on a single set of code. Its not a huge feature for the product, but one that I think makes all of the difference in the world. In the following screenshot, I define a table and then use a BEGIN...END statement to insert some rows into the table...simple stuff.

Here's the code that I use in the above screenshot.

IF OBJECT_ID('RecordLoad') IS NOT NULL
DROP TABLE RecordLoad
GO
CREATE TABLE RecordLoad
(
    ID INT IDENTITY(1,1),
    ValueColumn INT
)
GO

BEGIN
    ;WITH CounterCTE
    AS
    (
        SELECT 1 AS ValRow
        UNION ALL
        SELECT ValRow + 1
        FROM CounterCTE
        WHERE ValRow < 100
    )
        INSERT INTO RecordLoad(ValueColumn)
        SELECT ValRow 
        FROM CounterCTE
END

Notice the expanded brackets to the left of the code blocks. If I am done writing that block, or I just want to come back to it later, I can click on the box and collapse the code block so that it takes up only one line in the IDE. I can even highlight this line and run the entire block of code contained in it. This is very handy for when you want to run large snippets of data.

The second thing I really like about Management Studio 2008 is its built in IntelliSense. Sure, there are other tools out there from third parties that provide IntelliSense, but I don't think they're as good as what comes packaged with Management Studio 2008. In the following example, I modify my INSERT statement to show the available columns to use in the INSERT list. It provides a list of the fields along with the data type of the highlighted column. This is very handy information, and can make you a lot more productive.

Both of these features are based are technically part of IntelliSense, which can be enabled by right-clicking in the Query window and selecting "IntelliSense Enabled".

While I certainly don't think these are the best features that came with SQL Server 2008, or even the best enhancements to Management Studio 2008, I love them. I think they made code easier to write, and debug. Check them out if you haven't already.

Using TRY...CATCH IN SQL Server 2005/2008

TimChapman — Fri, 03 Apr 2009 19:16:00 GMT

New to SQL Server 2005 (and present in SQL Server 2008) is the ability to use TRY..CATCH constructs inside of your TSQL code to catch error statements. If you've done any type of programming in other programming languages, you know that this is a very handy tool to have. Whats even better in my opinion is the ability to write a generic error handling procedure you can use inside of your CATCH blocks to capture and record errors when they occur.

So, lets take a look at how we can take advantage of these new error handling features. In the following script I create a table which will be used by my stored procedure which will contain my TRY...CATCH blocks.

IF OBJECT_ID('RecordLoad') IS NOT NULL
DROP TABLE RecordLoad
GO
CREATE TABLE RecordLoad
(
    ID INT IDENTITY(1,1),
    ValueColumn INT
)
GO

Here I define a stored procedure that uses a CTE to insert 100 records into the table I just created. This is done inside of the TRY block, which will immediately revert any execution to the CATCH block once an error is incurred for which it is able to handle. After the insert of the 100 records, I cause a divide by zero error. This error is caught by my CATCH block, which then displays information to me regarding the error details. To get at this error related information, I use 6 new error handling functions.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        SELECT Error = 1/0  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        --USE NEW ERROR HANDLING FUNCTIONS TO GET ERROR DATA
        SELECT 
            ErrorLine = ERROR_LINE(), 
            ErrorMessage = ERROR_MESSAGE(), 
            ErrorNumber = ERROR_NUMBER(),
            ErrorProcedure = ERROR_PROCEDURE(),
            ErrorSeverity = ERROR_SEVERITY(),
            ErrorState = ERROR_STATE()
    END CATCH
END

Here's a screenshot of what my output from the above procedure execution looks like. As you can see, this is some very handy information to have.

The following procedure is almost the same as above, except that I am using the RAISERROR command to generate a custom error message, which is also caught by the CATCH block.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        RAISERROR('This is a custom error.', 16,1,1)  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        --USE NEW ERROR HANDLING FUNCTIONS TO GET ERROR DATA
        SELECT 
            ErrorLine = ERROR_LINE(), 
            ErrorMessage = ERROR_MESSAGE(), 
            ErrorNumber = ERROR_NUMBER(),
            ErrorProcedure = ERROR_PROCEDURE(),
            ErrorSeverity = ERROR_SEVERITY(),
            ErrorState = ERROR_STATE()
    END CATCH
END

And here is the output from that procedure call.

Finding out about these errors are great when they occur, but having to duplicate the code to display to the screen or insert to a table can be a cumbersome and potentially error prone task. Whats great about using the TRY...CATCH blocks is that I can define a generic stored procedure to save these messages to a table for later review. The following code snippets I have borrowed from an article I had previous written for TechRepublic. I define a table to store the errors when they occur, and a procedure that I can call in all of my CATCH statements to log the error information. The great thing is that since the procedure executes in the context of the CATCH block, there is no need to pass in any parameters, the functions take care of the details for me.

CREATE TABLE ProcedureErrors
(      
    ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,      
    ErrorDate DATETIME DEFAULT(GETDATE()),      
    ErrorLine INT,      
    ErrorMessage NVARCHAR(4000),      
    ErrorNumber INT,      
    ErrorProcedure NVARCHAR(126),      
    ErrorSeverity INT,      
    ErrorState INT,       
    DatabaseName NVARCHAR(255)
)

CREATE PROCEDURE [usp_LogProcedureError]
AS
BEGIN
    IF ERROR_NUMBER() <> 0 AND @@TRANCOUNT > 0
        ROLLBACK TRAN

    INSERT INTO ProcedureErrors
    (
        ErrorLine, ErrorMessage, ErrorNumber, 
        ErrorProcedure, ErrorSeverity, ErrorState, DatabaseName
    )
    VALUES
    (
        ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER(),
        ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE(), DB_NAME()
    )

    PRINT 'An Error has occurred in ' + ERROR_PROCEDURE() + '.'
    PRINT 'Error Description: ' + ERROR_MESSAGE()

END

And here is the altered procedure script with the new call to my usp_LogProcedureError procedure.

IF OBJECT_ID('usp_ErrorExample') IS NOT NULL
DROP PROCEDURE usp_ErrorExample
GO
CREATE PROCEDURE usp_ErrorExample
AS
BEGIN
    BEGIN TRY
        
        ;WITH CounterCTE
        AS
        (
            SELECT 1 AS ValRow
            UNION ALL
            SELECT ValRow + 1
            FROM CounterCTE
            WHERE ValRow < 100
        )
            INSERT INTO RecordLoad(ValueColumn)
            SELECT ValRow 
            FROM CounterCTE
            
        RAISERROR('This is a custom error.', 16,1,1)  --THIS WILL ERROR, AND INVOKE THE CATCH BLOCK
    END TRY
    BEGIN CATCH
        EXECUTE usp_LogProcedureError
    END CATCH
END

As you can see, it makes writing error handling routines much much easier. So, try to start infusing this error handling logic (or SOME type of error handling logic) into your SQL Server 2005 or 2008 code if you haven't already. It has saved me many, many hours of troubleshooting time.

Concatenating Results through SELECT statements

TimChapman — Fri, 20 Mar 2009 20:53:00 GMT

In this SQL tip I am going to show you a neat little trick which involves using a simple SELECT statement to concatenate row values into a single variable value.

First, I'll create a temp table named #Results for which I'll store FirstName and LastName data.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    FirstName varchar(25), 
    LastName varchar(25)
)

GO

This script adds some names to the temp table I just created.

INSERT INTO #Results(FirstName, LastName)
SELECT 'Tim', 'Chapman' 
UNION ALL
SELECT 'Brandon', 'Galderisi' 
UNION ALL
SELECT 'Aaron','Akin' 
UNION ALL
SELECT 'Mark', 'Wills' 
UNION ALL
SELECT 'Angel','Eyes' 
UNION ALL
SELECT 'Daniel','Wilson'

GO

The following script does the meat of the work. I declare a variable to hold the name values and use the variable in my SELECT statement. The SELECT statement essentially takes every value from the #Results table and appends it to the @FullNames variable.

DECLARE @FullNames VARCHAR(200)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' '
FROM #Results

PRINT @FullNames

The result of printing the value in the variable is below.

Chapman, Tim Galderisi, Brandon Akin, Aaron Wills, Mark Eyes, Angel Wilson, Daniel

If I want to add a line feed to the varaible for formatting purposes, it is easy enough to do by adding the CHAR(10) character to the variable string. This method will insert a new line feed between each value.

DECLARE @FullNames VARCHAR(100)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' ' + CHAR(10)
FROM #Results

PRINT @FullNames

Printing the contents of the @FullNames variable leaves me with the following output.

Chapman, Tim 
Galderisi, Brandon 
Akin, Aaron 
Wills, Mark 
Eyes, Angel 
Wilson, Daniel

Nested Common Table Expressions

Aaron Akin — Thu, 19 Mar 2009 02:03:00 GMT

If you've looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can't define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner. Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders.

USE AdventureWorks

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders

OrderID     OrderDate
----------- ----------
1           2001-05-17             
2           2001-05-17             
3           2001-05-17             
4           2001-05-17             
5           2001-05-31             
...

What isn't very clear is the use of multiple CTE definitions in one statement. Here is an example of multiple CTEs used in one statement. Each CTE in the statement has its own definition, and the final SELECT statement is able to query both CTEs. This will return a list of dates that orders were made along with the number of products ordered and the total amount of those products for each date.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt
---------- ------------- -------------
2001-05-17 563           9491.518
2001-05-31 2765          94404.303
2002-01-14 718           39874.7685
2002-01-15 11931         259365.2145
2002-02-08 2768          50781.36
...

If you take this concept of having multiple CTE definitions in one statement, you can then apply it to a statement with nested CTEs. I'm using the following statement to return the same result set as in the previous example, but this time I want to know the number of orders for each date that had more than 100 products ordered.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate, IsLargeOrder) AS
(
  SELECT PurchaseOrderID, OrderDate
       , (
         SELECT CASE
                  WHEN SUM(OrderDetailQty) >= 100 THEN 1
                  ELSE 0
                END
           FROM CTE_OrdersDetail d
           WHERE h.PurchaseOrderID = d.OrderID
         )
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty
   , SUM(od.OrderDetailAmt) AS TotalOrderAmt, SUM(o.IsLargeOrder) AS NumLargeOrders
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt         NumLargeOrders
---------- ------------- --------------------- --------------
2001-05-17 563           9491.518              1
2001-05-31 2765          94404.303             5
2002-01-14 718           39874.7685            1
2002-01-15 11931         259365.2145           26
2002-02-08 2768          50781.36              5
...

As you can see from this example, the CTE_OrdersDetail expression is being referenced within the definition of the CTE_Orders expression, giving you the functionality of nested CTEs. In order for a statement like this to work properly, you must declare each CTE definition before it is used in another CTE. This means that since CTE_OrdersDetail is being reference within CTE_Orders, I must declare CTE_OrdersDetail first. Otherwise, if the two definitions were switched, SQL would not be able to find the CTE_OrdersDetail object when it goes to verify the CTE_Orders expression.

Aaron
http://aaronakinsql.wordpress.com