SQL Server Refresher

• '... = null' complies fine, but will never match
• In particular, this is a project in joins (... ON X=Y fails if both are NULL)

• TABLE vars have to have a seperate DECLARE statement and cannot be combined

SQL2008 added MERGE command (no need to write custom UPSERT)

• Create a table type on the server (CREATE TYPE x AS TABLE(...)). Use this in the store proc parameter definition.
• Create and populate C# DataTable on the client (column types will be C$types)
• Add the columns using C# type (not SQL). Populate with data
• When passing up the table parameter, set SQL parameter type to Structured
• On execute, .NET will send up a single batch command that inserts the data from table into a table var of the right type, then calls the stored procedure with it
• If calling a stored procedure, declare the parameter READONLY in the store proc
• See: http://web.archive.org/web/20101127071716/http://mssqltips.com/tip.asp?tip=2112

DROP TABLE test
CREATE TABLE test (id varchar(10) NULL, parent varchar(10) NULL)
INSERT INTO test VALUES (1, NULL)
INSERT INTO test VALUES (2, 1)
INSERT INTO test VALUES (3, 2);
INSERT INTO test VALUES (4, 3);
INSERT INTO test VALUES (10, NULL);
INSERT INTO test VALUES (100, 10);
GO

-- Find all paths
WITH myCte AS
(
SELECT id, 
       parent, 
       0 AS depth, 
       CAST(id AS varchar(500)) AS path
FROM test
WHERE parent is null
UNION ALL
SELECT test.id, 
       test.parent, 
       myCte.depth + 1 AS depth, 
       CAST( myCte.path + '/' + test.id AS varchar(500)) AS path
FROM myCte INNER JOIN test ON myCte.id = test.parent
)
SELECT * FROM myCte
GO

--Results

id   parent  depth  path

1    NULL    0      1
10   NULL    0      10
100  10      1      10/100
2    1       1      1/2
3    2       2      1/2/3
4    3       3      1/2/3/4

-- From leaf
WITH myCte AS 
(
	SELECT id, parent, CAST(id AS varchar(500)) AS path
		FROM test
		WHERE id = 4
	UNION ALL
		SELECT test.id, test.parent, 
                CAST( test.id + '/' +myCte.path AS varchar(500)) AS path
		FROM myCte INNER JOIN test on myCte.parent = test.id
)
SELECT * FROM myCte 
GO

--Results

id	parent	path

4	3	4
3	2	3/4
2	1	2/3/4
1	NULL	1/2/3/4