Returning multiple values from sql stored procedure

Question

I'm trying to return 4 variables from a stored procedure to a page in ASP.Net VB Script. It's only returning the first result then breaking. My knowledge of SQL is limited. I have tried the following:

SQL:

Alter PROCEDURE  [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT COUNT(Receiptno) as requestsSent
FROM [demofeepay3].[dbo].[vwallrequests]
Where Orgid = @id

SELECT Sum(totamount) as requestTotal
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id

SELECT Sum(totamount) as requestTotalPaid
FROM [demofee].[dbo].[vwallrequests]
Where Orgid = @id AND status = 'paid'

SELECT Sum(totamount) as requestTotalUnpaid
FROM [demo].[dbo].[vwallrequests]
Where Orgid = @id AND status = 'unpaid'

ASP.NET

Function RequestsSent()
    Dim objCmd2 As sqlCommand
    Dim objRdr2 As sqlDataReader
    objCmd2 = New SqlCommand("spDashboardPaymentRequests", objConn)
    objCmd2.CommandType = CommandType.StoredProcedure
    objCmd2.Parameters.AddWithValue("@orgid", Session("orgid"))
    objConn.Open()
    objRdr2 = objCmd2.ExecuteReader
    objRdr2.Read()
    Session("RequestsSent") = objRdr2("requestsSent")
    Session("RequestsTotal") = objRdr2("requestTotal")
    Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")
    Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")
    objConn.Close()
End Function

Instead of a function make it a method, you are not returning anything. Declare some output params for your direction and set them in SQL... Also that sql can be reduced into 1 statement. — Trevor
– Trevor, Commented Aug 18, 2016 at 11:02

amiry jd · Accepted Answer · 2016-08-18 09:40:07Z

You have multiple selects which means multiple result-sets. So you have to move through them:

objRdr2.Read()
Session("RequestsSent") = objRdr2("requestsSent")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotal") = objRdr2("requestTotal")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalPaid") = objRdr2("requestTotalPaid")

objRdr2.NextResult()
objRdr2.Read()
Session("RequestsTotalUnpaid") = objRdr2("requestTotalUnpaid")

Or, you can change the sproc to return one resultset with multiple columns:

Alter PROCEDURE  [dbo].[spDashboardPaymentRequests]
@id integer
AS
SELECT
    (SELECT COUNT(Receiptno) FROM [demofeepay3].[dbo].[vwallrequests] Where Orgid = @id) 
    as requestsSent,

    (SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id) 
    as requestTotal,

    (SELECT Sum(totamount) FROM [demofee].[dbo].[vwallrequests] Where Orgid = @id AND status = 'paid')
    as requestTotalPaid,

    (SELECT Sum(totamount) FROM [demo].[dbo].[vwallrequests] Where Orgid = @id AND status = 'unpaid')
    as requestTotalUnpaid

sagi · Accepted Answer · 2016-08-18 09:31:41Z

0

I don't know about asp , but you probably do, so here is a query to get all of them as the same row , adjust your asp code accordingly:

SELECT COUNT(Receiptno) as requestsSent,
       (SELECT Sum(totamount) 
        FROM [demofee].[dbo].[vwallrequests]
        Where Orgid = @id) as requestTotal,
       --Query 3,
       --Query 4
FROM [demofeepay3].[dbo].[vwallrequests]
Where Orgid = @id

answered Aug 18, 2016 at 9:31

sagi

40.6k7 gold badges66 silver badges87 bronze badges

1 Comment

Johnn5er Over a year ago

Cheers mate, will try this, and let you know :-)

Collectives™ on Stack Overflow

Returning multiple values from sql stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related