Cursors only fetching about 200 records?

I have a cursor that is supposed to fetch all the records from a temporary
table, #temp, one at a time, do some manipulation on the data, insert the
results into another temporary table, #detailedTransRpt, then delete the
record from #temp. All of the records from #temp should be deleted before
@.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aroun
d
the time 200 records have been fetched, thereby leaving some data in #temp.
I have tried different data sets, and have compared the records on which the
fetch fails, but these records have nothing in common and I can't seem to
figure out why the fetch is failing. It seems to have more to do with the
number of records, than anything else. Has anyone come across a similar
problem? Any advice?Keith wrote:
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always aro
und
> the time 200 records have been fetched, thereby leaving some data in #temp
.
> I have tried different data sets, and have compared the records on which t
he
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?
Without seeing the code it's anyone's guess. Based on your description
it's probably unlikely that you'll need a cursor at all. There are
usually better solutions so I suggest you post DDL, some sample data
and show what result you want if you need some help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why are you deleting the rows within your cursor? I think you can loop
through all the rows and delete them only when you are done with the entire
set of data. I don't know if this has any effect on the cursor or not, but
it sounds a little odd and might be worth checking. Basically, I am
wondering if updating the temp table that you are reading from might cause
strange behavior, although I would expect an error rather than lost rows if
this were the case.
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
around
> the time 200 records have been fetched, thereby leaving some data in
#temp.
> I have tried different data sets, and have compared the records on which
the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||by any chance, is rowcount set to 200?|||I'm posting all the variable declarations, and what I set the stored
procedure parameters to, for debugging purposes, as well as the two table
CREATE statements, and the single cursor that I know is failing. There is
too much code in the entire SP for me to paste here. A function splits the
string from @.strbankAccID into bank account IDs, and then the transactions
from a given bank account ID are put into #temp. The transactions from #tem
p
are manipulated then put into #detailedTransRpt, and then deleted from #temp
.
However, the cursor @.@.fetch_status = -1 before all the transactions are
removed from #temp, and the transactions end up being mixed up between the
bank accounts on the report that this produces. Why was it done this way,
you may ask? Because some other developer (who is long gone) did it this wa
y
and I am now responsible for fixing the report. Can you feel the
frustration? :) Thanks!
DECLARE @.Lodge_ID VARCHAR(9)
DECLARE @.begin VARCHAR(25)
DECLARE @.end VARCHAR(25)
DECLARE @.strbankAccID VARCHAR (8000)
DECLARE @.transTypeCD CHAR(3)
DECLARE @.transVersion CHAR(3)
DECLARE @.accountChosen VARCHAR(3)
DECLARE @.totalRecord INT
SET @.LODGE_ID = 'D60'
set @.begin = '12/21/2005'
set @.end = '12/31/2005'
set @.strbankAccID = '583,1373,1374,1380,1381,1384,1385,1397'
--D60
set @.transTypeCD = 'All'
set @.transVersion = 'All'
set @.accountChosen = 'All'
SET NOCOUNT ON
DECLARE @.beginDate DATETIME
DECLARE @.endDate DATETIME
DECLARE @.acctIDstring VARCHAR(1000)
DECLARE @.pos INT
DECLARE @.str1 VARCHAR (8)
DECLARE @.length INT
DECLARE @.strTransNum VARCHAR(50)
DECLARE @.transID INT
DECLARE @.bankAcctID BIGINT
DECLARE @.acctNameLabel VARCHAR(50)
DECLARE @.transTypeLabel VARCHAR(50)
DECLARE @.transVersionLabel VARCHAR(100)
DECLARE @.bankType AS CHAR(1)
DECLARE @.charBankType AS CHAR(1)
DECLARE @.flag BIT
DECLARE @.DepositTransID INT
DECLARE @.charDepositStatusCD CHAR(2)
DECLARE @.bankID INT
CREATE TABLE #temp (
tempDepTransID INT,
DepositID INT,
TransNumber VARCHAR(50),
TransAmt MONEY,
SplitInd CHAR(1) ,
CategoryID INT,
SubCategoryID INT,
TransStatusCD CHAR(1),
TransVersionCD CHAR(2),
PaymentType CHAR(2),
TransDate DATETIME,
TransType CHAR(2),
TransDesc VARCHAR(150),
class_cd varchar(50)
)
CREATE TABLE #detailedTransRpt (
BankAcctID BIGINT,
depTransID INT,
Header VARCHAR(255),
AcctNum VARCHAR(30),
BankName VARCHAR(50),
AcctType VARCHAR(50),
Status VARCHAR(20),
TransDate DATETIME,
Number VARCHAR (255),-- receipt # ... trans number in a string with ','
TransDesc VARCHAR(150),-- receipt number ...trans number in a string with
','
PaymentType VARCHAR(20),-- deposit for section 3
Receipt MONEY,
Reconciled VARCHAR(20),
Disbursement MONEY,
TransVersion VARCHAR(100),
Category VARCHAR (50),
SubCategory VARCHAR (50),
class_cd varchar(50)
)
declare @.classCd varchar(50)
declare @.depTransID INT
declare @.tempDepTransID INT
DECLARE @.bankName AS VARCHAR (50)
DECLARE @.acctNo AS VARCHAR(30)
DECLARE @.acctName AS VARCHAR(50)
DECLARE @.charStatus AS CHAR(1)
DECLARE @.strStatus AS VARCHAR(20)
DECLARE @.charAcctType AS CHAR(1)
DECLARE @.strAcctType AS VARCHAR(50)
DECLARE @.header AS VARCHAR(255)
DECLARE @.TempTransID INT
DECLARE @.depositID INT
DECLARE @.transactionNumber VARCHAR(50)
DECLARE @.transactionDesc VARCHAR(150)
DECLARE @.numberofreceipt INT
DECLARE @.transNum VARCHAR(50)
DECLARE @.transAmt MONEY
DECLARE @.transTotal MONEY
DECLARE @.Category INT
DECLARE @.subCategory INT
DECLARE @.categoryDesc VARCHAR(100)
DECLARE @.subCategoryDesc VARCHAR(100)
DECLARE @.SplitInd CHAR(1)
DECLARE @.charTransStatusCD CHAR(1)
DECLARE @.charTransVersionCD CHAR(1)
DECLARE @.charPaymentTypeCD CHAR(2)
DECLARE @.strPaymentType VARCHAR(20)
DECLARE @.strTransStatus VARCHAR(20)
DECLARE @.strTransVersion VARCHAR(100)
DECLARE @.transDate DATETIME
DECLARE @.TransType CHAR(2)
DECLARE @.TransDesc VARCHAR(150)
DECLARE @.DepositMemo VARCHAR(50)
DECLARE @.DepositNumber VARCHAR(20)
DECLARE @.intDeposit INT
SET @.transactionNumber = ''
DECLARE curTransNum CURSOR FOR
SELECT * FROM #temp WHERE (DepositID IS NULL) AND TransType <> 'TR'
OPEN curTransNum
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType,@.TransDesc,@.classC
D
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.categoryDesc = NULL
SET @.subCategoryDesc = NULL
set @.classCDdesc = null
IF @.splitInd ='Y'
BEGIN
SET @.categoryDesc = 'SPLIT'
SET @.subCategoryDesc = 'SPLIT'
set @.classCDdesc = 'SPLIT'
END
ELSE
BEGIN
SELECT @.categoryDesc = Category_Description FROM Category WHERE
Category_ID = @.Category
SELECT @.subCategoryDesc = Sub_Category_description FROM Sub_Category
WHERE Sub_Category_id = @.subCategory
IF @.TransType = 'RT'
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_SCHEDULE_CAT_CD' AND Code LIKE @.classCD
else
select @.classCDdesc = description from lookup where Category LIKE
'CLASS_FUNCTIONAL_CAT_CD' AND Code LIKE @.classCD
END
SELECT @.strPaymentType = [Description]
FROM Lookup
WHERE Category LIKE 'PAYMENT_TYPE_CD' AND Code LIKE @.charPaymentTypeCD
IF @.charTransStatusCD = 'U'
SET @.strTransStatus = ''
ELSE
SET @.strTransStatus = @.charTransStatusCD
IF @.charTransVersionCD = '0'
SET @.strTransVersion = ''
ELSE IF @.charTransVersionCD = '1'
SET @.strTransVersion = 'Edited'
ELSE IF @.charTransVersionCD = '2'
SET @.strTransVersion = 'Adjusted'
ELSE IF @.charTransVersionCD = '3' OR @.charTransVersionCD = '5'
SET @.strTransVersion = 'Voided'
ELSE IF @.charTransVersionCD = '4' OR @.charTransVersionCD = '6'
SET @.strTransVersion = 'Unvoided'
IF @.TransType = 'RT'--undeposited receipts
BEGIN
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,'Receipt #'+@.transNum,
@.TransDesc,@.strPaymentType,--CAST(@.transAmt AS VARCHAR),
@.transAmt,@.strTransStatus,NULL,@.strTrans
Version,@.categoryDesc,@.subCategoryDe
sc,@.classCDdesc)
--DELETE #temp WHERE @.TransType = 'RT'
DELETE #temp WHERE tempDepTransID = @.depTransID
END
ELSE --this is disbursement
BEGIN
IF @.charPaymentTypeCD = 'EF'
SET @.transactionNumber = 'EFT#' + @.transNum
ELSE IF @.charPaymentTypeCD = 'CH'
SET @.transactionNumber = 'Chk#' + @.transNum -- 7/18/2005
INSERT INTO #detailedTransRpt
(bankAcctID,depTransID,Header,AcctNum,Ba
nkName,
AcctType,Status,TransDate,Number,
TransDesc,PaymentType,Receipt,Reconciled
,Disbursement,TransVersion,Category,
SubCategory,class_cd)
VALUES
(@.bankAcctID,@.depTransID,@.header,@.acctNo
,@.bankName,@.strAcctType,@.strStatus,@.
transDate,@.transactionNumber,
@.TransDesc,@.strPaymentType,NULL,@.strTran
sStatus,@.transAmt,
@.strTransVersion,@.categoryDesc,@.subCateg
oryDesc,@.classCDdesc)
DELETE #temp WHERE tempDepTransID = @.depTransID --@.TransType = 'DI'
END
FETCH NEXT FROM curTransNum INTO
@.depTransID,@.DepositID,@.transNum,@.transA
mt,@.splitInd,@.Category,
@.subCategory,@.charTransStatusCD,@.charTra
nsVersionCD,@.charPaymentTypeCD,
@.transDate,@.TransType, @.TransDesc,@.classCD
END
CLOSE curTransNum
DEALLOCATE curTransNum
"David Portas" wrote:

> Keith wrote:
> Without seeing the code it's anyone's guess. Based on your description
> it's probably unlikely that you'll need a cursor at all. There are
> usually better solutions so I suggest you post DDL, some sample data
> and show what result you want if you need some help.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm a fairly new developer, so I'm not familiar with the ROWCOUNT setting.
How and where would I check and set this? Incidentally, I am working with
SQL Server 2000.
"Alexander Kuznetsov" wrote:

> by any chance, is rowcount set to 200?
>|||Watch where you are deleting records in the cursor...delete after the fetch
next..
We don't use cursors here, but table variables, with an indentity
column..and then use a while loop.
Instead of deleting on the fly, just set a flag. Then delete all at once at
the end, based on the flag.
Jeff
"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
>I have a cursor that is supposed to fetch all the records from a temporary
> table, #temp, one at a time, do some manipulation on the data, insert the
> results into another temporary table, #detailedTransRpt, then delete the
> record from #temp. All of the records from #temp should be deleted before
> @.@.fetch_status = -1; however, it seems that @.@.fetch_status = -1 always
> around
> the time 200 records have been fetched, thereby leaving some data in
> #temp.
> I have tried different data sets, and have compared the records on which
> the
> fetch fails, but these records have nothing in common and I can't seem to
> figure out why the fetch is failing. It seems to have more to do with the
> number of records, than anything else. Has anyone come across a similar
> problem? Any advice?|||The short answer is that it is being done this way because another developer
did it this way, and I am trying not to rewrite the stored procedure and
possibly create more bugs in the process of fixing this one. All the record
s
in #temp are transactions for a single bank account, so the records are
deleted from here as they are inserted into #detailedTransRpt, because later
a different cursor (this SP contains a billion nested cursors!) changes the
bank account that will appear in a different section of the report. If ther
e
are records still left in #temp, then all the bank account transactions get
mixed up. Like I said, this just seems to happen when there are more than
200 transactions in the report, so I was wondering if there was some
significance between the number 200 and something to do with cursors.
"Jim Underwood" wrote:

> Why are you deleting the rows within your cursor? I think you can loop
> through all the rows and delete them only when you are done with the entir
e
> set of data. I don't know if this has any effect on the cursor or not, bu
t
> it sounds a little odd and might be worth checking. Basically, I am
> wondering if updating the temp table that you are reading from might cause
> strange behavior, although I would expect an error rather than lost rows i
f
> this were the case.
> "Keith" <Keith@.discussions.microsoft.com> wrote in message
> news:44450145-CBC9-4AC0-B08C-DAF3F992C4B9@.microsoft.com...
> around
> #temp.
> the
>
>|||Now that would be an incredibly simple solution...
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1146590107.762359.308020@.y43g2000cwc.googlegroups.com...
> by any chance, is rowcount set to 200?
>|||"Keith" <Keith@.discussions.microsoft.com> wrote in message
news:F26CC2DD-B066-48E7-BB41-0A6E0F88EB97@.microsoft.com...
> I'm posting all the variable declarations, and what I set the stored
> procedure parameters to, for debugging purposes, as well as the two table
> CREATE statements, and the single cursor that I know is failing. There is
> too much code in the entire SP for me to paste here. A function splits
> the
> string from @.strbankAccID into bank account IDs, and then the
> transactions
> from a given bank account ID are put into #temp. The transactions from
> #temp
> are manipulated then put into #detailedTransRpt, and then deleted from
> #temp.
> However, the cursor @.@.fetch_status = -1 before all the transactions are
> removed from #temp, and the transactions end up being mixed up between the
> bank accounts on the report that this produces. Why was it done this way,
> you may ask? Because some other developer (who is long gone) did it this
> way
> and I am now responsible for fixing the report. Can you feel the
> frustration? :) Thanks!
I would replace the whole cursor with two INSERTs and a DELETE. Here's my
effort at the first INSERT (untested and therefore not necessarily complete
or totally accurate).
INSERT INTO #detailedTransRpt
(bankAcctID, depTransID, Header, AcctNum, BankName, AcctType, Status,
TransDate, Number, TransDesc, PaymentType, Receipt, Reconciled,
Disbursement, TransVersion, Category, SubCategory, class_cd)
SELECT NULL, T.tempDepTransID, NULL, NULL, NULL, NULL, NULL,
T.TransDate, 'Receipt #'+T.TransNumber, T.TransDesc, L.Description,
NULL,
CASE WHEN TransStatusCD = 'U' THEN '' ELSE TransStatusCD END,NULL,
CASE TransVersionCD
WHEN '0' THEN ''
WHEN '1' THEN 'Edited'
WHEN '2' THEN 'Adjusted'
WHEN '3' THEN 'Voided'
WHEN '4' THEN 'Unvoided'
WHEN '5' THEN 'Voided'
WHEN '6' THEN 'Unvoided'
END, C.Category_description, S.Sub_Category_description, M.Description
FROM #Temp AS T
JOIN Lookup AS L
ON L.Category LIKE 'PAYMENT_TYPE_CD'
AND L.Code LIKE T.PaymentType
JOIN Lookup AS M
ON M.Category LIKE 'CLASS_SCHEDULE_CAT_CD'
AND L.Code LIKE T.PaymentType
JOIN Category AS C
ON T.CategoryID = C.Category_id
JOIN Sub_Category AS S
ON T.SubCategoryID = S.Sub_Category_id
WHERE TransType = 'RT' ;
DELETE #temp WHERE TransType = 'RT' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cursors within stored procedures

