Having Cursor Blues - Help Need

[Smeat]

Hi All

I'm having a problem with an SP im using to produce a report.

In summary, I'm using a cursor which is working but when I execute the SP I get a resultset for every loop in the cursor.

Any ideas how I get it to just return the results of the last SQL statement?

Here's the code I use to executive my SP:

USE [TestDB]
GO


DECLARE	@return_value int

EXEC	@return_value = [dbo].[PJ_TEMP]
		@VendorId = 3,
		@StartDate = 'August 1 2008 00:00:00',
		@EndDate = 'August 30 2008 23:59:59'


GO

These are the resules I'm getting when I executive

Id  DateManufacturerWarrantyExpires  StartDate, ExpiryDate
53	2008-06-20 00:00:00.000	2008-06-20 00:00:00.000	2009-06-15 00:00:00.000

60	2008-06-13 00:00:00.000	2008-06-19 00:00:00.000	2009-06-19 00:00:00.000

61	2008-06-07 00:00:00.000	2008-06-20 00:00:00.000	2009-06-20 00:00:00.000

etc, etc, etc...




The following are the results I actually want:

DealerName TotalRiskWarranties TotalUnder12MonthsOld TotalCoveredLessThan19Days TotalQualifyingWarranties

55	NULL	NULL	NULL	NULL
aserfgfasdf	NULL	NULL	NULL	NULL
Big Test Motors	NULL	NULL	NULL	NULL
bobobobob	NULL	NULL	NULL	NULL
New Test Dealer	NULL	NULL	NULL	NULL
Test Solvenian Dealer	11	10	0	1
Testy Motors	NULL	NULL	NULL	NULL
xxxxxxx	NULL	NULL	NULL	NULL

Here's the stored procedure:

CREATE PROCEDURE [dbo].[PJ_TEMP]

	@VendorId int,
	@StartDate datetime,
	@EndDate datetime

