Cascading delete and finding table reference level

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Cascading delete and finding table reference level

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-16 : 13:25:46

This function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!

CREATE FUNCTION dbo.fnCascadingDelete
(
	@Schema NVARCHAR(128) = NULL,
	@Table NVARCHAR(128) = NULL
)
RETURNS	@Return TABLE
	(
		RowID INT PRIMARY KEY CLUSTERED,
		IsSelfJoin TINYINT NOT NULL,
		HasPk TINYINT NOT NULL,
		[SQL] NVARCHAR(4000) NOT NULL
	)
AS
BEGIN
	DECLARE	@Constraints TABLE
		(
			RowID INT NOT NULL,
			Indent SMALLINT NOT NULL,
			[Catalog] NVARCHAR(128) NOT NULL,
			[Schema] NVARCHAR(128) NOT NULL,
			[Table] NVARCHAR(128) NOT NULL,
			[Column] NVARCHAR(128),
			pkCatalog NVARCHAR(128),
			pkSchema NVARCHAR(128),
			pkTable NVARCHAR(128),
			pkColumn NVARCHAR(128),
			pkType NVARCHAR(128),
			pkSize INT,
			IsSelfJoin TINYINT NOT NULL,
			HasPk TINYINT NOT NULL
		)

	INSERT	@Constraints
		(
			RowID,
			Indent,
			[Catalog],
			[Schema],
			[Table],
			[Column],
			pkCatalog,
			pkSchema,
			pkTable,
			pkColumn,
			pkType,
			pkSize,
			IsSelfJoin,
			HasPk
		)
	SELECT	RowID,
		Indent,
		[Catalog],
		[Schema],
		[Table],
		[Column],
		pkCatalog,
		pkSchema,
		pkTable,
		pkColumn,
		pkType,
		pkSize,
		SelfJoin,
		CASE
			WHEN [Column] IS NULL THEN 0
			ELSE 1
		END
	FROM	dbo.fnTableTree(@Schema, @Table)

	IF @@ROWCOUNT = 0
		RETURN

	DECLARE	@SQL TABLE
		(
			ID INT IDENTITY(1, 1),
			RowID INT PRIMARY KEY CLUSTERED,
			IsSelfJoin TINYINT NOT NULL,
			HasPk TINYINT NOT NULL,
			[SQL] NVARCHAR(4000) NOT NULL
		)

	DECLARE	@Indent SMALLINT,
		@RowID INT,
		@ID INT,
		@TSQL NVARCHAR(4000),
		@RowSQL NVARCHAR(4000),
		@EndSQL NVARCHAR(4000),
		@pkColumn NVARCHAR(128),
		@IsSelfJoin TINYINT,
		@HasPk TINYINT

	DECLARE	@Unwind TABLE
		(
			RowID INT NOT NULL,
			StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
			[SQL] NVARCHAR(4000)
		)

	WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
		BEGIN
			SELECT TOP 1	@RowID = c.RowID,
					@ID = c.RowID,
					@Indent = c.Indent,
					@TSQL = N'',
					@EndSQL = N'',
					@IsSelfJoin = c.IsSelfjoin,
					@HasPk = c.HasPk
			FROM		@Constraints AS c
			LEFT JOIN	@SQL AS s ON s.RowID = c.RowID
			WHERE		s.RowID IS NULL
			ORDER BY	c.Indent DESC,
					c.RowID DESC

			WHILE @ID > 0
				BEGIN
					IF @Indent = 0
						SELECT	@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
							@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
							@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
						FROM	@Constraints AS c
						WHERE	c.RowID = @ID
					ELSE
						SELECT	@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
							@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
							@pkColumn = QUOTENAME(c.pkColumn),
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
						FROM	@Constraints AS c
						WHERE	c.RowID = @ID

					SELECT TOP 1	@ID = c.RowID,
							@Indent = c.Indent,
							@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
					FROM		@Constraints AS c
					WHERE		c.RowID < @ID
							AND c.Indent < @Indent
					ORDER BY	c.Indent DESC,
							c.RowID DESC

					IF @@ROWCOUNT = 0
						SET	@ID = 0

					SET	@TSQL = @RowSQL + @TSQL
				END

			INSERT	@SQL
				(
					RowID,
					IsSelfJoin,
					HasPk,
					[SQL]
				)
			VALUES	(
					@RowID,
					@IsSelfJoin,
					@HasPk,
					@TSQL + @EndSQL
				)

			IF @IsSelfJoin = 1
				BEGIN
					DECLARE	@Yak NVARCHAR(160),
						@Catalog NVARCHAR(128),
						@Column NVARCHAR(128)

					SELECT	@Yak  = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
						@Catalog = [Catalog],
						@Schema = [Schema],
						@Table = [Table],
						@Column = [Column],
						@Catalog = [Catalog],
						@Table = [Table],
						@pkColumn = pkColumn
					FROM	@Constraints
					WHERE	RowID = @RowID

