Nested Cursor
-
I am sorry to ask this; I should be able to see it myself, but I just can't seem to find the loop.
Basically I am making new columns in a table (first cursor), then populating them (second cursor). Somehow, I am continuously looping through the second cursor using the first cursor's value, (which happens to be the year 1993).
I am putting in the entire stored proc. its kind of long. if you want, I can drop out sections (you will note I have commented out the dynamic SQL where the insert or updates take place).
Thanks,
Farrell
IF EXISTS( SELECT * FROM sysobjects WHERE id = object_id( N'spVendorVolumeNew')
AND OBJECTPROPERTY(id, N'IsProcedure')=1)
DROP PROC spVendorVolumeNew
GO
CREATE PROCEDURE spVendorVolumeNew
AS
SET NOCOUNT ON
DECLARE @sql varchar(8000),
@YearToUse int,
@VendorID varchar(35),
@PreviousVendorID varchar(35),
@YearTotal varchar(4),
@YearCount varchar(4),
@Total varchar(35),
@Count varchar(35),
@VendorName varchar(100),
@Address1 varchar(160),
@Address2 varchar(160),
@Address3 varchar(160),
@Address4 varchar(160),
@City varchar(50),
@State varchar(25),
@Zip varchar(25),
@Phone varchar(35),
@Fax varchar(35)
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[VendorVolumeNew]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE VendorVolumeNew
CREATE TABLE VendorVolumeNew(
VendorID varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
VendorName varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Address1 varchar(160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Address2 varchar(160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Address3 varchar(160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Address4 varchar(160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
City varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
State varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Zip varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Phone varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Fax varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
CREATE CLUSTERED INDEX cidx_VendorID ON VendorVolumeNew( VendorID ) ON [PRIMARY]
SELECT DISTINCT InvoiceYear AS 'Year'
INTO #VolumeYears
FROM AP
ORDER BY InvoiceYear
DECLARE VendorVolumeYear CURSOR FAST_FORWARD FOR
SELECT [Year] FROM #VolumeYears
OPEN VendorVolumeYear
next_Year:
FETCH NEXT FROM VendorVolumeYear INTO @YearToUse
IF @@FETCH_STATUS = 0
--WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'ALTER TABLE VendorVolumeNew ' + CHAR(13) +
'ADD Total' + CONVERT( varchar, @YearToUse) + ' money, ' + CHAR(13) + CHAR(9) +
' Count' + CONVERT( varchar, @YearToUse) + ' integer ' + CHAR(13)
EXEC( @sql)
--------------------------------------------------------------------------------------------
SELECT @sql = ' ',
@VendorID = ' ',
@PreviousVendorID = ' ',
@VendorName = NULL,
@Address1 = NULL,
@Address2 = NULL,
@Address3 = NULL,
@Address4 = NULL,
@City = NULL,
@State = NULL,
@Zip = NULL,
@Phone = NULL
DECLARE VendorVolume CURSOR FAST_FORWARD FOR
SELECT CONVERT( varchar(35), SUM( AP.InvoiceAmt)) AS 'Sum',
CONVERT( varchar(35), COUNT( AP.InvoiceAmt)) AS 'Count',
AP.VendorNum,
ISNULL( Vendor.VendorName, ' ') AS 'VendorName',
ISNULL( Vendor.Address1, ' ') AS 'Address1',
ISNULL( Vendor.Address2, ' ') AS 'Address2',
ISNULL( Vendor.Address3, ' ') AS 'Address3',
ISNULL( Vendor.Address4, ' ') AS 'Address4',
ISNULL( Vendor.City, ' ') AS 'City',
ISNULL( Vendor.State, ' ') AS 'State',
ISNULL( Vendor.Zip, ' ') AS 'Zip',
ISNULL( Vendor.PhoneAreaCode, ' ') + ' ' + ISNULL( Vendor.Phone, ' ') AS 'Phone',
ISNULL( Vendor.FaxAreaCode, ' ') + ' ' + ISNULL( Vendor.Fax, ' ') AS 'Fax'
FROM AP
INNER JOIN Vendor ON( AP.VendorNum = Vendor.VendorNum)
WHERE AP.InvoiceYear = @YearToUse
GROUP BY AP.VendorNum, Vendor.VendorName,
Vendor.Address1, Vendor.Address2, Vendor.Address3, Vendor.Address4,
Vendor.City, Vendor.State, Vendor.Zip, Vendor.PhoneAreaCode, Vendor.Phone,
Vendor.FaxAreaCode, Vendor.Fax
ORDER BY AP.VendorNum DESC
OPEN VendorVolume
next_VendorID:
FETCH NEXT FROM VendorVolume INTO @YearTotal, @YearCount, @VendorID,
@VendorName, @Address1, @Address2, @Address3, @Address4,
@City, @State, @Zip, @Phone, @Fax
IF @@FETCH_STATUS = 0
-- WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @YearToUse AS '@YearToUse', @YearTotal AS '@YearTotal', @YearCount AS '@YearCount', @VendorID AS '@VendorID', @VendorName AS '@VendorName'
/*
IF @VendorID IN( SELECT VendorID FROM VendorVolumeNew)
BEGIN
SELECT @sql =
'BEGIN TRANSACTION UpdateVendorVolume ' + CHAR(13) +
'UPDATE VendorVolumeNew SET ' + CHAR(13) +
'Total' + CONVERT( varchar, @YearToUse) + ' = CONVERT( money, ' + CONVERT( varchar, @Total) + ', ' + CHAR(13) +
'Count' + + CONVERT( varchar, @YearToUse) + ' = CONVERT( integer, ' + CONVERT( varchar, @Count) + CHAR(13) +
'WHERE VendorID = ' + CONVERT( varchar, @VendorID) + CHAR(13) +
'COMMIT TRANSACTION UpdateVendorVolume '
-- PRINT @sql
EXEC( @sql)
END
ELSE
BEGIN
SELECT @sql =
'BEGIN TRANSACTION InsertVendorVolume ' + CHAR(13) +
'INSERT INTO VendorVolumeNew( ' + CHAR(13) +
'Total' + CONVERT( varchar, @YearToUse) + ', Count' + CONVERT( varchar, @YearToUse) + ', ' + CHAR(13) +
'VendorID, VendorName, ' + CHAR(13) +
'Address1, Address2, Address3, Address4, ' + CHAR(13) +
'City, State, Zip, Phone, Fax )' + CHAR(13) +
'SELECT CONVERT( money, ' + CONVERT( varchar, @YearTotal) + '), ' +
' CONVERT( integer, ' + CONVERT( varchar, @YearCount) + '), ' + CHAR(13) +
CHAR(39) + CONVERT( varchar, @VendorID) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @VendorName) + CHAR(39) + ', ' + CHAR(13) +
CHAR(39) + CONVERT( varchar, @Address1) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Address2) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Address3) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Address4) + CHAR(39) + ', ' + CHAR(13) +
CHAR(39) + CONVERT( varchar, @City) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @State) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Zip) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Phone) + CHAR(39) + ', ' + CHAR(39) + CONVERT( varchar, @Fax) + CHAR(39) + CHAR(13) +
'COMMIT TRANSACTION InsertVendorVolume '
-- PRINT @sql
EXEC( @sql)
END
*/
GOTO next_VendorID
END
CLOSE VendorVolume
DEALLOCATE VendorVolume
--------------------------------------------------------------------------------------------
GOTO next_Year
END
CLOSE VendorVolumeYear
DEALLOCATE VendorVolumeYear
DROP TABLE #VolumeYears
I wasn't born stupid - I had to study.
-
Without delving into the problem direct....I'd seriously advise you to ditch the cursors. The performance gains from doing SET-based operations is massive. 99% of SQL can be written without cursors. cursors generally only need to be used when you need to compare one row of an input dataset to another already processed...or to compare with a local variable previously calculated (typically running totals...but even these can be done in a set-based manner)
Set-based SQL operations is like using a spoon to put sugar into a cup of tea. SQL Cursors are like using a tweezers to move sugar grains one at a time into the same cup.
w.r.t the problem, post some sample input data, 'matching' expected results and an english description of the problem you are trying to solve and you will benefit far more than just getting the cursor problem solved.
-
Thanks! And I agree. I rarely use Cursors (I make Identity Fields and generally While Loop through them), but on occassion, they are really useful for large datasets to walk through.
The solution is crummy. I have a IF @@FETCH_STATUS = 0. I changed that to IF @@FETCH_STATUS <> -1 and it worked perfectly! It took about 30 seconds.
But what a bummer that a Cursor cannot seem to recognize its EOF record.
Oil well. It works now.
Again, thanks for taking the time to look into this...
I wasn't born stupid - I had to study.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply