Solved

Returning only the final rowset

Posted on 2002-06-13
16
444 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
triggered use of sp_send_dbmail failure 2 22
Optimizing a query 3 33
SQL Server stored proc 2 10
SQL - insert empty rows into output results 9 0
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

929 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

12 Experts available now in Live!

Get 1:1 Help Now