HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Giving EXEC (@Variable) a Column name and Concatenation

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqldbasql-serverstackoverflow
concatenationcolumnandnameexecvariablegiving

Problem

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]
WH

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#232238, answer score: 3

Revisions (0)

No revisions yet.