Using CURSOR

Question

Post reply

Using CURSOR

willian-1136841

SSChasing Mays

Points: 645
More actions
August 3, 2009 at 12:16 pm

#213113

Hi!
I need to do a cursor that calculates the subtotal from a query with 2 subqueries. I'll return the result to Delphi. That´s my first cursor and I did it:
ALTER PROC [dbo].[FluxoContas]
@DT_START AS DATE,
@DT_END AS DATE,
@DT_START AS DATE,
@DT_END AS DATE
AS
DECLARE @COD AS INTEGER
DECLARE @MYDATE AS DATE
DECLARE @RECEIVE AS FLOAT
DECLARE @PAY AS FLOAT
DECLARE @PEOPLE AS NVARCHAR(40)
DECLARE @KIND AS CHAR(1)
DECLARE @RESULT AS FLOAT
DECLARE MyCursor SCROLL CURSOR
FOR
SELECT COD, MYDATE, RECEIVE, PAY, PEOPLE, KIND FROM (
SELECT C.COD,
C.DATA_VENCTO AS MYDATE,
0 AS RECEIVE,
C.VALOR_NOMINAL AS PAY,
F.FORNECEDOR AS PEOPLE,
'P' AS KIND
FROM ACCOUNTS_PAY C, SUPPLYERS F
WHERE C.FORNECEDOR = F.CODIGO
UNION
SELECT C.COD,
C.DATA_VENCTO AS MYDATE,
C.VALOR_NOMINAL AS RECEIVE,
0 AS PAY,
'MENSALIDADE' AS PEOPLE,
'R' AS KIND
FROM ACCOUNTS_RECEIVE C
)
ACCOUNTS_TEMP
ORDER BY 2, 1
CREATE TABLE #MY_TEMP (COD BIGINT, MYDATE DATE, RECEIVE FLOAT,
PAY FLOAT, PEOPLE NVARCHAR(40), KIND CHAR(1), RESULT FLOAT)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @COD , @MYDATE , @RECEIVE , @PAY , @PEOPLE , @KIND
WHILE @@FETCH_STATUS = 0
BEGIN
@RESULT= @RESULT + @RECEIVE - @PAY
INSERT INTO #TEMPORARIA VALUES (@COD , @MYDATE , @RECEIVE , @PAY , @PEOPLE , @KIND , @RESULT)
FETCH NEXT FROM MyCursor INTO @COD , @MYDATE , @RECEIVE , @PAY , @PEOPLE , @KIND
END
CLOSE MyCursor
DEALLOCATE MyCursor
When I compile it tells me that the line:
@RESULT= @RESULT + @RECEIVE - @PAY
Is wrong. @RESULT is not recognized.
I need to to this operation and return #MY_TEMP as result.
How do I do this?

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

SET @RESULT= @RESULT + @RECEIVE - @PAY

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

I also suggest not to use cursors: they're slow.

There's almost nothing that can't be done without cursors.

-- Gianluca Sartori

willian-1136841 SSChasing Mays Points: 645 More actions · Answer 3

Thanks!!!

It works fine!

But, one more question: If cursors aren´t the best choice, is there another way to do this? I don´t wanna do this operation in a external software, I need to do this operation direct into SQL Server. Is it possible?

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

You could use the famous "quirky update" algorithm by Jeff Moden.

It is described in an article that is currently under construction. I hope it will be soon fixed. The link is here:

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Anyway, even if the article is in process of rewrite, you can still rely on the technique.

For your tables this should do the trick. I'm not 100% sure that it will work because I had no sample data to test it: if it doesn't work and you want to go this way, you can post here sample data and DDL for your tables.

ALTER PROC [dbo].[FluxoContas]

@DT_START AS DATE,

@DT_END AS DATE,

@DT_START AS DATE,

@DT_END AS DATE

AS

DECLARE @COD AS INTEGER

DECLARE @MYDATE AS DATE

DECLARE @RESULT AS FLOAT

CREATE TABLE #MY_TEMP

(

COD BIGINT,

MYDATE DATE,

RECEIVE FLOAT,

PAY FLOAT,

PEOPLE NVARCHAR(40),

KIND CHAR(1),

RESULT FLOAT

)

CREATE CLUSTERED INDEX IX_TMP_ORDERBY --clustered to resolve "Merry-go-Round"

