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"
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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.
Go to Top of Page

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"
Go to Top of Page

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?
Go to Top of Page

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-23 : 07:33:18
PESO-->please post latest function for this
Go to Top of Page

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.



Go to Top of Page
   

- Advertisement -