SET	@RowSQL = 'DECLARE	@Lvl INT
SET	@Lvl = 0
DECLARE	@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
	BEGIN
		SET	@Lvl = @Lvl + 1

		INSERT		@Stage  (Lvl, RowKey)
		SELECT		@Lvl,
				t.' + QUOTENAME(@pkColumn) + '
		FROM		' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
		INNER JOIN	@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
					AND s.Lvl = @Lvl - 1
		LEFT JOIN	@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
		WHERE		cr.RowKey IS NULL
	END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'

				INSERT	@Unwind
					(
						RowID,
						[SQL]
					)
				VALUES	(
						@RowID,
						@RowSQL
					)
				END
		END

	INSERT		@Return
			(
				RowID,
				IsSelfJoin,
				HasPk,
				[SQL]
			)
	SELECT		s.ID,
			s.IsSelfJoin,
			s.HasPk,
			CASE
				WHEN u.RowID IS NULL THEN s.[SQL]
				ELSE u.[SQL]
			END
	FROM		@SQL AS s
	LEFT JOIN	@Unwind AS u ON u.RowID = s.RowID
	ORDER BY	s.ID,
			u.StepID

	RETURN
END

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-16 : 13:26:44

And the "master" function here (which can be run individually to find table reference levels)

CREATE FUNCTION dbo.fnTableTree
(
	@Schema NVARCHAR(128) = NULL,
	@Table NVARCHAR(128) = NULL
)
RETURNS	@Tree TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		Indent SMALLINT NOT NULL,
		[Catalog] NVARCHAR(128) NOT NULL,
		[Schema] NVARCHAR(128) NOT NULL,
		[Table] NVARCHAR(128) NOT NULL,
		[Column] NVARCHAR(128),
		[Type] NVARCHAR(128),
		[Size] INT,
		pkCatalog NVARCHAR(128),
		pkSchema NVARCHAR(128),
		pkTable NVARCHAR(128),
		pkColumn NVARCHAR(128),
		pkType NVARCHAR(128),
		pkSize INT,
		SelfJoin TINYINT NOT NULL
	)
