Stored Procedures give you more freedom than functions, and so they would be the obvious way of developing processes in SQL Server. There is one longstanding problem with them though, although it is possible to send several results to the application from a stored procedure and read them without problems, you have big problems capturing more than one in SQL Server. Even that one has to be done with care because of the problems with nested INSERT…EXECUTE statements. You cannot have a procedure that does an INSERT-EXEC operation call another one that also does an INSERT-EXEC operation.
All these problems can now be neatly bypassed in SQL Server 2016 and later due to JSON support. Actually you could do it before then using XML, if you cared for that sort of thing. I can tell you it was a bit messy.
Here is a way of capturing as many results from a stored procedure as you want. To demonstrate, I have taken an example of a stored procedure producing two results from MSDN, and modified it slightly. To demonstrate that you really have full control over the result, I save both as a SQL Relational table. I use temporary tables in this example but you can do it any way you like.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
--just in case it already exists, delete the procedure IF Object_Id('Production.ProductList', 'P') IS NOT NULL DROP PROCEDURE Production.ProductList; --Create the procedure GO CREATE PROC Production.ProductList @ProdName NVARCHAR(50), /** Summary: > This is an example of a stored procedure that has multiple resultsets that are returned, and which we can then use outside the procedure. This works with AdventureWorks 2016 Author: PhilFactor (based on an example on MSDN that doesn't) Date: 07/03/2018 Database: AdventureWorks2016 Example: > DECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX); EXEC Production.ProductList '%tire%', @ProductAndListPrice OUTPUT, @NumberOfOrders OUTPUT; SELECT * FROM OpenJson(@ProductAndListPrice) WITH (ProductID int '$.ProductID', [Name] NVARCHAR(50) '$.Name', ListPrice money '$.ListPrice') SELECT * FROM OpenJson(@NumberOfOrders) WITH ([Name] NVARCHAR(50) '$.Name', NumberOfOrders INT '$.NumberOfOrders') Returns: > Two JSON output variables **/ -- in this case we return two results, but there is no limit -- but each result needs to be saved in an output parameter @FirstResult NVARCHAR(MAX) OUT, @SecondResult NVARCHAR(MAX) OUT AS BEGIN -- First result set saved as JSON in an output variable SELECT @FirstResult = ( SELECT Product.ProductID, Product.Name, Product.ListPrice FROM Production.Product WHERE Product.Name LIKE @ProdName FOR JSON AUTO ); -- Second result set saved as JSON in an output variable SELECT @SecondResult = ( SELECT P.Name, Count(S.ProductID) AS NumberOfOrders FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS S ON P.ProductID = S.ProductID WHERE P.Name LIKE @ProdName GROUP BY P.Name FOR JSON AUTO ); END; GO -- Execute the procedure --first declare the variables that are destined to hold the JSON results DECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX); --execute the procedure EXEC Production.ProductList '%tire%', @ProductAndListPrice OUTPUT, @NumberOfOrders OUTPUT; --create a temporary table to put it in and fill it with the first results SELECT * INTO #ProductAndListPrice FROM OpenJson(@ProductAndListPrice) WITH (ProductID int '$.ProductID', [Name] NVARCHAR(50) '$.Name', ListPrice money '$.ListPrice') --create a second temporary table to put it in and fill it with the second results SELECT * INTO #NumberOfOrders FROM OpenJson(@NumberOfOrders) WITH ([Name] NVARCHAR(50) '$.Name', NumberOfOrders INT '$.NumberOfOrders') --now make sure that we have both results safely tucked away. SELECT * FROM #ProductAndListPrice SELECT * FROM #NumberOfOrders |
We run it and voila!!
Load comments