{"id":77515,"date":"2018-03-07T18:40:33","date_gmt":"2018-03-07T18:40:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77515"},"modified":"2019-01-22T17:22:38","modified_gmt":"2019-01-22T17:22:38","slug":"using-stored-procedures-sql-server-return-several-results","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-stored-procedures-sql-server-return-several-results\/","title":{"rendered":"Using Stored Procedures in SQL Server that return several results."},"content":{"rendered":"<p>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,\u00a0you 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\u00a0 INSERT&#8230;EXECUTE statements. You\u00a0cannot have a procedure\u00a0 that does an INSERT-EXEC operation call another one that also does an INSERT-EXEC operation.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012-simple-talk lang:tsql decode:true \">--just in case it already exists, delete the procedure\r\nIF Object_Id('Production.ProductList', 'P') IS NOT NULL \r\n  DROP PROCEDURE Production.ProductList;\r\n--Create the procedure  \r\nGO\r\nCREATE PROC Production.ProductList @ProdName NVARCHAR(50),\r\n\/**\r\nSummary: &gt;\r\nThis is an example of a stored procedure that has multiple resultsets \r\nthat are returned, and which we can then use outside the procedure.\r\nThis works with AdventureWorks 2016\r\nAuthor: PhilFactor (based on an example on MSDN that doesn't)\r\nDate: 07\/03\/2018\r\nDatabase: AdventureWorks2016\r\nExample: &gt;\r\nDECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX);\r\nEXEC Production.ProductList '%tire%', @ProductAndListPrice OUTPUT,\r\n  @NumberOfOrders OUTPUT;\r\nSELECT * FROM OpenJson(@ProductAndListPrice)\r\nWITH (ProductID int '$.ProductID', [Name] NVARCHAR(50) '$.Name',\r\n      ListPrice money '$.ListPrice')\r\nSELECT * FROM OpenJson(@NumberOfOrders)\r\nWITH ([Name] NVARCHAR(50) '$.Name', NumberOfOrders INT '$.NumberOfOrders')\r\nReturns: &gt;\r\n  Two JSON output variables\r\n**\/\r\n-- in this case we return two results, but there is no limit\r\n-- but each result needs to be saved in an output parameter\r\n @FirstResult NVARCHAR(MAX) OUT, @SecondResult NVARCHAR(MAX) OUT\r\nAS\r\n  BEGIN\r\n    -- First result set saved as JSON in an output variable \r\n    SELECT @FirstResult =\r\n      (\r\n      SELECT Product.ProductID, Product.Name, Product.ListPrice\r\n        FROM Production.Product\r\n        WHERE Product.Name LIKE @ProdName\r\n      FOR JSON AUTO\r\n      );\r\n    -- Second result set saved as JSON in an output variable  \r\n    SELECT @SecondResult =\r\n      (\r\n      SELECT P.Name, Count(S.ProductID) AS NumberOfOrders\r\n        FROM Production.Product AS P\r\n          JOIN Sales.SalesOrderDetail AS S\r\n            ON P.ProductID = S.ProductID\r\n        WHERE P.Name LIKE @ProdName\r\n        GROUP BY P.Name\r\n      FOR JSON AUTO\r\n      );\r\n  END;\r\nGO\r\n\r\n-- Execute the procedure \r\n--first declare the variables that are destined to hold the JSON results\r\nDECLARE @ProductAndListPrice NVARCHAR(MAX), @NumberOfOrders NVARCHAR(MAX);\r\n--execute the procedure\r\nEXEC Production.ProductList '%tire%', @ProductAndListPrice OUTPUT,\r\n  @NumberOfOrders OUTPUT;\r\n\r\n--create a temporary table to put it in and fill it with the first results\r\nSELECT * INTO #ProductAndListPrice FROM OpenJson(@ProductAndListPrice)\r\nWITH (ProductID int '$.ProductID', [Name] NVARCHAR(50) '$.Name', ListPrice money '$.ListPrice')\r\n\r\n--create a second temporary table to put it in and fill it with the second results\r\nSELECT * INTO #NumberOfOrders FROM OpenJson(@NumberOfOrders)\r\nWITH ([Name] NVARCHAR(50) '$.Name', NumberOfOrders INT '$.NumberOfOrders')\r\n\r\n--now make sure that we have both results safely tucked away.\r\nSELECT * FROM #ProductAndListPrice\r\nSELECT * FROM #NumberOfOrders<\/pre>\n<p>We run it and voila!!<br \/>\n <img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-77516\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/TwoResults.png\" alt=\"\" width=\"294\" height=\"518\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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,\u00a0you have big problems capturing more&#8230;&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5134,4252,4727],"coauthors":[6813],"class_list":["post-77515","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-prompt","tag-t-sql-programming","tag-t-sql-stored-procedure"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77515","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77515"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77515\/revisions"}],"predecessor-version":[{"id":77518,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77515\/revisions\/77518"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77515"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77515"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77515"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77515"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}