AS
BEGIN
	DECLARE	@Temp TABLE
		(
			TempID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
			TempKey VARBINARY(8000),
			Indent SMALLINT,
			ConstraintID INT
		)

	DECLARE	@Constraints TABLE
		(
			ConstraintID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
			SelfJoin TINYINT NOT NULL DEFAULT 0,
			pkCatalog NVARCHAR(128) NOT NULL DEFAULT N'',
			pkSchema NVARCHAR(128) NOT NULL DEFAULT N'',
			pkTable NVARCHAR(128) NOT NULL DEFAULT N'',
			pkColumn NVARCHAR(128),
			pkType NVARCHAR(128),
			pkSize INT,
			fkCatalog NVARCHAR(128) NOT NULL,
			fkSchema NVARCHAR(128) NOT NULL,
			fkTable NVARCHAR(128) NOT NULL,
			fkColumn NVARCHAR(128),
			fkType NVARCHAR(128),
			fkSize INT
		)

	INSERT		@Constraints
			(
				fkCatalog,
				fkSchema,
				fkTable,
				fkColumn
			)
	SELECT		t.TABLE_CATALOG,
			t.TABLE_SCHEMA,
			t.TABLE_NAME,
			ccu.COLUMN_NAME
	FROM		INFORMATION_SCHEMA.TABLES AS t
	LEFT JOIN	INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON tc.TABLE_CATALOG = t.TABLE_CATALOG
				AND tc.TABLE_SCHEMA = t.TABLE_SCHEMA
				AND tc.TABLE_NAME = t.TABLE_NAME
				AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
	LEFT JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
				AND ccu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
				AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	WHERE		t.TABLE_TYPE = 'BASE TABLE'
			AND OBJECTPROPERTY(OBJECT_ID(t.TABLE_CATALOG + N'.' + t.TABLE_SCHEMA + N'.' + t.TABLE_NAME), 'IsMSShipped') = 0
			AND (@Schema IS NULL OR @Schema = t.TABLE_SCHEMA)
			AND (@Table IS NULL OR @Table = t.TABLE_NAME)
	ORDER BY	t.TABLE_CATALOG,
			t.TABLE_SCHEMA,
			t.TABLE_NAME

	INSERT		@Temp
			(
				Indent,
				ConstraintID
			)
	SELECT		0,
			ConstraintID
	FROM		@Constraints
	ORDER BY	ConstraintID

	INSERT		@Constraints
			(
				SelfJoin,
				pkCatalog,
				pkSchema,
				pkTable,
				pkColumn,
				pkType,
				pkSize,
				fkCatalog,
				fkSchema,
				fkTable,
				fkColumn,
				fkType,
				fkSize
			)
	SELECT		CASE
				WHEN pk.TABLE_CATALOG = fk.TABLE_CATALOG AND pk.TABLE_SCHEMA = fk.TABLE_SCHEMA AND pk.TABLE_NAME = fk.TABLE_NAME THEN 1
				ELSE 0
			END,
			pk.TABLE_CATALOG,
			pk.TABLE_SCHEMA,
			pk.TABLE_NAME,
			pk.COLUMN_NAME,
			pt.DATA_TYPE,
			pt.CHARACTER_MAXIMUM_LENGTH,
			fk.TABLE_CATALOG,
			fk.TABLE_SCHEMA,
			fk.TABLE_NAME,
			fk.COLUMN_NAME,
			ft.DATA_TYPE,
			ft.CHARACTER_MAXIMUM_LENGTH
	FROM		INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
	INNER JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS pk ON pk.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG
				AND pk.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
				AND pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
	INNER JOIN	INFORMATION_SCHEMA.COLUMNS AS pt ON pt.TABLE_CATALOG = pk.TABLE_CATALOG
				AND pt.TABLE_SCHEMA = pk.TABLE_SCHEMA
				AND pt.TABLE_NAME = pk.TABLE_NAME
				AND pt.COLUMN_NAME = pk.COLUMN_NAME
	INNER JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS fk ON fk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
				AND fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
				AND fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
	INNER JOIN	INFORMATION_SCHEMA.COLUMNS AS ft ON ft.TABLE_CATALOG = fk.TABLE_CATALOG
				AND ft.TABLE_SCHEMA = fk.TABLE_SCHEMA
				AND ft.TABLE_NAME = fk.TABLE_NAME
				AND ft.COLUMN_NAME = fk.COLUMN_NAME
	ORDER BY	pk.TABLE_CATALOG,
			pk.TABLE_SCHEMA,
			pk.TABLE_NAME,
			pk.COLUMN_NAME,
			fk.TABLE_CATALOG,
			fk.TABLE_SCHEMA,
			fk.TABLE_NAME,
			fk.COLUMN_NAME

	UPDATE	@Temp
	SET	TempKey = CAST(TempID AS VARBINARY(4))

	DECLARE	@Indent SMALLINT

	SET	@Indent = 0

	WHILE @Indent < 2000 AND @@ROWCOUNT > 0 OR @Indent = 0
		BEGIN
			SET	@Indent = @Indent + 1

			INSERT		@Temp
					(
						TempKey,
						Indent,
						ConstraintID
					)
			SELECT		t.TempKey,
					@Indent,
					fk.ConstraintID
			FROM		@Temp AS t
			INNER JOIN	@Constraints AS pk ON pk.ConstraintID = t.ConstraintID
			INNER JOIN	@Constraints AS fk ON fk.pkCatalog = pk.fkCatalog
						AND fk.pkSchema = pk.fkSchema
						AND fk.pkTable = pk.fkTable
			WHERE		t.Indent = @Indent - 1
					AND NOT	(
							pk.pkCatalog = pk.fkCatalog
							AND pk.pkSchema = pk.fkSchema
							AND pk.pkTable = pk.fkTable
						)
			ORDER BY	fk.ConstraintID

			UPDATE	@Temp
			SET	TempKey = TempKey + CAST(TempID AS VARBINARY(4))
			WHERE	Indent = @Indent
		END

	INSERT		@Tree
			(
				Indent,
				SelfJoin,
				[Catalog],
				[Schema],
				[Table],
				[Column],
				[Type],
				[Size],
				pkCatalog,
				pkSchema,
				pkTable,
				pkColumn,
				pkType,
				pkSize
			)
	SELECT		t.Indent,
			c.SelfJoin,
			c.fkCatalog,
			c.fkSchema,
			c.fkTable,
			c.fkColumn,
			c.fkType,
			c.fkSize,
			NULLIF(c.pkCatalog, N''),
			NULLIF(c.pkSchema, N''),
			NULLIF(c.pkTable, N''),
			c.pkColumn,
			c.pkType,
			c.pkSize
	FROM		@Temp AS t
	INNER JOIN	@Constraints AS c ON c.ConstraintID = t.ConstraintID
	ORDER BY	t.TempKey,
			t.Indent

	RETURN
END

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 10:41:15

Peter,

This is really great function! But I had one suggestion. This function generates Delete statements equal to Number of FKs + 1. In some cases, this is not really required.

Consider for example the below sample script:

create table Test1
(
	a int primary key,
	b int constraint fk_test1 foreign key references Test1(a)
)

create table Test2
(
	a int primary key,
	b int constraint fk_test2 foreign key references Test1(a)
)

create table Test3
(
	a int primary key,
	b int constraint fk1_test3 foreign key references Test1(a),
	c int constraint fk2_test3 foreign key references Test2(a),
)

Here as can be seen Test3 has no incoming foreign key references hence it is an ideal candidate to start the deletion with.

Your function generates DELETE statements in correct order:

DELETE t4 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test2] AS t3 ON t3.[b] = t1.[a] INNER JOIN [Test].[dbo].[test3] AS t4 ON t4.[c] = t3.[a] WHERE t1.[a] = '%1'

DELETE t5 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test3] AS t5 ON t5.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t3 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test2] AS t3 ON t3.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t2 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test1] AS t2 ON t2.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t1 FROM [Test].[dbo].[test1] AS t1 WHERE t1.[a] = '%1'

But in fact only 3 DELETEs are sufficient here:

Delete t3
From Test3 t3 JOIN Test1 t1 ON t3.b = t1.a
join Test2 t2 ON t3.c = t2.a
Where t3.a like '%1'

Delete t2
From Test2 t2 JOIN Test1 t1 ON t2.b = t1.a
Where t1.a like '%1'

Delete t1
From Test1 t1
Where t1.a like '%1'

Also, I could not understand why WHERE part is always applied to TEST1 and not any other table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 10:44:59

Try the latest functions I just posted.
It has a column "HasPk" to tell if Pk is present.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 10:53:14

Peter,

There seems to be some coding error in fnCascadingDelete() function.
One of the code block included in this function's output was this:

DECLARE @Lvl INT  
SET @Lvl = 0  
DECLARE @Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey int)  
INSERT @Stage (Lvl, RowKey) 
SELECT 0, t2.[b] 
FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test1] AS t2 
ON t2.[b] = t1.[a] 
WHERE t1.[a] = '%1' 

WHILE @@ROWCOUNT > 0   
BEGIN    
	SET @Lvl = @Lvl + 1      
	INSERT  @Stage  (Lvl, RowKey)    
	SELECT  @Lvl, t.ID    
	FROM  [Test].[dbo].[Table2] AS t INNER JOIN @Stage AS s ON s.RowKey = t.ParentID       
	AND s.Lvl = @Lvl - 1    
	LEFT JOIN @Stage AS cr ON cr.RowKey = t.ID    
	WHERE  cr.RowKey IS NULL   
END    

