Sunday, March 22, 2009

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:


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

0 comments: