February 24, 2009 at 1:24 am
i have the ff.tables
create table program(
pid int,
pcode varchar(20),-- program code (e.g. 'BSCS', 'BSIT')
pname varchar(20),-- program description
yrs int-- number of years of the program (1, 2,3 and 4)
)
create table unitprice (
upid int,-- id
pid varchar(20), -- foreign key
sy varchar(9),-- school year
sem int,-- semester
yrlevel int,-- apply to a year level
amount-- price
)
i want to initialize the unitprice of all programs for all year levels (based on the programs number of yrs)
Usually i use a cursor to do this. Is there another way of doing this in just one or two sql transaction and not use a loop?
declare @pid nvarchar(5)
declare @yrs int
declare @prgyrs int
--Declare the cursor
declare cursor_unitprice cursor for
select pid, yrs from program
--Open the cursor
open cursor_unitprice
--Fetch the 1st Row
fetch cursor_unitprice into @pid, @yrs
while @@fetch_status = 0
begin
set @prgyrs = @yrs
while (@prgyrs > 0)
begin
insert into unitprice(pid, sy, sem, yrlevel, amount) values(@pid, @sy, @sem, @prgyrs, 0)
set @prgyrs = @prgyrs -1
end
--Fetch next row
fetch cursor_unitprice into @pid, @yrs
end
--Close and Deallocate cursor
close cursor_unitprice
deallocate cursor_unitprice
Sample Output:
Program Table
pidpcodepname yrs
_______________________________________
101BSCSComp. Sci.4
102HMHosp. Mgmt.2
UnitPrice Table
upidpidsysem yrlevelamount
_______________________________________________________
11012008-200921 0
21012008-200922 0
31012008-200923 0
41012008-200924 0
51022008-200921 0
61022008-200922 0
February 24, 2009 at 1:59 am
A quick comment, the datatype is missing for the amount column.
And yes, there is an alternative to cursor, i will try to post the solution later on (busy right now).
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 24, 2009 at 2:26 am
You can use this statement as a base to accomplish what you want. (You still have to create your insert statement from this Common Table Expression)
;WITH myCTE(pid, yrs, lvl)
AS (
SELECT pid, yrs, 1 FROM program
union all
SELECT p.pid, p.yrs, lvl+1 FROM program p inner join myCte ct on ct.pid=p.pid where ct.lvl<p.yrs--and ct.rowCnt<p.yrs
)
SELECT * FROM myCTE order by pid
/Håkan Winther
Senior Development DBA
(Always trying to avoid cursors)
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
February 24, 2009 at 2:53 am
thanx for the new info, its my first encounter with CTE, giving it a try right now...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply