Solved

Returning only the final rowset

Posted on 2002-06-13
16
440 Views
Last Modified: 2012-08-13
I have a Stored procedure that:
1) selects data into a temporary table
2) creates a cursor which loops through that temporary table
3) updates each row of that temporary table based on the OUTPUT of another stored procedure.

The procedure works fine, however, it returns multiple rowsets, and I ONLY want to return the final rowset.

(Here is the code)
CREATE PROCEDURE [dbo].[spRpt013CommissionCategory]
      @iBusinessPeriod datetime = NULL,
      @iDealerGroupID INT = NULL,
      @iDealerID INT = NULL,
      @iAgentGroupID INT = NULL,
      @iAgentID INT=NULL,
      @iCategoryID INT = NULL,
      @iCategoryGroupID INT = NULL,
      @iDetail CHAR(1) = NULL
AS
DECLARE @strSQL NVARCHAR(4000)
DECLARE @contract INT
DECLARE @surcharge VARCHAR(3)
DECLARE @strSurchargeList VARCHAR(30)

BEGIN
SET NOCOUNT ON
If @iBusinessPeriod IS NULL
Select @iBusinessPeriod = GETDATE()
--First create the basic SQL String
SELECT @strSQL = N'SELECT     CoverageConfigHdr.SelectionName, Contract.FirstName2, Contract.LastName2, Account.AccountName, Contract.ContractId, Contract.NewUsedOtherStatus,
                       Vehicle.VehicleMake, Vehicle.VehicleModel, Contract.TermMileage, Contract.TermMonths, Payment.Amount, ''4wd'' As Surcharge, DealerAccount.AccountID AS
      DealerAccountID, DealerAccount.AccountName AS DealerAccountName, Account.AccountID AS PayeeAccountID, Account.AccountName AS PayeeAccountName,
      DistributionCategory.DistributionCategoryID, DistributionCategory.DistributionCategoryDesc, DistributionCategoryType.DistributionCategoryTypeID, DistributionCategoryType.DistributionCategoryTypeDesc,
      Remit.RemitID, Contract.EffectiveDt, CoverageType.NewUsedFlg, CoverageConfigHdr.CvgConfigId,
      BusinessPeriod.BeginDate, BusinessPeriod.EndDate
      INTO #tempCommission
      FROM
      Payment LEFT OUTER JOIN
                      RateFundsDistribution ON Payment.PayeeAccountId = RateFundsDistribution.PayeeAccountId LEFT OUTER JOIN
                      DistributionCategory ON RateFundsDistribution.DistributionCategoryID = DistributionCategory.DistributionCategoryID LEFT OUTER JOIN
              DistributionCategoryType ON DistributionCategory.DistributionCategoryTypeID = DistributionCategoryType.DistributionCategoryTypeID INNER JOIN
                      Account ON Payment.PayeeAccountId = Account.AccountId LEFT OUTER JOIN
            AccountAccountGroup ON AccountAccountGroup.AccountID = Account.AccountID LEFT OUTER JOIN
            AccountGroup ON AccountAccountGroup.AccountGroupID = AccountGroup.AccountGroupID INNER JOIN
      BankCheck ON Payment.BankCheckID = BankCheck.BankCheckID INNER JOIN
      BankAccount ON BankCheck.BankAccountID = BankAccount.BankAccountID INNER JOIN
      Deposit ON BankAccount.BankAccountID = Deposit.BankAccountID INNER JOIN
                      Remit ON Deposit.DepositID = Remit.DepositID INNER JOIN
                      Batch ON Remit.BatchID = Batch.BatchID INNER JOIN
            Contract ON Batch.BatchID = Contract.BatchID INNER JOIN
            Account DealerAccount ON Contract.AccountID = DealerAccount.AccountID INNER JOIN
                      Vehicle ON Contract.VehicleId = Vehicle.VehicleId INNER JOIN
                      RateSelection ON Contract.RateSelectionId = RateSelection.RateSelectionId INNER JOIN
                      CoverageConfigHdr ON RateSelection.CvgConfigId = CoverageConfigHdr.CvgConfigId INNER JOIN
                      CoverageType ON CoverageConfigHdr.CvgTypeID = CoverageType.CoverageTypeId LEFT OUTER JOIN
            BusinessPeriod ON Payment.BusinessPeriodDate BETWEEN BusinessPeriod.BeginDate AND BusinessPeriod.EndDate
      WHERE ''' + CONVERT(VARCHAR(11),@iBusinessPeriod) + N''' BETWEEN BusinessPeriod.BeginDate and BusinessPeriod.EndDate  '

IF @iDealerID IS NOT NULL
SELECT @strSQL = @strSQL + N' AND DealerAccount.AccountID = ' + CONVERT(VARCHAR(11),@iDealerID)

If @iAgentID IS NOT NULL
SELECT @strSQL = @strSQL + N' AND Account.AccountID = ' + CONVERT(VARCHAR(11),@iAgentID)

IF @iDealerGroupID IS NOT NULL
SELECT @strSQL = @strSQL + N' AND AccountGroup.AccountGroupID = ' + CONVERT(VARCHAR(11),@iDealerGroupID)

IF @iAgentGroupID IS NOT NULL
SELECT @strSQL = @strSQL + N' AND AccountGroup.AccountGroupID = ' + CONVERT(VARCHAR(11),@iAgentGroupID)

If @iCategoryID IS NOT NULL
SELECT @strSQL = @strSQL + N' AND AccountCategory.AccountCategoryId = ' + CONVERT(VARCHAR(11),@iCategoryID)

EXEC(@strSQL)

--Now go through and concatenate together all the surcharges for each contract, and update each record of the tempCommissions table

DECLARE surcharge_cursor CURSOR FOR
SELECT ContractID, Surcharge FROM tempCommission
OPEN surcharge_cursor

-- Perform the first fetch.
FETCH NEXT FROM surcharge_cursor
INTO @contract, @surcharge

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM surcharge_cursor

EXEC spGetAllSurchargesByContract @contract,  @iSurcharges = @strSurchargeList

Update tempCommission Set Surcharge = ISNULL(@strSurchargeList,' ') WHERE ContractID = @contract

END

CLOSE surcharge_cursor
DEALLOCATE surcharge_cursor
SET NOCOUNT Off
SELECT * FROM tempCommission

END
0
Comment
Question by:ChaunceyGardener
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Change:
SELECT @strSQL = ...

To
Set @strSQL = ...

How are you using the rowsets?

Anthony
0
 

Author Comment

by:ChaunceyGardener
Comment Utility
Change made. I am calling this stored procedure straight from a Crystal Report (v.8.5) via ODBC. The report only sees the first rowset created inside the cursor.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I am afraid I have never used Crystal Reports with Stored Procedures.  Perhaps someone else can jump in.

Anthony
0
 
LVL 1

Expert Comment

by:Brighton
Comment Utility
I believe Crystal Reports uses the first SELECT statement it encounters ( Only those that return data to the screen) when the datasourse is a Stored Procedure to determine the fields and data that the report can see.  But looking at your code, all your select statements place the data in variables and you don't do a SELECT * until the end.  But with the EXEC spGetAllSurchargesByContract statement inside your cursor it may have unwanted SELECT statement inside it that could be causing the problem.
Does spGetAllSurchargesByContract have any select statements that return data to the screen other than the return statement that populates strSurchargeList?
0
 

Author Comment

by:ChaunceyGardener
Comment Utility
>>Does spGetAllSurchargesByContract have any select statements that return data to the screen other than
the return statement that populates strSurchargeList?<<

Yes it does, and that's the recordset Crystal currently sees. - but I only want the final recordset.
0
 
LVL 1

Expert Comment

by:Brighton
Comment Utility
Should your last statement be
SELECT * FROM #tempCommission
instead of
SELECT * FROM tempCommission
( I assume this is the data you want for crytal, not the data from spGetAllSurchargesByContract, right?)



Also, Can you post spGetAllSurchargesByContract?

I would think it would need to be modified to not return the data back to the screen but to variables or temp tables.
0
 

Author Comment

by:ChaunceyGardener
Comment Utility
Here's spGetAllSurchargesByContract:

CREATE PROCEDURE [dbo].[spGetAllSurchargesByContract]
          @iContractID INT = NULL,
          @iSurcharges VARCHAR(40) OUTPUT
 AS
DECLARE @strSurchargeHolder VARCHAR(3)
DECLARE get_all_surcharges CURSOR FOR
SELECT OptionType.DisplayCode
FROM OptionType JOIN OptionApplied on OptionType.OptionTypeID = OptionApplied.OptionTypeID
WHERE OptionApplied.ContractID = @iContractID

BEGIN

SELECT @iSurcharges = ''    

OPEN get_all_surcharges
FETCH NEXT FROM get_all_surcharges INTO @strSurchargeHolder

WHILE (@@fetch_status <> -1)
BEGIN

SELECT @iSurcharges = @iSurcharges + @strSurchargeHolder + ','

FETCH NEXT FROM get_all_surcharges INTO @strSurchargeHolder
END

CLOSE get_all_surcharges
DEALLOCATE get_all_surcharges
END
GO
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
In spGetAllSurchargesByContract change:
SELECT @iSurcharges = ...

To
Set @iSurcharges = ''  

Anthony
0
 

Author Comment

by:ChaunceyGardener
Comment Utility
Done, but that still didn't fix it... the first recordset appears to be the cursor:

>>
DECLARE surcharge_cursor CURSOR FOR
SELECT ContractID, Surcharge FROM tempCommission
<<

0
 
LVL 1

Accepted Solution

by:
Brighton earned 100 total points
Comment Utility
When you build the contents of @strSQL, you are creating inside this string a select statement that inserts information into a temporary table called #tempCommission.  Then you execute this string with EXEC(@strSQL) at which point the #tempCommission table has data ready for you to cursor through.  But your cursor is selecting from tempCommission instead of #tempCommission.
Should this be changed to the temp table? I also noticed that the last line of the first stored procedure also selects for the non-temp table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Brighton,

There is no mention of #TempCommission. So I can only assume that ChaunceyGardener is not using a temporary table in the sense that you and I know it (perhaps he should, but that is another issue).  So TempComission is correct.

Anthony
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
Comment Utility
In other words, in order for ChaunceyGardener to use a temporary table such as #TempCommission, there would have to be the following statement in the Stored Procedure:
Create Table #TempCommission
...

and there is not.

Anthony
0
 
LVL 1

Expert Comment

by:Brighton
Comment Utility
He does a SELECT INTO command to build the table.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>He does a SELECT INTO command to build the table. <<
You are absolutely right, I overlooked that.

Anthony
0
 

Expert Comment

by:CleanupPing
Comment Utility
ChaunceyGardener:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now