SP HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP HELP

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2007-09-12 : 13:18:58

I'm new to stored procedures and would like some help with something I'm trying to accomplish if it can be done. Below is a query that with a snippet of its result set. I would like the SP to do is pass variables with a start and end for the period. I hope this makes sense and any help provided would be greatly appreciated.

Thanks

PS. Hope this isnt too confusing :)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set nocount on
SET LOCK_TIMEOUT 50000

delete gposoftextract

DECLARE Cursor_Accts CURSOR for
(Select distinct a.actindx, a.year1
from gl10110 a
join gl00100 b
on a.actindx = b.actindx
where b.pstngtyp = 0)

DECLARE @AcctIdx int
DECLARE @AcctYear smallint
DECLARE @AcctPeriod smallint

DECLARE @Counter int
set @Counter = 1

DECLARE @tbl_balance_accts TABLE
(actindx int,
Year1 smallint,
Periodid smallint,
perdblnc numeric(19,5)
)

---------------------------------------------
-- Create records for all balance sheet accounts
-- and all periods
----
OPEN Cursor_Accts

FETCH NEXT FROM Cursor_Accts INTO @AcctIdx, @AcctYear

WHILE @@FETCH_STATUS = 0
begin
WHILE @Counter < 13
Begin

INSERT INTO @tbl_balance_accts
(Actindx, year1, periodid, perdblnc)
Values (@AcctIdx, @AcctYear, @counter, 0)

Set @Counter = @Counter + 1
End
Set @Counter = 1
FETCH NEXT FROM Cursor_Accts INTO @AcctIdx, @AcctYear
end

CLOSE Cursor_Accts
DEALLOCATE Cursor_Accts

UPDATE @tbl_balance_accts
set perdblnc = (SELECT sum(perdblnc) from gl10110 b
where b.actindx = a.actindx
and b.year1 = a.year1
and b.periodid <= a.periodid)
from @tbl_balance_accts a, gl10110 b

------------------------------------
-- Insert P&L records into the table variable

INSERT INTO @tbl_balance_accts
SELECT a.actindx, a.year1, a.periodid, a.perdblnc
from gl10110 a
join gl00100 b
on a.actindx = b.actindx
where a.Perdblnc <> 0
and b.pstngtyp <> 0

---------------------------------------
-- Select records from table variable to return
insert into GPOSOFTEXTRACT -- Change made to insert result set into GPOSOFTEXTRACT table - John Ortiz
select
a.actnumbr_1 as 'BizType',
a.actnumbr_2 as 'GEO LOC',
a.actnumbr_3 as 'NAT CLASS',
a.actnumbr_4 as 'DEPT',
a.actnumbr_5 as 'COST CTR',
periodid,
g.year1

,case
when periodid = 1
then cast (year1 - 1 as varchar (30))+ '.' + 'OCT'
when periodid = 2
then cast (year1 - 1 as varchar (30))+ '.' + 'NOV'
when periodid = 3
then cast (year1 as varchar (30))+ '.' + 'DEC'
when periodid = 4
then cast (year1 as varchar (30))+ '.' + 'JAN'
when periodid = 5
then cast (year1 as varchar (30))+ '.' + 'FEB'
when periodid = 6
then cast (year1 as varchar (30))+ '.' + 'MAR'
when periodid = 7
then cast (year1 as varchar (30))+ '.' + 'APR'
when periodid = 8
then cast (year1 as varchar (30))+ '.' + 'MAY'
when periodid = 9
then cast (year1 as varchar (30))+ '.' + 'JUN'
when periodid = 10
then cast (year1 as varchar (30))+ '.' + 'JUL'
when periodid = 11
then cast (year1 as varchar (30))+ '.' + 'AUG'
when periodid = 12
then cast (year1 as varchar (30))+ '.' + 'SEP'
when periodid = 13
then cast (year1 as varchar (30))+ '.' + 'YEARENDADJ'
when periodid = 0
then cast (year1 - 1 as varchar (30))+ '.' + 'ROLLFORWARD'
else cast (year1 - 1 as varchar (30))+ '.' + 'UNKOWN'

END as 'FISCAL PERIOD'

,isnull(Perdblnc,0) as AMOUNT

from @tbl_balance_accts g
join gl00105 a
on g.actindx = a.actindx
join gl00100 d
on g.actindx = d.actindx

select * from gposoftextract

Result set
ACCTNUM_1 ACCTNUM_2 ACCTNUM_3 ACCTNUM_4 ACCTNUM_5 PERIOD YEAR PERIODID BALANCE
--------- --------- --------- --------- --------- ------ ------ -------------------- ---------------------------------------
1 CA03 90430 28 008 3 2007 2007.DEC 222.77000
1 CA03 90430 28 008 4 2007 2007.JAN 337.28000
1 CA03 90430 28 008 5 2007 2007.FEB 455.36000
1 CA03 90430 28 008 6 2007 2007.MAR 538.81000
1 CA03 90430 28 008 7 2007 2007.APR 613.96000
1 CA03 90430 28 008 8 2007 2007.MAY 706.87000
1 CA03 90430 28 008 9 2007 2007.JUN 827.21000
1 CA03 90430 28 008 10 2007 2007.JUL 958.04000
1 CA03 90430 28 008 11 2007 2007.AUG 1016.38000
1 CA03 90430 28 008 12 2007 2007.SEP 837.53000
1 CA05 90588 40 999 1 2007 2006.OCT 728.00000
1 CA05 90588 40 999 2 2007 2006.NOV 1174.25000
1 CA05 90588 40 999 3 2007 2007.DEC 1587.00000
1 CA05 90588 40 999 4 2007 2007.JAN 2206.25000
1 CA05 90588 40 999 5 2007 2007.FEB 2554.25000
1 CA05 90588 40 999 6 2007 2007.MAR 2937.50000

John O

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-12 : 20:42:41

Try getting rid of the cursor and writing it as a single statement.

Suspect you want to pass in the two dates to an sp

create proc s_myproc
@start datetime ,
@end datetime
as

Not sure where you want to use the dates in the proc though

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

justjohno
Starting Member

23 Posts

Posted - 2007-09-13 : 04:42:14

NR, thanks for the advice. Let me try to organize this mess of a query and repost. Again, I'm new and learning.

John O

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources