SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab1(ID INT, Name NVARCHAR(10), Amount MONEY);
INSERT INTO tab1(ID, Name, Amount)
VALUES (1, 'Alexander', 10),(2, 'Jimmy', 100), (6, 'Billy', 20);
3 rows affected

create procedure dbo.pVendorBalance 
AS
BEGIN
   -- first resultset
   SELECT * FROM tab1 WHERE ID <=2;
    
   -- second resultset
   SELECT * FROM tab1 WHERE ID > 5;
END;
EXEC  dbo.pVendorBalance 


ID
Name
Amount


1
Alexander
10.0000

2
Jimmy
100.0000




ID
Name
Amount


6
Billy
20.0000


create procedure dbo.pVendorBalance2 @resultSet1 NVARCHAR(MAX) OUT, @resultSet2 NVARCHAR(MAX) OUT
AS
BEGIN
    SELECT @resultSet1 =  ( SELECT * FROM tab1 WHERE ID <=2 FOR JSON AUTO ),
           @resultSet2 = (SELECT * FROM tab1 WHERE ID > 5    FOR JSON AUTO);
END;
DECLARE @r1 NVARCHAR(MAX), @r2 NVARCHAR(MAX);
EXEC dbo.pVendorBalance2 @r1 OUT, @r2 OUT;

SELECT @r1, @r2;

-- first resultset as table
SELECT * 
INTO #t1
FROM OpenJson(@r1)
WITH (ID int '$.ID', [Name] NVARCHAR(50) '$.Name',Amount money '$.Amount');

-- second resultset as table
SELECT *  
INTO #t2
FROM OpenJson(@r2)
WITH (ID int '$.ID', [Name] NVARCHAR(50) '$.Name',Amount money '$.Amount');

SELECT * FROM #t1;
SELECT * FROM #t2;


(No column name)
(No column name)


[{"ID":1,"Name":"Alexander","Amount":10.0000},{"ID":2,"Name":"Jimmy","Amount":100.0000}]
[{"ID":6,"Name":"Billy","Amount":20.0000}]




ID
Name
Amount


1
Alexander
10.0000

2
Jimmy
100.0000




ID
Name
Amount


6
Billy
20.0000