Hi - Trying to work w/cursors but this is the error I receive:
'Cursorfetch: The number of variables declared in the INTO list must match
that of selected columns.' What am I a doing wrong. Below is my code.
Here's the code:
DECLARE @.Address_Line1 VARCHAR (100)
EXECUTE spGet_Statement_Rate '8', '2005'
--spGet_Statement_Rate looks like this:
--DECLARE RATE_CURSOR CURSOR GLOBAL FOR
--SELECT Address_Line1, Rate_ID FROM COMPANY
OPEN RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
WHILE (@.@.FETCH_STATUS = 0) BEGIN
print @.Address_Line1--FROM RATE_CURSOR
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
END
--
CLOSE RATE_CURSOR
DEALLOCATE RATE_CURSORif u could post the ddl and sample data we could say whether u could do w/o
a cursor itself..
im not a fan of cursor based coding..
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong.
The message says it all. Your cursor select statement:
SELECT Address_Line1, Rate_ID FROM COMPANY
has 2 columns specified. However, your FETCH statements:
FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
have only 1 column specified.
You can correct the issue either by omitting Rate_ID from the select list or
adding a variable for Rate_ID to your FETCH statements.
You might also revisit your code to see if you can accomplish the task
without a cursor. Set-based processing is often much more efficient than
cursor processing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
> Hi - Trying to work w/cursors but this is the error I receive:
> 'Cursorfetch: The number of variables declared in the INTO list must match
> that of selected columns.' What am I a doing wrong. Below is my code.
> Here's the code:
> DECLARE @.Address_Line1 VARCHAR (100)
> EXECUTE spGet_Statement_Rate '8', '2005'
> --spGet_Statement_Rate looks like this:
> --DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> --SELECT Address_Line1, Rate_ID FROM COMPANY
> OPEN RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> WHILE (@.@.FETCH_STATUS = 0) BEGIN
> print @.Address_Line1--FROM RATE_CURSOR
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> END
> --
> CLOSE RATE_CURSOR
> DEALLOCATE RATE_CURSOR
>|||What do you mean by 'set based processing?' In a nutshell, I need to create
a result set of data. Then for each record returned, I need to execute a
bunch of stored procedures. I understand that cursors aren't the most
efficient, but this will run off hours and only once a month.
"Dan Guzman" wrote:
> The message says it all. Your cursor select statement:
> SELECT Address_Line1, Rate_ID FROM COMPANY
> has 2 columns specified. However, your FETCH statements:
> FETCH NEXT FROM RATE_CURSOR INTO @.Address_Line1
> have only 1 column specified.
> You can correct the issue either by omitting Rate_ID from the select list
or
> adding a variable for Rate_ID to your FETCH statements.
> You might also revisit your code to see if you can accomplish the task
> without a cursor. Set-based processing is often much more efficient than
> cursor processing.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:A75B44DC-DC6C-425D-B2BE-8B4F333F174C@.microsoft.com...
>
>|||> What do you mean by 'set based processing?'
Process the whole SET of rows at a time rather than one row at a time.
SQL Server is designed and optimized for set-based operations rather
than for cursor operations.
Aside from performance there are plenty of good reasons not to use
cursors. Set-based code is generally much more concise and easier to
develop, test, debug and maintain.
David Portas
SQL Server MVP
--|||> What do you mean by 'set based processing?' In a nutshell, I need to
> create
> a result set of data. Then for each record returned, I need to execute a
> bunch of stored procedures. I understand that cursors aren't the most
> efficient, but this will run off hours and only once a month.
You can't use set-based processing to execute procs for each row returned.
The point is that you can often use a set-based process instead of a looping
construct. Consider basic example:
DECLARE MyCursor CURSOR GLOBAL FOR
SELECT MyData FROM SomeTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @.MyData
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM MyCursor INTO @.MyData
INSERT INTO MyTable(MyData)
VALUES(@.MyData)
END
CLOSE MyCursor
DEALLOCATE MyCursor
The above can also be accomplished with the statement below and perform much
better:
INSERT INTO MyTable (MyData)
SELECT MyData FROM SomeTable
It's best to use inline SQL statements when possible. If you need to keep
code in different procs, you can pass data using temp table instead of a
cursor.
Hope this helps.
Dan Guzman
SQL Server MVP|||Okay, here's the cursor i was going to use (this would be in a stored
procedure):
DECLARE RATE_CURSOR CURSOR GLOBAL FOR
SELECT
S.Statement_ID,
S.Rate_ID,
C.Company_Name,
C.Address_Line1,
C.Address_Line2,
C.City,
C.State,
C.Zip,
C.Phone,
C.creditAllow,
R.Rate_Type,
R.Amount_Per_Transaction,
ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
R.Percentage_Per_Transaction,
ISNULL (Percentage_Per_Transaction_RCK, 0) AS
Percentage_Per_Transaction_RCK,
ISNULL (Percentage_Per_Transaction_ARC, 0) AS
Percentage_Per_Transaction_ARC,
R.Monthly_Fee,
R.Monthly_minimum,
R.Rate_Per_Batch,
R.Discount_VPT,
R.Discount_Monthly_Fee,
R.Discount_RPB,
R.statement_fee,
R.service_fee,
R.ACHgateway_fee,
R.Reject_company_fee,
R.Reject_customer_fee,
R.ACHRefund_Fee,
R.ChargeBack_fee,
R.BadDE_fee,
R.NOC,
R.reserve_percentage,
R.GrossNet,
R.tranFeeCredit,
R.discountFeeCredit,
R.statementFeeCredit,
R.monthlyminCredit,
R.creditOutReturnFee,
RCK,
ARC,
C.RCKRebate,
C.CCDPPD_rebate_amount,
C.ARC_rebate_amount,
C.isRCK_rebate,
C.isARC_rebate,
C.isCCDPPD_rebate,
COMPANY_ID
FROM ST S, RT R, CO C
WHERE S.Rate_ID = R.Rate_ID and
S.Account_ID = C.Company_ID and
S.statement_Month = @.MONTH and
S.statement_Year = @.YEAR AND
C.ACCOUNT_STATUS = 1
Now, for earch record returned above, I will need to execute about 6 stored
procedures, passing in vaious parameters. I'm unfamilar as to how to loop
through a result set (as opposed to using a cursor) to achieve the same, so
if that's a better option, a code example would be appreciated.|||If you have to iterate through a result set for each row returned then
a cursor may well be the best way to do it. What you should generally
try to avoid is iteration in ANY form - other methods of doing so are
just a cursor in disguise. As you have a legacy proc designed to work
only with single rows you will have to decide whether it's worth a
re-write or if you can live with the cursor solution. The better
alternative in most cases is to avoid designing procs that impose any
single row limitations on your SQL code.
David Portas
SQL Server MVP
--|||To add on to David's response, I'd like to emphasize his point that the real
issue is your procs that operate on one record at a time. Ideally, you can
ditch both the cursor and the single-row procs and instead use set-based
processing. For example:
Instead of:
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM RATE_CURSOR INTO <variable list here>
EXEC MyInsertProc <variable list here>
END
Try:
INSERT INTO MyTable(<column list here> )
SELECT ...
I know that rewriting existing code can be painful and might not be worth
the effort in this case if your cursor performance is acceptable. However,
you should at least be familiar with set-based techniques so you can employ
them going forward as appropriate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:D1E5E64E-3AA9-40C6-975D-C7A079AD20DF@.microsoft.com...
> Okay, here's the cursor i was going to use (this would be in a stored
> procedure):
> DECLARE RATE_CURSOR CURSOR GLOBAL FOR
> SELECT
> S.Statement_ID,
> S.Rate_ID,
> C.Company_Name,
> C.Address_Line1,
> C.Address_Line2,
> C.City,
> C.State,
> C.Zip,
> C.Phone,
> C.creditAllow,
> R.Rate_Type,
> R.Amount_Per_Transaction,
> ISNULL (Amount_Per_Transaction_RCK, 0) AS Amount_Per_Transaction_RCK,
> ISNULL (Amount_Per_Transaction_ARC, 0) AS Amount_Per_Transaction_ARC,
> R.Percentage_Per_Transaction,
> ISNULL (Percentage_Per_Transaction_RCK, 0) AS
> Percentage_Per_Transaction_RCK,
> ISNULL (Percentage_Per_Transaction_ARC, 0) AS
> Percentage_Per_Transaction_ARC,
> R.Monthly_Fee,
> R.Monthly_minimum,
> R.Rate_Per_Batch,
> R.Discount_VPT,
> R.Discount_Monthly_Fee,
> R.Discount_RPB,
> R.statement_fee,
> R.service_fee,
> R.ACHgateway_fee,
> R.Reject_company_fee,
> R.Reject_customer_fee,
> R.ACHRefund_Fee,
> R.ChargeBack_fee,
> R.BadDE_fee,
> R.NOC,
> R.reserve_percentage,
> R.GrossNet,
> R.tranFeeCredit,
> R.discountFeeCredit,
> R.statementFeeCredit,
> R.monthlyminCredit,
> R.creditOutReturnFee,
> RCK,
> ARC,
> C.RCKRebate,
> C.CCDPPD_rebate_amount,
> C.ARC_rebate_amount,
> C.isRCK_rebate,
> C.isARC_rebate,
> C.isCCDPPD_rebate,
> COMPANY_ID
> FROM ST S, RT R, CO C
> WHERE S.Rate_ID = R.Rate_ID and
> S.Account_ID = C.Company_ID and
> S.statement_Month = @.MONTH and
> S.statement_Year = @.YEAR AND
> C.ACCOUNT_STATUS = 1
> Now, for earch record returned above, I will need to execute about 6
> stored
> procedures, passing in vaious parameters. I'm unfamilar as to how to loop
> through a result set (as opposed to using a cursor) to achieve the same,
> so
> if that's a better option, a code example would be appreciated.
No comments:
Post a Comment