Hello,

I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first?

The Stored Procs are:

CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency
@CID int,
@CY char(3)
AS
SELECT Distinct S.BalancePoolName
FROM SiteRef S
 INNER JOIN Account A ON A.PoolId=S.ID      
      Inner JOIN AccountBalance AB ON A.Id = AB.AccountId
      Inner JOIN AccountPool AP On AP.Id=A.PoolId
Where A.CompanyId=@CID And AB.Currency=@CY

CREATE PROCEDURE USP_BalanceDateByAccountPool
@PoolName varchar(50)
AS
Declare @DT datetime
Select @DT=
(Select MAX(AccountBalance.DateX) From Company Company
 INNER JOIN Account Account ON Company.Id = Account.CompanyId
      INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
      Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName)
SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance
FROM Company Company
 INNER JOIN Account Account ON Company.Id = Account.CompanyId
      INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID
      Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId
WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT
Order By AccountBalance.DateX DESC

Any assistance would be greatly appreciated.

Thank you,
Dave

Normally, this should work with a single sql-statement, so two procedures and a call of the second procedure for each row of the first result isn't required.

Your second query should use a subquery (the first query) and add the BalancePoolName as output column with a Group By BalancePoolName.

Then use this complete query as subquery in the join-part of your third and move these conditions

SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT

into the ON-Part of the join.