create table kool
(
id int primary key identity(1,1),
koolnimi varchar(30) unique,
aadress text,
kontaktTelefon varchar(13),
regNumber varchar(10)
);
insert into kool(koolNimi, aadress, kontaktTelefon, regNumber)
values('Tallinna Kutsekool', 'Tehnika, 2 Tallinn', '45678987','9994566788');
insert into kool(koolNimi, aadress, kontaktTelefon, regNumber)
values('THG', 'Koidu, 7 Tallinn', '88899445','9994565689');
insert into kool(koolNimi, aadress, kontaktTelefon, regNumber)
values('TMG', 'Mustamäe tee, 58 Tallinn', '13254877','9994566456');
select * from kool
#вызов процедуры
use procedure_topolja;
exec helloKool;
#удаление процедуры
use procedure_topolja;
drop procedure helloKool;
#Пользователь вводит 1 букву школы и отображаются все данные о этих школах
use procedure_topolja
go
create procedure nimiOtsing
@taht varchar (1)
as
begin
select * from kool
where koolnimi like @taht + '%'
end;
exec nimiotsing @taht='T';
#с помощью id удаляет запись
use procedure_topolja
go
create procedure deleteId
@deleteId int
as
begin
delete from kool
where id=@deleteId;
select * from kool
end;
exec deleteId @deleteId=3;
#
use procedure_topolja
go
create procedure kooliMaksumus
@minHind money output,
@maxHind money output
AS
begin
select @minHind=MIN(maskumus),
@maxHind = MAX(maskumus)
from kool
end
declare @minHind money, @maxHind money;
execute kooliMaksumus @minHind output, @maxHind output
print 'minimaalne hind'+convert(varchar, @minHind);
print 'maksimaalne hind'+convert(varchar, @maxHind);
USE procedure_topolja;
GO
CREATE PROCEDURE addKool
@koolNimi_ nvarchar(30),
@aadress_ text,
@kontaktTelefon_ nvarchar(13),
@regNumber_ nvarchar(10)
AS
INSERT INTO kool(koolNimi, aadress, kontaktTelefon, regNumber)
VALUES(@koolNimi_, @aadress_, @kontaktTelefon_, @regNumber_)
