Table ( A ) has a 1-1 relation with many tables ( B, C, D, ... ) and is defined by two columns:
ObjectType(nvarchar(100)) // name of the other table_Guid(uniqueidentifier) // record ID in the other tableAdditionally, all tables contain an IsDeleted(bit) column.
The question is:
How to list all the records from (A) that point to non-existing record in B, C, D, (...) OR to the record that has IsDeleted = 1 set?
The following will not work because ObjectType must be a parameter:
SELECT ObjectType, _Guid FROM A
where
NOT EXISTS (
select * from ObjectType where oid = _Guid
)
The following will also not work:
SELECT ObjectType, _Guid FROM A
where
NOT EXISTS (
exec('select * from '+ObjectType+' where oid =''' + _Guid + '''')
)
What am I missing?
This terrible SQL below seems to work:
DECLARE @A_tmpTable TABLE (
idx smallint Primary Key IDENTITY(1,1)
, ObjectType nvarchar(200)
, _Guid nvarchar(100)
, Oid nvarchar(100)
)
DECLARE @orphanedA_tmpTable TABLE (
Oid nvarchar(100)
)
DECLARE @_objectType nvarchar(200)
DECLARE @_Guid nvarchar(100)
DECLARE @_oid nvarchar(100)
DECLARE @i int, @numrows int
DECLARE @found bit
-- populate temp table
INSERT @A_tmpTable SELECT ObjectType, _Guid, Oid FROM A WHERE IsDeleted = 0
--SELECT * FROM @A_tmpTable
-- foreach the @A_tmpTable
SET @i = 1;
SET @numrows = (SELECT COUNT(*) FROM @A_tmpTable)
--SELECT @i, @numrows
IF @numrows > 0
WHILE (@i <= @numrows)
BEGIN
SET @ObjectType = (SELECT TOP 1 ObjectType FROM @A_tmpTable WHERE idx = @i);
SET @_Guid = (SELECT TOP 1 _Guid FROM @A_tmpTable WHERE idx = @i);
SET @_oid = (SELECT TOP 1 Oid FROM @A_tmpTable WHERE idx = @i);
DECLARE @SQL nvarchar(max) = 'IF EXISTS (SELECT * FROM '+@ObjectType+' WHERE OID =''' + @_Guid + ''' AND IsDeleted = 0)
SET @found = 1;
ELSE
SET @found = 0;
';
-- check if table record exists and save the result in the @found variable
exec sp_executesql @SQL, N'@found bit out', @found out
IF @found = 0
INSERT INTO @orphanedA_tmpTable (Oid) VALUES (@_oid);
SET @i = @i + 1
END
SELECT * FROM A WHERE Oid IN (SELECT Oid FROM @orphanedA_tmpTable)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments