Solved

Returning only the final rowset

Posted on 2002-06-13
16
446 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
ID: 7075768
Change:
SELECT @strSQL = ...

To
Set @strSQL = ...

How are you using the rowsets?

Anthony
0
 

Author Comment

by:ChaunceyGardener
ID: 7075830
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
ID: 7075869
I am afraid I have never used Crystal Reports with Stored Procedures.  Perhaps someone else can jump in.

Anthony
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 1

Expert Comment

by:Brighton
ID: 7075974
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
ID: 7076524
>>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
ID: 7076561
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
ID: 7083629
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7083724
In spGetAllSurchargesByContract change:
SELECT @iSurcharges = ...

To
Set @iSurcharges = ''  

Anthony
0
 

Author Comment

by:ChaunceyGardener
ID: 7084223
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
ID: 7084264
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
ID: 7084310
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
ID: 7084315
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
ID: 7084328
He does a SELECT INTO command to build the table.
0
 
LVL 75

Expert Comment

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

Anthony
0
 

Expert Comment

by:CleanupPing
ID: 9280333
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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