ON #MY_TEMP (COD, MYDATE)

INSERT INTO #MY_TEMP

SELECT C.COD,

C.DATA_VENCTO AS MYDATE,

0 AS RECEIVE,

C.VALOR_NOMINAL AS PAY,

F.FORNECEDOR AS PEOPLE,

'P' AS KIND,

CAST(0 AS float) AS Result

FROM ACCOUNTS_PAY C,

SUPPLYERS F

WHERE C.FORNECEDOR = F.CODIGO

UNION

SELECT C.COD,

C.DATA_VENCTO AS MYDATE,

C.VALOR_NOMINAL AS RECEIVE,

0 AS PAY,

'MENSALIDADE' AS PEOPLE,

'R' AS KIND,

CAST(0 AS float) AS Result

FROM ACCOUNTS_RECEIVE C

ORDER BY 2, 1

UPDATE #My_Temp

SET @Result = Result = @RESULT + @RECEIVE - @PAY

FROM #My_Temp WITH (INDEX(IX_TMP_ORDERBY),TABLOCKX)

SELECT *

FROM #My_temp

Hope this helps

Gianluca

-- Gianluca Sartori

willian-1136841 SSChasing Mays Points: 645 More actions · Answer 5

Hi, thanks for helping!

This code will process each row using @RESULT from one line to another?

I need take the @RESULT from atual row and use it on the next row... @RESULT has a accumulated value since first row until last... this is a money account operation.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

willian (8/4/2009)
Hi, thanks for helping!
This code will process each row using @RESULT from one line to another?
I need take the @RESULT from atual row and use it on the next row... @RESULT has a accumulated value since first row until last... this is a money account operation.

Absolutely... and it's capable of doing a million rows in under 7 seconds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Just don't get the idea that you can use any ol' index to do this... It MUST be a clustered index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

I may have missed it, but you will want to ensure that @RESULT is initialized to zero (0) before you start the update.

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

Hi Jeff, thanks for chiming in!

When will the article be online again? It's hard to explain this technique to others without the help of your article... expecially for poor pretenders like me! 🙂

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Gianluca Sartori (8/5/2009)
Hi Jeff, thanks for chiming in!
When will the article be online again? It's hard to explain this technique to others without the help of your article... expecially for poor pretenders like me! 🙂

You're definitely not a pretender, Gianluca. You're becoming one of the greats on this forum.

I thought I could get back to the article a couple of times and just haven't been able to do it. I may have to split it in two so I can get it done. I don't have a hard date yet. Apologies for the slothfulness on this one. I'm so far behind that I need to be twins to catch up. Like I said to a friend the other day, "It's no longer a matter of how many sticks I have in the fire, it's now a matter of how fires I have sticks in.". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 11

I'm a bit curious... what was the issue? I know that you can't do a "top 100 per cent" ... "order by ..." statement in a view... is this the same issue?

If so... does the OVER statement work at all?

i.e.

create view guaranteedOrderView

select xxx, row_number() over (order by xxx) as OrderNo

Not sure how to test it to see for myself...

Random Technical Stuff[/url]

Slick84 SSCertifiable Points: 5602 More actions · Answer 12

Lynn Pettis (8/4/2009)
I may have missed it, but you will want to ensure that @RESULT is initialized to zero (0) before you start the update.

How would you initiliaze the @result to zero (0) ?

--
:hehe:

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 13

Slick84 (8/6/2009)
Lynn Pettis (8/4/2009)
I may have missed it, but you will want to ensure that @RESULT is initialized to zero (0) before you start the update.
How would you initiliaze the @result to zero (0) ?

set @result=0.0

Random Technical Stuff[/url]

Slick84 SSCertifiable Points: 5602 More actions · Answer 14

ta.bu.shi.da.yu (8/6/2009)
Slick84 (8/6/2009)
Lynn Pettis (8/4/2009)
I may have missed it, but you will want to ensure that @RESULT is initialized to zero (0) before you start the update.
How would you initiliaze the @result to zero (0) ?
set @result=0.0

So not trying to hi-jack this thread but If i'm doing multiple inserts/updates in a stored procedure and storing the counts after each update/insert in a variable and then passing the value onto another stored procedure, do I have to initialize it to zero (0) before it gets to the next statement or does it automatically initialize to zero(0) after i've passed the parameter?

Like so...

update1

set @value = @@rowcount