AS
BEGIN
	-- Requirement is for number of warranties at risk (active) this month,
	-- Number of warranties where vehicle id under 12 months old at period end (@EndDate)
	-- Number of warranties where vehicle is covered for less than 19 days in the period. NB: A vehicle is only covered once the manufacturer warranty runs out.
	SET NOCOUNT ON;

	-- Temp table to hold stats whlst being built.
    DECLARE @InvoiceReportStats TABLE(DealerID int, DealerName nvarchar(50), TotalRiskWarranties int, TotalUnder12MonthsOld int, TotalCoveredLessThan19Days int)

	-- Get a list of dealers.
	INSERT INTO @InvoiceReportStats (DealerID, DealerName)
	SELECT [ID], DealerName FROM Dealer WHERE VendorId = @VendorId AND IsVirtual = 0


	-- Use cursor to iterate through each dealer and update it's stats
	DECLARE @dealerId int
	DECLARE dealer_cursor CURSOR FOR
	SELECT DealerID FROM @InvoiceReportStats
	OPEN dealer_cursor

	FETCH NEXT FROM dealer_cursor INTO @dealerId

	WHILE @@FETCH_STATUS = 0
		BEGIN

			-- Add TotalRiskWarranties - Total active warranties within date range --
			UPDATE @InvoiceReportStats
			SET TotalRiskWarranties = (
				SELECT COUNT(*) 
				FROM Warranty 
				WHERE DealerId = @dealerId 
				AND StartDate <= @StartDate
				AND ExpiryDate >= @StartDate
			)
			WHERE DealerID = @dealerId
	

			-- Add TotalUnder12MonthsOld - Total warranties where vehicle is under 12 months old at @EndDate --
			DECLARE @FirstRegDateThreshold datetime
			SELECT @FirstRegDateThreshold = DateAdd(year, -1, @EndDate)

			UPDATE @InvoiceReportStats
			SET TotalUnder12MonthsOld = (
				SELECT COUNT(*) 
				FROM Warranty 
				WHERE DealerId = @dealerId 
				AND StartDate <= @StartDate
				AND ExpiryDate >= @StartDate
				AND DateFirstRegistered > @FirstRegDateThreshold
			)
			WHERE DealerID = @dealerId

			-- Add TotalCoveredLessThan19Days - Total active warranties where vehicle is covered for less than 19 days in the period --
			-- NB: A vehicle is only covered once the manufacturer warranty expires.

			-- Declare a new cursor to iterate through each warranty for the given dealer --
				DECLARE @warrantyId int
				DECLARE @manufacturerWarrantyExpiryDate datetime
				DECLARE @warrantyStartDate datetime
				DECLARE @warrantyExpiryDate datetime

				DECLARE warranty_cursor CURSOR FOR SELECT ID, DateManufacturerWarrantyExpires, StartDate, ExpiryDate FROM Warranty 
					WHERE DealerId = @dealerId
					AND StartDate <= @StartDate
					AND ExpiryDate >= @StartDate
				OPEN warranty_cursor
				
				DECLARE @TotalUnder19Days int
				SELECT @TotalUnder19Days = 0
				FETCH NEXT FROM warranty_cursor INTO @warrantyId, @manufacturerWarrantyExpiryDate, @warrantyStartDate, @warrantyExpiryDate
				WHILE @@FETCH_STATUS = 0
					BEGIN

						-- How many days covered in this period
						DECLARE @DaysInPeriod int
						SELECT @DaysInPeriod = DATEDIFF(day, @StartDate, @EndDate)

						-- Check if the manufacturers warranty expires within the date range
						IF @manufacturerWarrantyExpiryDate BETWEEN @StartDate AND @EndDate
							BEGIN
								
								IF DATEDIFF(day, DATEADD(day, 1, @manufacturerWarrantyExpiryDate), @EndDate) < 19
									BEGIN
										SELECT @TotalUnder19Days = @TotalUnder19Days + 1
									END
							END
						ELSE	
							BEGIN
								-- Check if warranty start date is after the date range start (@StartDate)
								IF @warrantyStartDate > @StartDate
									BEGIN

										IF DATEDIFF(day, @warrantyStartDate, @EndDate) < 19
											BEGIN
												SELECT @TotalUnder19Days = @TotalUnder19Days + 1
											END
									END
								ELSE						
									-- Check if warranty expires within the date range
									IF @warrantyExpiryDate BETWEEN @StartDate AND @EndDate
										BEGIN
										IF DATEDIFF(day, @StartDate, @warrantyExpiryDate) < 19
											BEGIN
												SELECT @TotalUnder19Days = @TotalUnder19Days + 1
											END
										END
							END

						FETCH NEXT FROM warranty_cursor
					END
				CLOSE warranty_cursor
				DEALLOCATE warranty_cursor

				UPDATE @InvoiceReportStats
				SET TotalCoveredLessThan19Days = @TotalUnder19Days
				WHERE DealerID = @dealerId

			FETCH NEXT FROM dealer_cursor
		END
	CLOSE dealer_cursor
	DEALLOCATE dealer_cursor

	-- Return results.
	SELECT DealerName, TotalRiskWarranties, TotalUnder12MonthsOld, TotalCoveredLessThan19Days, TotalRiskWarranties - (TotalUnder12MonthsOld + TotalCoveredLessThan19Days) AS TotalQualifyingWarranties
	FROM @InvoiceReportStats
	ORDER BY DealerName

END

Any ideas how I get it to just return the results of the last SQL statement?

	SELECT DealerName, TotalRiskWarranties, TotalUnder12MonthsOld, TotalCoveredLessThan19Days, TotalRiskWarranties - (TotalUnder12MonthsOld + TotalCoveredLessThan19Days) AS TotalQualifyingWarranties
	FROM @InvoiceReportStats
	ORDER BY DealerName

TIA

Smeat

 

[gmmastros]

Ouch. Cursor within a cursor?

My gut tells me that you don't need any cursors at all for this query. There are others that regularly read questions in this forum that may be able to help you with the cursor(s). However, if you are willing to post some sample data and expected results, I am willing to help you write a query that doesn't use any cursors. The benefit here will be easier to understand set based code that will probably function many times faster.

Let me know what you think.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

I'm with George I see nothing in there that requires a cursor at all. Cursors are extremely bad for performance and in general should NOT be used.

"NOTHING is more important in a database than integrity." ESquared

 

[Smeat]

Thanks both for your comments, thanks also for the offer of help George.

Based on your comments I decided to do this within a business object which turned out to be a lot easier in the end.

Smeat