Row by Row Processing

I am trying to create a procedure within SQL Server 2000 that will update a
table for viewing over our intranet(nightly process). The final output will
show current balances within accounts.
The way the logic works for calculating the accounts is that the accounts
are allocated by years.
Example:
2001 ACCOUNT1
2002 ACCOUNT1
2001 ACCOUNT2
2003 ACCOUNT2
etc.....
Expenses occur over multiple years and must be applied to the earliest
year/account that exists. If the balance of the year/account is zero, then
look at the next year (if it exists) to apply the expense (expenses could be
split between 2 years).
What I have in the SQL server is a table with the accounts and balances, and
a seperate view with the expenditures. I want to take the view and "walk"
through record by record updating the table with current balances. I have
never done this before in SQL server and wonder if it can be done. We would
like to do it in the SQL Server because then the SQL server handles all the
data updating, etc. on it's own and we don't have to worry about an external
process to update this table.
I'm looking for an example, or guidance on what is the best way to perform
this task.
Thanks.You should go for a rowbased solution. YOu didnt post soe DDL, so we
cansee wheter the new data will be stored in existing tables (then you
should use an update) or in a new table (Then you should use ainsert or
select into). perhaps you can give some more information about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lyners" <Lyners@.discussions.microsoft.com> schrieb im Newsbeitrag
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
>I am trying to create a procedure within SQL Server 2000 that will update a
> table for viewing over our intranet(nightly process). The final output
> will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
> be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
> and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
> would
> like to do it in the SQL Server because then the SQL server handles all
> the
> data updating, etc. on it's own and we don't have to worry about an
> external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||hi
just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
was this the one u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||@.OG: But rather using cursor you should always prefer using rowbased
statements. In common you can say that cursor are slower than rowbased
statements.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
> hi
> just look at CURSORs in SQL Server Books Online. U can traverse row-by-row
> was this the one u are looking for?
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Lyners" wrote:
>|||You could use a cursor for this, but cursors on large rowsets can be slow.
It sounds like what you are trying to do could be better implemented by
joining to a sub-query.
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:742DBEF6-85F1-47B8-8F6C-3F8B668ECDE9@.microsoft.com...
> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output
will
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances,
and
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We
would
> like to do it in the SQL Server because then the SQL server handles all
the
> data updating, etc. on it's own and we don't have to worry about an
external
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||The best way to get help with your problem is to post DDL, sample data
and required results. See:
http://www.aspfaq.com/etiquett=ADe.asp?id=3D5006
I doubt that row-by-row processing is the best solution. Pobably you
can do this with an UPDATE or SELECT statement.
--=20
David Portas=20
SQL Server MVP=20
--|||Thanks Jens,
for the update. I was suposed to mention that but clicked send before
mentioning that. I normally suggest people not to use cursors as they consum
e
a lotof time and memory.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Jens Sü?meyer" wrote:

