5

I'm trying to writing a stored procedure that will check for orphan data before our customers attempt an upgrade, as orphaned records can cause issues. This is what I have thus far;

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX) 
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])

BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END


------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

END;

At the moment, this script is working, however the output is;

Imagine 1

I want to return the results on a single line, and have each column with their own name, for example: @OrphanAuditItems result shows as column name: OrphanAuditItems, and so on.

How's the best way to achieve this?

Thanks, Tom

3

You will need to mix your 3 dynamic SQLs into 1 that returns 3 columns, or keep each dynamic SQL but mix their result at the end. There are 2 possible solutions following:


One solution is to use UNION ALL and the PIVOT to get 1 row with 3 columns, all inside the dynamic SQL.

Change this part:

DECLARE @OrphanAuditItems NVARCHAR(MAX)
DECLARE @OrphanAuditAnswers NVARCHAR(MAX) 
DECLARE @OrphanAuditQuestion NVARCHAR(MAX)

--.....

SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC (@OrphanAuditItems)
EXEC(@OrphanAuditAnswers)
EXEC(@OrphanAuditQuestion)

For this:

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    ;WITH PrePivot AS
    (
        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditItems''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
        WHERE 
            FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits]) 

        UNION ALL

        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditAnswers''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
        WHERE 
            AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])

        UNION ALL

        SELECT 
            Amount = COUNT(*),
            Type = ''OrphanAuditQuestion''
        FROM 
            [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
        WHERE 
            AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits]) 
    )
    SELECT
        P.OrphanAuditItems,
        P.OrphanAuditAnswers,
        P.OrphanAuditQuestion
    FROM
        PrePivot AS T
        PIVOT (
            MAX(T.Amount) FOR T.Type IN ([OrphanAuditItems], [OrphanAuditAnswers], [OrphanAuditQuestion])
        ) AS P '

-- PRINT (@DynamicSQL)
EXEC (@DynamicSQL)

Make sure to use the PRINT to validate the resulting SQL before executing the EXEC.


Another solution is to retrieve the values into variables, using sp_executesql with OUTPUT parameters:

-- Items
DECLARE @OrphanAuditItems INT

DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    SELECT
        @OrphanAuditItems = COUNT(*)
    FROM
        #OrphanResults AS O
        CROSS JOIN [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems]
    WHERE 
        FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditItems INT OUTPUT',
    @OrphanAuditItems = @OrphanAuditItems OUTPUT


-- Answers
DECLARE @OrphanAuditAnswers INT

SET @DynamicSQL = N'
    SELECT 
        @OrphanAuditAnswers = COUNT(*) 
    FROM 
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
    WHERE 
        AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditAnswers INT OUTPUT',
    @OrphanAuditAnswers = @OrphanAuditAnswers OUTPUT


-- Questions
DECLARE @OrphanAuditQuestion INT

SET @DynamicSQL = N'
    SELECT 
        @OrphanAuditQuestion = COUNT(*) 
    FROM 
        [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
    WHERE 
        AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'

EXEC sp_executesql 
    @stmt = @DynamicSQL,
    @params = N'@OrphanAuditQuestion INT OUTPUT',
    @OrphanAuditQuestion = @OrphanAuditQuestion OUTPUT

SELECT
    OrphanAuditItems = @OrphanAuditItems,
    OrphanAuditAnswers = @OrphanAuditAnswers,
    OrphanAuditQuestion = @OrphanAuditQuestion

I find the latter a bit more flexible and readable than the first.

  • Amazing, thank you so much for this EZLo! I have to admit that I have never seen this type of code before as I'm still learning, but this is working perfectly. – Tom_W Mar 15 at 11:33
2

One method is with subqueries for each column. The example below also uses QUOTENAME to enclose identifiers. Personally, I would name schemas as to conform to regular identifier naming rules rather than using the numeric TenantId as the schema name.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
GO

CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]

@TenantId   INT = NULL

AS
BEGIN

DECLARE @OrphanAuditData NVARCHAR(MAX)

------------------------------------------------------------------------------------------------
/* Throw an error if the TenantId is NULL or Invalid.               */
------------------------------------------------------------------------------------------------
IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])
BEGIN
    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
END


------------------------------------------------------------------------------------------------
/* Checks for Orphan records related to the Audits table                */
------------------------------------------------------------------------------------------------
SET @OrphanAuditData = N'SELECT
    (SELECT COUNT(*) 
        FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditItems] 
        WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[Audits])) AS OrphanAuditItems
'
+ N'    ,(SELECT COUNT(*)
        FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditAnswers] 
        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].' + QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditItems])) AS OrphanAuditAnswers
'
+ N'    ,(SELECT COUNT(*)
        FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '.[AuditQuestion] 
        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].'+ QUOTENAME(CAST(@TenantId AS NVARCHAR)) + '].[Audits])) AS OrphanAuditQuestion;'

EXEC (@OrphanAuditData);

END;
GO
0

I would personally insert all of the data into a holding table in the form of ([COUNT] INT NOT NULL, [TableName] NVARCHAR(MAX) NOT NULL) as below. You can then use PIVOT as below to get the values for each table.

As an aside, please also note the use of the QUOTENAME function - this will ensure your code isn't vulnerable to SQL injection if the table is passed in as a variable (I would look to simply provide a list of table names and have it run through them all dynamically personally - happy to write this out and provide as an answer if you'd like).

CREATE TABLE #Datas (ID INT);

INSERT INTO #Datas
(
    ID
)
VALUES
( 1  -- ID - int
    )
    GO

DECLARE @SQL1 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName1'' FROM ' + QUOTENAME('#Datas') + ';'
DECLARE @SQL2 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName2'' FROM ' + QUOTENAME('#Datas') + ';'
DECLARE @SQL3 NVARCHAR(MAX) = 'SELECT COUNT(1), ''TableName3'' FROM ' + QUOTENAME('#Datas') + ';'

CREATE TABLE #Counts (Cnt INT, Tbl NVARCHAR(MAX));

INSERT INTO #Counts
EXEC (@SQL1);

INSERT INTO #Counts
EXEC (@SQL2);

INSERT INTO #Counts
EXEC (@SQL3);

SELECT * FROM #Counts

PIVOT (SUM(Cnt) FOR Tbl IN ([TableName1],[TableName2],[TableName3])) AS d

DROP TABLE #Datas;
DROP TABLE #Counts

dbfiddle repro

0

If you're using SQL Server 2012 or later, you can also just use WITH RESULT SETS with your EXEC:

exec (N'select 1') 
WITH RESULT SETS
(
    (
        ID int
    )
)

Result Set

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.