Using a Recursive CTE to Build Paths

Here’s the scenario. You have a hierarchical data set like categories, folders, etc. and you need to output the entire path for each.

Let’s say we take the category example and we have a table that looks like:

CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[ParentCategoryId] [int] NULL,
    -- normal PK script removed to keep this short
) 

To get the full path for each category, the SQL would look like:

WITH CTE_CategoriesWithPaths AS
( 
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId, 
        c.Name AS [Path] 
    FROM Category c 
    WHERE c.ParentCategoryId IS NULL 
    UNION ALL
    SELECT 
        c.Id, 
        c.Name, 
        c.ParentCategoryId,
        cte.[Path] + '/' + c.Name AS [Path] 
    FROM 
        Category c 
        INNER JOIN CTE_CategoriesWithPaths cte 
            ON c.ParentCategoryId = cte.Id
    WHERE c.ParentCategoryId IS NOT NULL 
)
SELECT * 
from CTE_CategoriesWithPaths

SQL If Exists Then Drop

This is one topic that I continually find myself refering to my notes and bookmarks on and I’ve finally decided to add this piece of very good reference information to my own blog.

Tables

Option 1:

IF OBJECT_ID('enterTableNameHere', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Option 2:

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterTableNameHere]')
         AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
BEGIN
    DROP TABLE [dbo].[enterTableNameHere]
END
GO

Views

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'enterSchemaNameHere' AND TABLE_NAME = 'enterViewNameHere'
)
BEGIN
    DROP VIEW [dbo].[enterViewNameHere]
END
GO

Stored Procedures

IF EXISTS
(
    SELECT * FROM dbo.sysobjects
    WHERE id = object_id(N'[dbo].[enterStoredProcedureNameHere]')
        AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
    DROP PROCEDURE [dbo].[enterStoredProcedureNameHere]
END
GO

User-Defined Functions

IF EXISTS
(
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES
    WHERE	ROUTINE_NAME = 'enterFunctionNameHere'
    AND ROUTINE_SCHEMA = 'dbo'
    AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
    DROP FUNCTION [dbo].[enterFunctionNameHere]
END
GO

2012 Baton Rouge SQL Saturday & Tech Day

The information for this year’s Baton Rouge SQL Saturday & Tech Day came online this past week. It’s scheduled for August 4, 2012.

Free SQL Server, SharePoint and .NET training!  Chance to win great prizes!  What more could an IT Professional ask for?  If this sounds good to you, then don’t miss your opportunity to attend SQL Saturday #150 and TECH Day, the largest FREE training event in Louisiana dedicated exclusively to SQL Server, .NET, Development, SharePoint and Business Intelligence.   This is a COMMUNITY driven event!

For more information visit the official website at: http://sqlsaturday.com/150/eventhome.aspx