use chap3

use chapter3
print chap3.dbo.PulleyDistance(40, 20, 300)

print chap3.dbo.PulleyDistance(40, 20, null)

create table Pulley
(
model char(10),
diameter float
)

create table Belt
(
model char(10),
length float
)


truncate table Pulley

declare @i int
set @i = 40
while (@i > 0)
BEGIN
insert into Pulley values ('P' + CONVERT(varchar, @i), @i)
set @i = @i - 1
END


declare @i int
set @i = 30
while (@i > 0)
BEGIN
insert into Belt values ('M' + CONVERT(varchar, @i), @i * 4)
set @i = @i - 5
END

select count(*) from Belt, Pulley P1, Pulley P2

Create View Transmissions
as select B.model as Belt, 
P1.model as P1, 
P2.model as P2, 
dbo.PulleyDistance(P1.diameter, P2.diameter, B.length) as C
from
Belt B, Pulley P1, Pulley P2
where dbo.PulleyDistance(P1.diameter, P2.diameter, B.length) > 0
and P1.Diameter < P2.Diameter

select * from Transmissions


select B.model as Belt, 
P1.model as P1,
 P2.model as P2,
 dbo.PulleyDistanceD(P1.diameter, P2.diameter, B.length) as C
from
Belt B, Pulley P1, Pulley P2
where dbo.PulleyDistanceD(P1.diameter, P2.diameter, B.length) > 0
and P1.Diameter < P2.Diameter
order by dbo.PulleyDistanceD(P1.diameter, P2.diameter, B.length)


declare @d decimal
set @d = 5
print SQRT(@d)


CREATE FUNCTION Width(@Size int)
returns int
AS
BEGIN
IF (@Size IS NULL)
  RETURN NULL
RETURN -@Size
END

print dbo.Width(null)

