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 more