Hi,
I've got a stored procedure that makes use of several cursors to
collate data and then place it into a temp table, which is then
eventually returned to the calling code. The problem is that when I
execute the stored proc, opening the cursors seems to be returning a
recordset, when all I need is the temp table. Code is below, any help
much appreciated!
CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
AS
DECLARE @.Measure varchar(50)
DECLARE @.NumberOfCEFSentToLSCNew int
DECLARE @.NumberOfCEFSentToLSCRepeat int
DECLARE @.NumberOfFASentToLSC int
DECLARE @.NumberOfLAEnteredAccredited int
DECLARE @.NumberOfLAEnteredBespoke int
DECLARE @.AccountManager varchar(50)
DECLARE @.CurrentCount int
/* Create a temporary table */
CREATE TABLE #CalculatedValues
(
Measure varchar(50),
AccountManager varchar(50),
NumberOfCEFSentToLSCNew int,
NumberOfCEFSentToLSCRepeat int,
NumberOfFASentToLSC int,
NumberOfLAEnteredAccredited int,
NumberOfLAEnteredBespoke int
)
/* get measures into a cursor */
DECLARE MeasureCursor CURSOR FOR
SELECT new_name
FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
OPEN MeasureCursor
/* start cursor loop */
FETCH NEXT FROM MeasureCursor INTO @.Measure
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* NumberOfCEFSentToLSCNew */
INSERT INTO #CalculatedValues
SELECT @.Measure,
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
0, 0, 0, 0
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
/* NumberOfCEFSentToLSCRepeat */
DECLARE TempCursor1 CURSOR FOR
SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
a1a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 1
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor1
FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
END
FETCH NEXT FROM TempCursor1
END
CLOSE TempCursor1
DEALLOCATE TempCursor1
/*NumberOfFASentToLSC*/
DECLARE TempCursor2 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor2
FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
END
FETCH NEXT FROM TempCursor2
END
CLOSE TempCursor2
DEALLOCATE TempCursor2
/* NumberOfLAEnteredAccredited */
DECLARE TempCursor3 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
AS VARCHAR(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor3
FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
END
FETCH NEXT FROM TempCursor3
END
CLOSE TempCursor3
DEALLOCATE TempCursor3
/* NumberOfLAEnteredBespoke */
DECLARE TempCursor4 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
as varchar(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor4
FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
END
FETCH NEXT FROM TempCursor4
END
CLOSE TempCursor4
DEALLOCATE TempCursor4
FETCH NEXT FROM MeasureCursor INTO @.Measure
END
/* end cursor loop */
CLOSE MeasureCursor
DEALLOCATE MeasureCursor
/* return values back */
SELECT * FROM #CalculatedValues
GODo you mean when the recordset are returned to the client. Would SET NOCOUNT
ON solve your problem?
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
<peteandrews@.hotmail.com> wrote in message
news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
> Hi,
> I've got a stored procedure that makes use of several cursors to
> collate data and then place it into a temp table, which is then
> eventually returned to the calling code. The problem is that when I
> execute the stored proc, opening the cursors seems to be returning a
> recordset, when all I need is the temp table. Code is below, any help
> much appreciated!
> CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
> AS
> DECLARE @.Measure varchar(50)
> DECLARE @.NumberOfCEFSentToLSCNew int
> DECLARE @.NumberOfCEFSentToLSCRepeat int
> DECLARE @.NumberOfFASentToLSC int
> DECLARE @.NumberOfLAEnteredAccredited int
> DECLARE @.NumberOfLAEnteredBespoke int
> DECLARE @.AccountManager varchar(50)
> DECLARE @.CurrentCount int
> /* Create a temporary table */
> CREATE TABLE #CalculatedValues
> (
> Measure varchar(50),
> AccountManager varchar(50),
> NumberOfCEFSentToLSCNew int,
> NumberOfCEFSentToLSCRepeat int,
> NumberOfFASentToLSC int,
> NumberOfLAEnteredAccredited int,
> NumberOfLAEnteredBespoke int
> )
> /* get measures into a cursor */
> DECLARE MeasureCursor CURSOR FOR
> SELECT new_name
> FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
> OPEN MeasureCursor
> /* start cursor loop */
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* NumberOfCEFSentToLSCNew */
> INSERT INTO #CalculatedValues
> SELECT @.Measure,
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
> 0, 0, 0, 0
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> /* NumberOfCEFSentToLSCRepeat */
> DECLARE TempCursor1 CURSOR FOR
> SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
> a1a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 1
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor1
> FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
> END
> FETCH NEXT FROM TempCursor1
> END
> CLOSE TempCursor1
> DEALLOCATE TempCursor1
> /*NumberOfFASentToLSC*/
> DECLARE TempCursor2 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
> 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor2
> FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
> END
> FETCH NEXT FROM TempCursor2
> END
> CLOSE TempCursor2
> DEALLOCATE TempCursor2
> /* NumberOfLAEnteredAccredited */
> DECLARE TempCursor3 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> AS VARCHAR(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor3
> FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
> END
> FETCH NEXT FROM TempCursor3
> END
> CLOSE TempCursor3
> DEALLOCATE TempCursor3
> /* NumberOfLAEnteredBespoke */
> DECLARE TempCursor4 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> as varchar(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> OPEN TempCursor4
> FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
> END
> FETCH NEXT FROM TempCursor4
> END
> CLOSE TempCursor4
> DEALLOCATE TempCursor4
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> END
> /* end cursor loop */
> CLOSE MeasureCursor
> DEALLOCATE MeasureCursor
> /* return values back */
> SELECT * FROM #CalculatedValues
> GO
>|||I found at least on other problem (apart from the possible NOCOUNT problem):
FETCH NEXT FROM TempCursor1
There might be more than one FETCH without specifying INTO @.variable(s). Suc
h will return a
resultset instead of adding the value for each column into the variables. I
didn't want to post this
as I don't have the time to go through the logic and propose a set based sol
ution (which is always
preferred to look at). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dr31d7$p6h$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Do you mean when the recordset are returned to the client. Would SET NOCOU
NT
> ON solve your problem?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> <peteandrews@.hotmail.com> wrote in message
> news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
>|||Thanks for the replies, SET NOCOUNT didn't make any difference but
Tibor's suggestion sorted it - I'd left out the INTO clause from the
FETCH statements, I fixed that and it works great, so thanks very much!
Tibor - out of interest (and a desire to get away from using cursors),
I'd be really grateful if you could point me in the direction of where
I can read up on set based solutions?|||A set based solution is more a way of attacking the problem. You can for ins
tance Google these
newsgroup archives and you should find some posts where a single (or a few)
UPDATE, SELECT, etc have
been suggested instead of a proposed or current cursor solution. The idea is
to manage to do the
whole operation in one UPDATE statement (for example) instead of looping and
looking at each row. An
extremely simplified example would be to increase product price by 10% for e
ach product from country
"Spain":
Procedural thinking:
Create a cursor to loop all rows in product table.
For each row, check if it is from Spain.
If it is, execute an UPDATE statement against that product's PK value.
Set based solution:
UPDATE products SET price = price * 1.1 WHERE country = 'Spain'
I know that above is stupidly simple, but the reasoning goes even if you add
a couple of complexity
levels.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peteandrews@.hotmail.com> wrote in message
news:1138052525.777723.271910@.g14g2000cwa.googlegroups.com...
> Thanks for the replies, SET NOCOUNT didn't make any difference but
> Tibor's suggestion sorted it - I'd left out the INTO clause from the
> FETCH statements, I fixed that and it works great, so thanks very much!
> Tibor - out of interest (and a desire to get away from using cursors),
> I'd be really grateful if you could point me in the direction of where
> I can read up on set based solutions?
>

Cursors and Temp Table : in SQL Server

1. Can I pass Parameter to a Cursor ?
2. Is it must to declare the Holding variable for a Cursor above it
before opening ?
3. Can a temp table be update ?1) Yes (see example below)
2) No (see example below)
Cursor example:
use pubs
DECLARE @.State char(2)
set @.State = 'UT'
PRINT '-- Utah Authors report --'
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = @.State
ORDER BY au_id
OPEN authors_cursor
DECLARE @.au_id varchar(11), @.au_fname varchar(20), @.au_lname varchar(40),
@.message varchar(80), @.title varchar(80)
FETCH NEXT FROM authors_cursor
INTO @.au_id, @.au_fname, @.au_lname
print @.au_id
CLOSE authors_cursor
DEALLOCATE authors_cursor
3) Yes (see example below)
Create table #test (a char(1))
insert into #test values ('a')
select * from #test
update #test
set a = 'b'
select * from #test
drop table #test
"akpatelrs@.googlemail.com" wrote:

> 1. Can I pass Parameter to a Cursor ?
> 2. Is it must to declare the Holding variable for a Cursor above it
> before opening ?
> 3. Can a temp table be update ?
>