SQL Procedures

create table kool1(
id int primary key identity(1,1),
koolNimi varchar(50) unique,
aadress text,
telefon varchar(10),
regNumber varchar(20)
);
insert into kool1(koolNimi, aadress)
values (‘Tallinna Kool’, ‘Tehnika 18, Tallinn’);
Select * from kool1;

— protseduuri loomine
use VitaliiSokhan
go
create procedure helloKool1
as
begin
select * from kool1;
end;

–protseduuri vyzov

use VitaliiSokhan;
exec helloKool1;

–proc udalenie

drop procedure helloKool1;

–vvod pervuju bukvu shkoly i togda otobrazhajutsa vse dannye o shkole

use VitaliiSokhan;
go
create procedure kooliOtsing
@taht varchar(1)
as
begin
select * from kool1
WHERE koolNimi LIKE @taht + ‘%’;
end;

–vyzov procedury
exec kooliOtsing @taht=’T’;

—
–proc na dobavlenie

use VitaliiSokhan;
go
create procedure lisaKool
@kNimi varchar(50),
@aadress TEXT,
@telefon varchar(13),
@reg varchar(10)
as
begin
insert into kool1(koolNimi, aadress, telefon, regNumber)
values (@kNimi, @aadress, @telefon, @reg);
select * from kool1;
end;

–vyzov
exec lisaKool ‘Aianduskool’, ‘Rapina’, ‘123456’, ‘8695369’;

–udalenie

use VitaliiSokhan
go
create procedure kustutaKool
@deleteId int
as
begin
select* from kool1;
delete from kool1
where id=@deleteId;
select* from kool1;
end;

–VYZOV
EXEC kustutaKool 1;

–PROCEDURA SPRASHYVAET CHTO NUZNO SDELAT I U NAS DVA VYBORA DOBAVIT STOLBIK ILI UDALIT
—ADD – DOBAVLENIE // DROP – UDALENIE
CREATE PROCEDURE muudatus
@tegevus varchar(10),
@tabelinimi varchar(25),
@veerunimi varchar(25),
@tyyp varchar(25) =null
AS
BEGIN
DECLARE @sqltegevus as varchar(max)
set @sqltegevus=case
when @tegevus=’add’ then concat(‘ALTER TABLE ‘,
@tabelinimi, ‘ ADD ‘, @veerunimi, ‘ ‘, @tyyp)
when @tegevus=’drop’ then concat(‘ALTER TABLE ‘,
@tabelinimi, ‘ DROP COLUMN ‘, @veerunimi)
END;
print @sqltegevus;
begin
EXEC (@sqltegevus);
END
END;
–добавление столбца
EXEC muudatus @tegevus=’add’, @tabelinimi=’kool1′, @veerunimi=’opilasteArv’, @tyyp=’int’;

select * from kool1;
–удаление столбца
EXEC muudatus @tegevus=’drop’, @tabelinimi=’kool1′, @veerunimi=’test’;
select * from kool1;

---

—uuendada opilsteArv id jargi

create procedure uuendaKoll
@arv int,
@uuendatudid int
as
begin
select * from kool1;
update kool1 set opilasteArv = @arv
where id=@uuendatudid;
select * from kool1;
end;

–zapusk

exec uuendaKoll @arv=5000, @uuendatudid=5