to cursor or not to cursor
-
Good morning,
The following block of code assigns a sequential GL_Control number to a batch of postings to a General Ledger Journal table. The number is created by adding 1 to the max existing GL_Control number by Fiscal_Period to the batch.
It is done using a cursor.
Can this be doen without a cursor?
--Transactions are first sorted by Fiscal_Year, Fiscal_Period, Fund into a temp table #temp1
--Now assign GL_Control numbers at Fiscal_Period or Fund breaks
DECLARE a CURSOR FOR
SELECT Fiscal_Yr_Ending, Fiscal_Period, Fund, Acct_ID, Transaction_Type, Transaction_Amount FROM #temp1
OPEN a
--Get the first row
FETCH a INTO @fiscal_year, @fiscal_period, @fund, @account_id, @type, @amount
--Initialize variables
SET @year_prev = 0;
SET @period_prev = 0;
SET @fund_prev = 0;
WHILE (@@FETCH_STATUS=0)
BEGIN
--Each fund gets a sequential GL_Control within a period.
IF @fiscal_period = @period_prev AND @fund = @fund_prev
BEGIN
GOTO GetNext
END
ELSE
BEGIN
SELECT @gl_control = MAX(GL_Control_Number) + 1
FROM GL_Journal
WHERE
Fiscal_Year = @fiscal_year
AND Fiscal_Period = @fiscal_period
GROUP BY Fiscal_Year, Fiscal_Period
IF @@ROWCOUNT = 0 --Must be the first in its year, period
SET @gl_control = 1
--INSERT GL_Journal
INSERT dbo.GL_Journal
VALUES
(
DEFAULT,
@fiscal_year,
@fiscal_period,
@gl_control,
@account_id,
GETDATE(),
'J',
CASE WHEN @type = 'D' THEN (@amount * -1) ELSE @amount END,
NULL
 
END
SET @year_prev = @fiscal_year
SET @period_prev = @fiscal_period
SET @fund_prev = @fund
GetNext: FETCH a INTO @fiscal_year, @fiscal_period, @fund, @account_id, @type, @amount;
END
CLOSE a;
DEALLOCATE a;
Thank you!
jmatt
-
HI there,
An observation is that the cursor as written is not guaranteed to do what you think it is doing. You mention up front that the data is sorted into a temp table. That probably works well. However, the cursor used to pull the data from the temp table does not have an order by clause. As such, by definition, the order of the data coming out of the temp table is non-deterministic, meaning that there is no guarantee regarding the sequence of the cursor over the table. In practice, the data will *probably* come out of the table in the same order is was inserted, but this seems like a risky assumption considering the subject matter of this proc. If you choose to use a cursor, then you really need to as an order by clause to the cursor declaration to ensure that the data is ordered correctly.
Now, on to the actual question: Can this be done without a cursor? Yes, to some extent. An algorithm would be to build a table (call it #temp2) and populate it with each distinct period and fund. In #temp2, assign a gl_control to each row. Then do an insert similar to the following:
insert into gl_journal select DEFAULT,
#temp1.fiscal_year,
#temp1.fiscal_period,
#temp2.gl_control,
@account_id,
GETDATE(),
'J',
CASE WHEN #temp1.type = 'D' THEN (#temp1.amount * -1) ELSE #temp1.amount END,
NULL
from #temp1 inner join #temp2 fiscal_period = fiscal_period and fund = fund
This is pseudocode and needs to be tweaked before it will actually run.
You would still need to iterate over #temp2 in order to assign a sequential control number, but #temp2 is probably a smaller table.
Another thought is that by using something like an identity column, you can implement an iterator that does not use a SQL cursor. The iterator tends to use fewer resources than a cursor.
hope this helps
Wayne
-
Wayne,
Thanks for your help, I really appreciate your thoughtful reply.
In an effort to understand; two questions ...
Isn't the point of a cursor to move through a table one row at a time from the first row to the last? If the rows are sorted in the source table correctly how is it possible that the order the rows are processed is non-deterministic?
If I put an order by clause in my cursor definition what does it mean? In other words, isn't the result set for each iteration of the cursor one row by definition? So what is getting ordered?
Thanks again
jmatt
-
You can view a cursor like something that is based on a select statement. It is this select statement that populates the cursor.
So, you may have a table that have some rows physically sorted by a clustered index from, say 1-10. However, to actually see these rows you must get them, and to get them you must say SELECT mycolumn from mytable.
But - there is no such thing as up or down, back or front or order in relational theory, unless you ask for it. So, by just saying 'SELECT mycolumn from mytable' will not guarantee that you are served the rows in the same order as they may lie in the table. To be sure, you must say SELECT mycolumn from mytable ORDER BY myColumn. Then, and only then can you be sure that rows are in the expected order (for sequential processing and the like)
So, your cursor should be declared something like;
DECLARE myCursor AS SELECT myCol FROM myTab ORDER BY myCol
....
Hope it got clearer?
/Kenneth
-
It got much clearer!
I don't use cursors much (I know what a drag they are). I had this ADO classic recordset image in my mind.
So ordering the #temp1 source table is really not necessary and may actually be slowing down the sp?
Again thanks for the help.
jmatt
-
> So ordering the #temp1 source table is really not necessary and may actually be slowing down the sp?
Yes, that is correct. An order by on the cursor declaration is required anyway, so you may as well not bother ordering #temp1.
Wayne
-
Thanks again for the help. I have made the changes!
jmatt
-
When you are using a temp table there is a way to do row by row processing without using a cursor:
DECLARE @key ....
SELECT TOP 1 @key = tKey FROM #Temp
WHILE LEN(LTRIM(@Key)) = 0
BEGIN
... Do changes by using @key
DELETE FROM #Temp WHERE tKEY = @key
SET @key = ''
SELECT TOP 1 @key = tKey FROM #Temp
END
5ilverFox
Consulting DBA / Developer
South Africa -
Sorry - I didn't have time to understand the full details of what you need to accomplish - so if this is off the mark please ignore.
Anyway if you can define an ordered query of the records you want to assign the sequential number to, then you can use the SELECT INTO statement with an IDENTITY field to create a temporary table with an identifier of the record and the sequency number to assign it, then you can update you original table based on this temp table... so 2 queries.
e.g.
select convert(int,id) /*Must convert existing identity fields, cos only allowed 1 in a select into*/, identity (1,1) as Sequence
into #Temp
from MyTable [where ...] [order by ...]
update MyTable set Sequence = T.Sequence
from MyTable, #Temp as T
where MyTable.id = T.id
-
You're right about the top thing, but he's using a temp table to keep the keys that need to be processed and once the work is done he deletes the key from the table.. so the order by doesn't really matter in that case. :
DELETE FROM #Temp WHERE tKEY = @key
-
I personally think a cursor is not necessary for this operation and the job can be performed entirely in the temp table. Once that's done is a matter of single insert from temp to Actual data table.
Just my $0.02
* Noel -
you don't need a cursor. you should be able to accomplish this by normalizing your data, and using an identity column.
especially because your doing something like this... which is odd.
IF @fiscal_period = @period_prev AND @fund = @fund_prev
BEGIN
GOTO GetNext
END
if you just *can't* normalize and index, then you might use an update statement w/ variable assignment
here's an example (essential lines in blue):
-- get some sample values in a table...
select t.* into mytable from (select a=1) as t
insert into mytable values (1)
insert into mytable values (1)
insert into mytable values (2)
insert into mytable values (2)
insert into mytable values (4)
insert into mytable values (4)
-- see what we have...
select * from mytable order by a
-- get max value, issue a table lock if you're serious
declare @vKeyCounter int
set @vKeyCounter = (SELECT ISNULL(MAX(a),0) FROM MyTable)
-- update using sequential assignment,
-- arbitrary conditional just for demonstration.
UPDATE MyTable
SET @vKeyCounter = a = @vKeyCounter+ 1
where a > 1
-- check the results...
select * from mytable order by a
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply