cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Turn Stored Procedure Output into View or Table

Jump to solution

Hi,

I need help storing the output of a stored procedure into a view or a table. I'm referring specifically to [dbo].[ReportLocationConsumptions]  under Programmability. 

The code to execute the stored procedure is the following:

 

USE [FNMScompliance]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[ReportLocationConsumptions]

SELECT 'Return Value' = @return_value

GO

 

Thanks!

0 Kudos
1 Solution

Hi Sabrina,

You can always insert the data returned from a stored procedure into a table.

See the following code as an example:

IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL   DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable 
(
      RowIndex                         INT
    , LicenseName                      NVARCHAR(256)
    , LicenseType                      NVARCHAR(256)
    , CostPerRight                     MONEY
    , Publisher                        NVARCHAR(64)
    , CoveredProducts                  NVARCHAR(MAX)
    , LicenseVersion                   NVARCHAR(60)
    , LicenseEdition                   NVARCHAR(60)
    , LocationHierarchicalName         NVARCHAR(MAX)
    , Purchased                        NVARCHAR(256)
    , Consumed                         NVARCHAR(256)
    , LicenseCost                      MONEY
    , ComplianceCost                   MONEY
    , ShortfallAvailability            INT
    , Level1                           NVARCHAR(256)
    , Level2                           NVARCHAR(256)
    , Level3                           NVARCHAR(256)
    , Level4                           NVARCHAR(256)
    , PurchasedLocally                 INT
    , ConsumedLocally                  INT
    , LocalShortfallAvailability       INT
    , LocalCompliance                  MONEY
    , UsedLocally                      INT
    , RolledUpPurchased                INT
    , RolledUpConsumed                 INT
    , RolledUpUsed                     INT
    , AssignedLocally                  INT
    , LocationTreeLevel                INT
    , CoveredProductsAndEdition        NVARCHAR(MAX)
    , LocationPath                     NVARCHAR(256)
    , Path                             NVARCHAR(256)
    , CoveredInstallations             NVARCHAR(MAX)
)

INSERT INTO #ReturnTable 
EXEC [dbo].[ReportLocationConsumptions]

SELECT * FROM #ReturnTable 

Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.

T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.

You can call the function by simply doing a select:

SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)

Please note:

The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.


I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'

 

View solution in original post

2 Replies

Hi Sabrina,

You can always insert the data returned from a stored procedure into a table.

See the following code as an example:

IF OBJECT_ID ('tempdb..#ReturnTable') IS NOT NULL   DROP TABLE #ReturnTable;
CREATE TABLE #ReturnTable 
(
      RowIndex                         INT
    , LicenseName                      NVARCHAR(256)
    , LicenseType                      NVARCHAR(256)
    , CostPerRight                     MONEY
    , Publisher                        NVARCHAR(64)
    , CoveredProducts                  NVARCHAR(MAX)
    , LicenseVersion                   NVARCHAR(60)
    , LicenseEdition                   NVARCHAR(60)
    , LocationHierarchicalName         NVARCHAR(MAX)
    , Purchased                        NVARCHAR(256)
    , Consumed                         NVARCHAR(256)
    , LicenseCost                      MONEY
    , ComplianceCost                   MONEY
    , ShortfallAvailability            INT
    , Level1                           NVARCHAR(256)
    , Level2                           NVARCHAR(256)
    , Level3                           NVARCHAR(256)
    , Level4                           NVARCHAR(256)
    , PurchasedLocally                 INT
    , ConsumedLocally                  INT
    , LocalShortfallAvailability       INT
    , LocalCompliance                  MONEY
    , UsedLocally                      INT
    , RolledUpPurchased                INT
    , RolledUpConsumed                 INT
    , RolledUpUsed                     INT
    , AssignedLocally                  INT
    , LocationTreeLevel                INT
    , CoveredProductsAndEdition        NVARCHAR(MAX)
    , LocationPath                     NVARCHAR(256)
    , Path                             NVARCHAR(256)
    , CoveredInstallations             NVARCHAR(MAX)
)

INSERT INTO #ReturnTable 
EXEC [dbo].[ReportLocationConsumptions]

SELECT * FROM #ReturnTable 

Hover, converting the procedure into a multi-statement table-valued functions (MSTVF) is a more elegant solution.

T-SQL does not allow using temp tables in a function, so you have to convert all temp tables into table variables first. See the attached file 'ReportlocationconsumptionsMSTVF.sql' as an example.

You can call the function by simply doing a select:

SELECT * FROM [dbo].[ReportlocationconsumptionsMSTVF] (NULL)

Please note:

The original (FNMS 2022 R1) procedure 'ReportLocationConsumptions' is not only very poorly formatted and commented, it also has a bug.


I added the missing condition in line #35/36 in the attached file 'ReportLocationConsumptions_FNMS2022R1_CORRECTED.SQL'

 

Thank you for the answer and explanation why. I was able to create the tableau and bring it into Tableau. 

0 Kudos