mjhjmtu

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
 )
)