Verifying Trees

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ruedifuchs Valued Member Points: 53 More actions · Answer 1

Very interesting code indeed - thank you very much for this.

Some comments:

code to drop global temporary table ##Tree has no effect (end of TRY block and in CATCH block) - probably because it has been created in the same compilation scope.
I consider this as a feature, since I cannot use @DDL to create a table using @DML to save the entries of ##tree to it.
SELECT statements in @DML do not return any result, so I need to rely on ##Tree existing after execution of [dbo].[sp_VerifyTree]
Usage of trim() is only supported starting in SQL Server 2017

Glen Cooper SSCommitted Points: 1917 More actions · Answer 2

The attached file contains a print-ready copy of this article in PDF format along with the listing of the stored procedure.

You must be logged in to view attached files.

Glen Cooper SSCommitted Points: 1917 More actions · Answer 3

Hello Ruedifuchs,

Thank you for your comments.

Unless I've misunderstood comment 2) it is possible to SELECT statements in @DML that return results, eg.

DECLARE @ERROR_NUMBER INT

DECLARE @ERROR_MESSAGE VARCHAR(MAX)

DECLARE @Row_Count INT

EXEC @ERROR_NUMBER =

dbo.sp_VerifyTree

@Schema = 'dbo', -- schema of table containing tree

@Graph = 'Graph', -- table containing tree

@NodeId = 'NodeId', -- column name for NodeId in table containing tree

@Node = 'Node', -- column name for Node in table containing tree

@ParentId = 'ParentId', -- column name for ParentId in table containing tree

@DDL = '', -- DDL code to execute first (optional)

@DML = '

SELECT * FROM ##Tree

', -- DML code to execute last (optional)

@ERROR_NUMBER = @ERROR_NUMBER OUTPUT,

@ERROR_MESSAGE = @ERROR_MESSAGE OUTPUT,

SELECT @ERROR_NUMBER AS ERROR_NUMBER, @ERROR_MESSAGE AS ERROR_MESSAGE, @Row_Count AS ROW_COUNT