Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning only the final rowset

Posted on 2002-06-13
16
Medium Priority
?
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 400 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 400 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

719 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