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
Best Answer
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 thePIVOT
to get 1 row with 3 columns, all inside the dynamic SQL.Change this part:
For this:
Make sure to use the
PRINT
to validate the resulting SQL before executing theEXEC
.Another solution is to retrieve the values into variables, using
sp_executesql
withOUTPUT
parameters:I find the latter a bit more flexible and readable than the first.