> @.OG: But rather using cursor you should always prefer using rowbased
> statements. In common you can say that cursor are slower than rowbased
> statements.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Chandra" <chandra@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:C9B5793C-2D15-48DA-96DC-8CB91DFD4338@.microsoft.com...
>
>|||It's OK to admit that we all use cursors on occasion. They can be convenient
so long as the rowset is small. A lot of the system stored procedures in
MASTER are implemented using cursors, so we can't avoid them.
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:1101D390-347A-45C3-AA4B-78577F8A4080@.microsoft.com...
> Thanks Jens,
> for the update. I was suposed to mention that but clicked send before
> mentioning that. I normally suggest people not to use cursors as they
consume
> a lotof time and memory.
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Jens Smeyer" wrote:
>
row-by-row
update
output
accounts
earliest
could
balances,
"walk"
have
all|||I got it working... kind of. My problem is the select at the end. it appears
that the @.vchNextExpenseFiscalYearID are being reset. I set the
@.vchNextExpenseFiscalYearID by a select statement, but there is another quer
y
with the same field name (FiscalYear) that pulls into another varable. Does
SQL Server 2000 set a varable so that it is static so it is set to the year
(i.e. 2003), or is my varable (@.vchNextExpenseFiscalYearID) dynamic and set
to the field FiscalYear, so it changes with the field?
Please note that I did not use Cursors or Fetch next. I didn't use cursors
because of the resource use, and I didn't use Fetch Next because I did not
realize there was such a command until I was close to the end.
here is my code:
CREATE PROCEDURE dbo.loadunspentbonds AS
delete from unspentbonds
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetprior2005
insert into unspentbonds(fiscalyear, project, subproject, bondamount,
UnspentBond) select fiscalyear, project, subproject, bondamount, bondamount
from vwUnspentBondsbudgetafter2004
/* Update the Unspent Bond table with Expenditures and the current bond amou
nt
** We use a row by row processing to achieve our results because expenditure
s
** go back to the first year of the bonds
*/
SET NOCOUNT ON
-- declare all variables!
DECLARE @.iReturnCode int,
@.vchNextProjectID nvarchar(5),
@.vchNextSubProjectID nvarchar(2),
@.vchNextFiscalYearID nvarchar(4),
@.vchCurrentProjectID nvarchar(5),
@.vchCurrentSubProjectID nvarchar(2),
@.vchCurrentFiscalYearID nvarchar(4),
@.iExpenseLoopControl int,
@.vchNextExpenseProjectID nvarchar(5),
@.vchNextExpenseSubProjectID nvarchar(2),
@.vchNextExpenseFiscalYearID nvarchar(4),
@.vchCurrentExpenseProjectID nvarchar(5),
@.vchCurrentExpenseSubProjectID nvarchar(2),
@.vchCurrentExpenseFiscalYearID nvarchar(4),
@.fltGLExpendedAmount float,
@.fltUnspentBondAmount float
-- Initialize variables
SELECT @.iExpenseLoopControl = 1
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
-- Make sure the table has data
if isnull(@.vchNextExpenseProjectID,'') = ''
BEGIN
RETURN
END
-- Retrieve the first Unspent Bond Row
SELECT TOP 1 @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID =
FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
WHILE @.iExpenseLoopControl = 1
BEGIN
-- Begin the nested(inner) loop.
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchCurrentExpenseProjectID and
SubProject = @.vchCurrentExpenseSubProjectID
--make sure that the Unspent Bond exists
if isnull(@.vchNextProjectID,"") <> ""
BEGIN
WHILE @.vchNextProjectID = @.vchCurrentExpenseProjectID and
@.vchNextSubProjectID = @.vchCurrentExpenseSubProjectID
BEGIN
-- Get the first Unspent Bond for the current Expense Record
SELECT @.vchCurrentProjectID = Project,
@.vchCurrentSubProjectID = SubProject,
@.vchCurrentFiscalYearID = FiscalYear,
@.fltUnspentBondAmount = UnspentBond
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project = @.vchNextProjectID and
SubProject = @.vchNextSubProjectID and
FiscalYear = @.vchNextFiscalYearID
IF @.fltGLExpendedAmount < @.fltUnspentBondAmount
BEGIN
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = @.fltUnspentBondAmount - @.fltGLExpendedAmount,
ExpenditureAmount = ExpenditureAmount +
@.fltGLExpendedAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
SELECT @.fltGLExpendedAmount = 0
END
ELSE
BEGIN
SELECT @.fltGLExpendedAmount = @.fltGLExpendedAmount -
@.fltUnspentBondAmount
UPDATE [CapitalfINANCE].[dbo].[UnspentBonds]
SET unspentBond = 0,
ExpenditureAmount = ExpenditureAmount + @.fltUnspentBondAmount
WHERE Project = @.vchCurrentProjectID and
SubProject = @.vchCurrentSubProjectID and
FiscalYear = @.vchCurrentFiscalYearID
END
SELECT @.vchNextProjectID = Project,
@.vchNextSubProjectID = SubProject,
@.vchNextFiscalYearID = FiscalYear
FROM [CapitalfINANCE].[dbo].[UnspentBonds]
WHERE Project + SubProject + FiscalYear > @.vchNextProjectID +
@.vchNextSubProjectID + @.vchNextFiscalYearID
END
END
SELECT TOP 1 @.vchNextExpenseProjectID = Project,
@.vchNextExpenseSubProjectID = SubProject,
@.vchNextExpenseFiscalYearID = FiscalYear
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project + SubProject + FiscalYear >
@.vchCurrentExpenseProjectID + @.vchCurrentExpenseSubProjectID +
@.vchCurrentExpenseFiscalYearID
BEGIN
-- Make sure the table has data
if isnull(@.vchnextExpenseProjectID,"") = ""
BEGIN
BREAK
END
SELECT @.vchCurrentExpenseProjectID = Project,
@.vchCurrentExpenseSubProjectID = SubProject,
@.vchCurrentExpenseFiscalYearID = FiscalYear,
@.fltGLExpendedAmount = GLExpended
FROM [CapitalFinance].[dbo]. [vwUnspentBondsDailyExpendituresAfter200
4]
WHERE Project = @.vchNextExpenseProjectID and
SubProject = @.vchNextExpenseSubProjectID and
FiscalYear = @.vchNextExpenseFiscalYearID
END
RETURN
GO
"Lyners" wrote:

> I am trying to create a procedure within SQL Server 2000 that will update
a
> table for viewing over our intranet(nightly process). The final output wil
l
> show current balances within accounts.
> The way the logic works for calculating the accounts is that the accounts
> are allocated by years.
> Example:
> 2001 ACCOUNT1
> 2002 ACCOUNT1
> 2001 ACCOUNT2
> 2003 ACCOUNT2
> etc.....
> Expenses occur over multiple years and must be applied to the earliest
> year/account that exists. If the balance of the year/account is zero, then
> look at the next year (if it exists) to apply the expense (expenses could
be
> split between 2 years).
> What I have in the SQL server is a table with the accounts and balances, a
nd
> a seperate view with the expenditures. I want to take the view and "walk"
> through record by record updating the table with current balances. I have
> never done this before in SQL server and wonder if it can be done. We woul
d
> like to do it in the SQL Server because then the SQL server handles all th
e
> data updating, etc. on it's own and we don't have to worry about an extern
al
> process to update this table.
> I'm looking for an example, or guidance on what is the best way to perform
> this task.
> Thanks.
>