Using CTE in TSQL

Last week I found CTE (Common Table Expressions) when I posted a question to StackOverflow. CTE is an ANSI SQL-99 standard and it was added to Microsoft SQL Server 2005.

For me, it was new. I applied answers to my TSQL codes and then I really like it. It is so simple and readable. With its recursive power, I resolved my issue in a couple lines of codes. Today I further googled out more information about this and here are some links with good view of CTE:

• 4 Guys' article on CTE: what and how to use CTE.
• Using temporary tables, table variables and CTE, great view on the differences among them.
• An article on CTE by Kevin S. Goff, an MVP, with several examples.

Another example I tried was to replace T-SQL cursors by using both CTE and a table variable:

DECLARE @row INT;
DECLARE @rowMax INT;
DECLARE @myTable (
  id   INT IDENTITY(1,1) -- key with auto creament
  name VARCHAR(100),
  dt   DATETIME );
-- Using CTE to select data into @myTable
WITH CTE_Temp(name, dt) AS
(
  -- SELECT to hold a temp set of data
  SELECT tagName as name, dt FROM myTagTable 
    WHERE version = 0;
)
INSERT INTO @myTable (name, dt) -- Insert to @myTable
  SELECT name, dt FROM CTE_Temp; 
SELECT @row = MIN(id), @rowMax = MAX(id)
  FROM @myTable;
WHILE (@row <= @rowMax)
BEGIN
  -- do someting about data in @myTable
  SET @row = @row + 1; -- move to next row
END

Of course, this can be done with SELECT INTO FROM statement. This is just an example to use CTE to hold a section of data from a base table.

The power of CTE is its unique feature: recursiion. Within the WITH block, you can define two basic queries: base or anchor query and recursive query. Here is an example of Split like function to split a string by delimiter into a table as return:

CREATE FUNCTION [dbo].[udf_Split]
(
  -- Add the parameters for the function here
  @p_StringList NVARCHAR(4000),
  @p_Delimiter NVARCHAR(512) = ';'
)
-- Return table definition
RETURNS @Results TABLE (
  position int NOT NULL,
  item NVARCHAR(MAX)
)AS
BEGIN
  -- Use WITH statement with recursive power
  WITH Pieces(pn, start, stop) AS
  (
    -- start from 1, 1, stop(=CHARINDX())
    SELECT 1, 1, CHARINDEX(@p_Delimiter, @p_StringList) 
    UNION ALL
    -- next to update pn, start and stop values
    -- by recursive call
    SELECT 
        pn + 1,
        stop + 1,
        CHARINDEX(@p_Delimiter, @p_StringList, stop + 1) 
      FROM Pieces
      WHERE stop IS NOT NULL AND stop > 0
  )
  -- Test the result
  -- SELECT * FROM Pieces;
  INSERT INTO @Results
    SELECT
      pn, -- as position
      SUBSTRING(@p_StringList, start, 
      CASE WHEN stop IS NOT NULL AND stop > 0 
        THEN stop-start 
      ELSE LEN(@p_StringList) END) 
      AS s -- as item by using SUBSTRING() function to get sub string from Pieces
      FROM Pieces;
 
  RETURN;
END