SELECT 'DELETE FROM [Test].[dbo].[Table2] WHERE [ID] = ' + QUOTENAME(RowKey, '''') + ''  FROM @Stage  ORDER BY RowID DESC

But this table2 does not exist in the database!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 10:54:34

I see...
They are residues from my development environment. I will replace hard-coded names with dynamic names in a while.
Be patient!

Done!

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 11:23:37

Still there is some hard-coding which should be removed:

WHILE @@ROWCOUNT > 0   
BEGIN    
SET @Lvl = @Lvl + 1      
INSERT  @Stage  (Lvl, RowKey)    
SELECT  @Lvl,      t.ID    
FROM  [Test].[dbo].[test1] AS t    INNER JOIN @Stage AS s 
ON s.RowKey = t.ParentID AND s.Lvl = @Lvl - 1    
LEFT JOIN @Stage AS cr ON cr.RowKey = t.ID    
WHERE  cr.RowKey IS NULL   
END

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-18 : 11:36:40

Fixed.
Also fixed bug with misprinted output when executing the code.

quote:

Originally posted by harsh_athalye

SELECT 'DELETE FROM [Test].[dbo].[Table2] WHERE [ID] = ' + QUOTENAME(RowKey, '''') + ''  FROM @Stage  ORDER BY RowID DESC

E 12°55'05.25"
N 56°04'39.16"

p3tar
Starting Member

4 Posts

Posted - 2008-03-12 : 00:05:23

Hi all.

I have a problem executing this function, error - Incorrect syntax near '%'. Do I need to replace "%1" in generated @SQL. An example of usage would be appreciated.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 02:26:03

Yes, you should replace '%1' with the value needed for your environment.
If you want to delete EmployeeID 245, replace %1 with 245.

E 12°55'05.25"
N 56°04'39.16"

p3tar
Starting Member

4 Posts

Posted - 2008-03-12 : 18:13:14

Thanks, I'm not good with string manipulation and dynamic sql. How should I replace it? With REPLACE() function or should I use Exec sp_executesql.....Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 02:26:41

first replace and then either
exec
sp_executesql

E 12°55'05.25"
N 56°04'39.16"

p3tar
Starting Member

4 Posts

Posted - 2008-03-13 : 12:00:35

I have another problem, when I select SQL from cascading delete function for the table with four FKs relationships I get the correct SQL with the four delete statements, but when I assign that SQL to a variable so I can execute it later on I get only one delete statement for the main table.

WORKS:

SELECT SQL
FROM dbo.fnCascadingDelete ('dbo', 'SomeTable')

Result:
DELETE t4 FROM [DB].[dbo].[SomeTable] AS t1 INNER JOIN
[DB].[dbo].[ReferencingTable1] AS t4 ON t4.[ID] = t1.[ID] WHERE
t1.[ID] = '%1'
DELETE t3 FROM [DB].[dbo].[SomeTable] AS t1 INNER JOIN
[DB].[dbo].[ReferencingTable2] AS t3 ON t3.[ID] = t1.[ID] WHERE
t1.[ID] = '%1'
DELETE t2 FROM [DB].[dbo].[SomeTable] AS t1 INNER JOIN
[DB].[dbo].[ReferencingTable3] AS t2 ON t2.[ID] = t1.[ID] WHERE
t1.[ID] = '%1'
DELETE t1 FROM [DB].[dbo].[SomeTable] AS t1 WHERE t1.[ID] = '%1'

----------------------------------------------------------------------

DOESN'T WORK:

DECLARE @SQL nvarchar(4000)

SELECT @SQL = SQL
FROM dbo.fnCascadingDelete ('dbo', 'SomeTable')
--SELECT @SQL = REPLACE(@SQL, '%1', @ID)

SELECT @SQL
--Exec sp_executesql @SQL

Result:
DELETE t1 FROM [DB].[dbo].[SomeTable] AS t1 WHERE t1.[ID] = '%1'

What am I doing wrong? Thanks once more.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 02:08:59

When you assign the output from a table function, you only get the last result.
Please remember that the function returns a resultset that can hold many records. A variable can only hold one column in one record.

What you can do, is to insert the output from the function in a temporary table and loop each row there.

E 12°55'05.25"
N 56°04'39.16"

p3tar
Starting Member

4 Posts

Posted - 2008-03-16 : 18:56:15

Thanks, Peso. Today I tryed to make this script work for me, and I succeeded with while loop. Is there any script for cascading set-null or set-default?

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-23 : 07:33:18

PESO-->please post latest function for this

anji2726
Starting Member

1 Post

Posted - 2013-11-13 : 09:39:51

Hi SwePeso,

Thanks for great functions.

I came across similar scenario which need to update all the datetime columns of a particular master table and its children's and grand children's...so on in a database.

For populating all the table hierarchies for a given master table(Ex: Dept) your first function works perfectly.But updating only the datetime columns I`ve tried to change your dynamic delete scripts to dynamic update scripts for a particular department
(EX: Update t3 SET [hiredate] = DATEADD(DAY,365,[hiredate]) FROM [Test].[dbo].[dept] AS t1 INNER JOIN [Test].[dbo].[emp] AS t3 ON t3.[dept] = t1.[deptno] WHERE t1.[deptno] = '%1')
.But its not working for me in below scenarios.

Where
If a table having more than one datetime columns in it.
If a table don`t have any datetime columns but its children's has datetime columns.

Please help me in generating the update scripts for all the datetime columns in all tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources