CODEWITHSUNDEEP
sql-server
Cataster
Cataster

Reputation: 3481

Export stored procedure results into a table

I have the following stored procedure that generates SQL statements based on certain conditions.

I'd like for the SQL statements to also return the actual results into a table.

CREATE TABLE [dbo].[Rejects](
    [Report Year] [varchar](100) NULL,
    [COS - Country Code] [varchar](100) NULL,
    [Franchise - Style Code] [varchar](100) NULL,
    [Product - Style Code] [varchar](100) NULL
) ON [PRIMARY]

How could I do that?

DECLARE
    @ReportYearCmd VARCHAR(1000),
    @CosCountCmd VARCHAR(1000),
    @FranchiseCountCmd AS VARCHAR(1000),
    @ProductCountCmd AS VARCHAR(1000);

WITH Validations AS (
    SELECT TOP 1 * FROM [Handshake] WHERE [Status] = 'Loaded' AND [Update Time] = ( SELECT MAX( [Update Time] ) FROM Handshake )
)
UPDATE Validations 
SET 
    @ReportYearCmd = CASE WHEN Report_Year_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Report Year] AS [Report Year] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] );'
    END,
    @CosCountCmd = CASE WHEN COS_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] );' 
    END,
    @FranchiseCountCmd = CASE WHEN  Franchise_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Franchise - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] );' 
    END,
    @ProductCountCmd = CASE WHEN Product_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] );' 
    END,
    [Status] = CASE
        WHEN ( Report_Year_Count = 0 AND COS_Count = 0 AND Franchise_Count = 0 AND Product_Count = 0 ) THEN 'Good'
        ELSE 'Rejects'
    END
FROM [Validations] 
OUTER APPLY (

    SELECT 
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] ) ), 0 ) AS [Report_Year_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] ) ), 0 ) AS [COS_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Franchise_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Product_Count]

) AS [ValidationCounts];

-- Return validation sql statements --

SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

Basically, right after this part:

-- Return validation sql statements --

SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

Currently, when I execute this SP I get something like:

ReportYearCmd CosCountCmd FranchiseCountCmd  ProductCountCmd

NULL          SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] )        NULL               SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] )

I'd like to return the actual result, i.e. if the Cmd is NOT NULL, as in the example above, return CosCountCmd and ProductCountCmd data:

Report Year COS - Country Code   Franchise - Style Code   Product - Style Code 
NULL        reject1              NULL                     Rejectxy
NULL        reject2              NULL                     Reject1234
NULL        NULL                 NULL                     Reject567

Upvotes: 1

Views: 232

Answers (2)

critical_error
critical_error

Reputation: 6696

Try this:

/* Execute the dynamic SQL to return their resultsets if cmd variable IS NOT NULL */

IF @ReportYearCmd IS NOT NULL
    EXEC ( @ReportYearCmd );

IF @CosCountCmd IS NOT NULL
    EXEC ( @CosCountCmd );

IF @FranchiseCountCmd IS NOT NULL
    EXEC ( @FranchiseCountCmd );

IF @ProductCountCmd IS NOT NULL
    EXEC ( @ProductCountCmd );

Note: This potentially returns four different resultsets--not a pivoted result.

EDIT:

Return a single resultset for each reject category.

This is about as close as you're going to get without having to do a lot of extra work:

DECLARE @Rejects TABLE ( 
    [Report Year] VARCHAR(50),
    [COS - Country Code] VARCHAR(50),
    [Product - Style Code] VARCHAR(50),
    [Franchise - Style Code] VARCHAR(50)
);

IF @ReportYearCmd IS NOT NULL
    INSERT INTO @Rejects ( [Report Year] ) EXEC ( @ReportYearCmd );
    
IF @CosCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [COS - Country Code] ) EXEC ( @CosCountCmd );
    
IF @FranchiseCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [Franchise - Style Code] ) EXEC ( @FranchiseCountCmd );
    
IF @ProductCountCmd IS NOT NULL
    INSERT INTO @Rejects ( [Product - Style Code] ) EXEC ( @ProductCountCmd );

-- Return resultset.
SELECT * FROM @Rejects;

Returns something like:

+-------------+--------------------+------------------------+----------------------+
| Report Year | COS - Country Code | Franchise - Style Code | Product - Style Code |
+-------------+--------------------+------------------------+----------------------+
| NULL        | reject1            | NULL                   | NULL                 |
| NULL        | reject2            | NULL                   | NULL                 |
| NULL        | NULL               | NULL                   | Rejectxy             |
| NULL        | NULL               | NULL                   | Reject1234           |
| NULL        | NULL               | NULL                   | Reject567            |
+-------------+--------------------+------------------------+----------------------+

Your calling application will need to handle NULL values when processing the results as there is no easy way to "roll-up" NULL columns/rows to reduce the results as shown in your expectation.

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

) AS [ValidationCounts];
-- insertion here-
Insert into temp_table (col1, col2, col3, ...)
SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

Upvotes: 0

Related Questions