procedure/static cursor - 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 2005 Forums

Transact-SQL (2005)

procedure/static cursor

Author

Topic

magdaf75
Starting Member

5 Posts

Posted - 2008-03-30 : 23:16:43

can someone tell me how to use static cursor to read the rows applying it in procedure.
i have input and output tables
i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges.
ok, there is my problem:
An Internet service provider has three different subscription
packages for its customers:

Package A: For $15 per month with 50 hours of access provided.
Additional hours are $2.00 per hour over 50 hours.
Assume usage is recorded in one-hour increments,
i.e., a 25-minute session is recorded as one hour.

Package B: For $20 per month with 100 hours of access provided.
Additional hours are $1.50 per hour over 100 hours.

Package C: For $25 per month with 150 hours access is provided.
Additional hours are $1.00 per hour over 150 hours

Assume a 30-day billing cycle.

1) Create a table to hold customer input billing data.

2) Populate input table with follwing records:

CustomerID Pkg Hours
---------- --- ------
1000 A 49
1010 A 50
1020 a 90
1030 a 130

1090 B 40
1100 B 99
1110 b 100
1120 b 145

1140 C 45
1150 c 85
1160 c 149
1170 c 150
1180 c 200

3) Create a table to hold customer data used to generate
the statement to be sent to the customer. It should
include CustomerID, Package, HoursUsed, and Charges.

Write an SQL script that reads customer billing data,
calculates a customer’s monthly charges, and populates
the customer statement table.
Use Cursor to process records and Stored Procedures for
ProcessBill and calcCharges.

CREATE TABLE custinput(
cust_id int NULL,
pkg char(1) NULL,
hrs smallint NULL
)

CREATE TABLE custoutput(
cust_id int NULL,
pkg char(1) NULL,
hrsused smallint NULL,
charges money null

)

insert into custinput values (1000,'A',49);
insert into custinput values (1010,'A',50);
insert into custinput values (1020,'a',90);
insert into custinput values (1030,'a',130);
insert into custinput values (1090,'B',40);
insert into custinput values (1100,'B',99);
insert into custinput values (1110,'b',100);
insert into custinput values (1120,'b',145);

insert into custinput values (1140,'C',45);
insert into custinput values (1150,'c',85);

insert into custinput values (1160,'c',149);
insert into custinput values (1170,'c',150);
insert into custinput values (1180,'c',200);

then there is conditions:

if upper (@pkg)= 'A'
begin
if @hrs<= 50 set @charges =15
else set @charges =15 + (@hrs-50)*2

end;

else if upper(@pkg)= 'B'
begin
if @hrs <= 100 set @charges = 20
else set @charges = 20 + (@hrs - 100)*1.5

end;
else

if @hrs <=150 set @charges = 25
else set @charges =25+(@hrs-150)

insert into custoutput values(@cust_id,@pkg,@hrs,@charges)

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-31 : 03:21:09

Why do you want to use cursors?Any particulare reason?
Without a cursor a single insert and update statement can do your work.

CREATE TABLE custinput(
cust_id int NULL,
pkg char(1) NULL,
hrs smallint NULL
)

CREATE TABLE custoutput(
cust_id int NULL,
pkg char(1) NULL,
hrsused smallint NULL,
charges money null

)
insert into custinput values (1000,'A',49);
insert into custinput values (1010,'A',50);
insert into custinput values (1020,'a',90);
insert into custinput values (1030,'a',130);
insert into custinput values (1090,'B',40);
insert into custinput values (1100,'B',99);
insert into custinput values (1110,'b',100);
insert into custinput values (1120,'b',145);
insert into custinput values (1140,'C',45);
insert into custinput values (1150,'c',85);
insert into custinput values (1160,'c',149);
insert into custinput values (1170,'c',150);
insert into custinput values (1180,'c',200);

insert into custoutput(cust_id,pkg,hrsused,charges)select cust_id,pkg,hrs,0 from custinput

update custoutput set charges=
case pkg
when 'a' then
case when hrsused>50 then ((hrsused-50)*2)+15 else 15 end
when 'b' then
case when hrsused>100 then ((hrsused-100)*1.50)+20 else 20 end
when 'c' then
case when hrsused>150 then ((hrsused-150)*1)+25 else 25 end
end

select * from custoutput

drop table custinput
drop table custoutput