Ggtkuk

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;

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

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.

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

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

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

    )

)