SQL Server: Bring all data for all users

I have made one sp that checks whether a user is available or not. the sp calculates balance of users.

Now if user is selected from UI,it runs fine. but if it is not selected, then i need to bring the balance for all users in the application.

For that i have made开发者_开发技巧 cursor that brings up balance for all users. But my sp has many case-when and putting cursor inside that is not feasible solution.

Hence i made a function but threw an error of

Select statements included within a function cannot return data to a client.

I am using SQL Server 2005. Please guide me.

UPDATED: FUNCTION ADDED

ALTER FUNCTION dbo.fngetBalance()

RETURNS @balanceTable TABLE
(
    Parambalance bigint
)   
AS
BEGIN
--INSERT INTO @balanceTable (Parambalance) VALUES (1)

DECLARE getBalance CURSOR
FOR 
  SELECT  temp2.BillCharge - temp2.Receipt - temp2.AdjustedAmt AS Balance from
                    (SELECT SUM(Fees) AS BillCharge, temp.*  FROM BillDetail
                    JOIN
                    (SELECT
                            BillDetail.PatientID,
                            CONVERT(VARCHAR(15), dbo.Examination.ExamDt, 101) AS BillDate,
                           'Payment received' AS DescOfService,
                            Payment.BillID AS BillID, 
                            SUM(Isnull(Payment.PlanPaid, 0)) + SUM(IsNull(Payment.PatPaid, 0))  as Receipt,
                            SUM(ISNULL(Payment.WriteOff1, 0)) + SUM(IsNUll(Payment.Writeoff2, 0))  as AdjustedAmt
                           --Examination.ExamDt   
                          FROM
                            Billdetail
                            JOIN Payment ON Payment.BillDetailID = BillDetail.BillDetailID
                            JOIN dbo.Examination ON dbo.BillDetail.ExaminationID = dbo.Examination.ExaminationID 
                            GROUP BY Payment.BillID, Examination.ExamDt,BillDetail.PatientID) temp ON 
                            temp.BillID = Billdetail.BillID
                            GROUP BY temp.BillID, temp.Receipt, temp.AdjustedAmt,temp.BillDate, temp.DescOfService, temp.PatientID) AS temp2
                            ORDER BY temp2.PatientID
--SELECT temp-temp2  AS balance from
--(SELECT SUM (BillDetail.Fees) AS fees  FROM dbo.BillDetail GROUP BY billdetail.PatientID) AS temp
--JOIN
--(SELECT SUM (payment.planPaid) + SUM(payment.patpaid) + SUM (Payment.WriteOff1) + SUM (Payment.WriteOff2) AS payment
--FROM dbo.Payment GROUP BY payment.PatientID) temp2
OPEN getBalance 
FETCH NEXT FROM getBalance
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM getBalance
END

CLOSE getBalance
DEALLOCATE getBalance
RETURN
END

You need to insert the results of your fetch statement into your return value table.

  CREATE FUNCTION dbo.fngetBalance()
  RETURNS @balanceTable TABLE
  (
     Parambalance bigint
  )
  AS
  BEGIN
     DECLARE getBalance CURSOR FOR
     SELECT [... insert your code here ...]

     DECLARE @balance bigint

     OPEN getBalance
     FETCH NEXT FROM getBalance
     INTO @balance
     WHILE @@FETCH_STATUS = 0
     BEGIN
       INSERT INTO @balanceTable VALUES(@balance)
       FETCH NEXT FROM getBalance
       INTO @balance
     END
     CLOSE getBalance
     DEALLOCATE getBalance
     RETURN   
  END

It sounds like you want to use a table valued function. You need to return the table from within the function. Note: To generate a useable result set you need to call the function like so:

select * from myFunction

Hope that helps.