0AdCmapx

1--ISHOD 4--
2--grupa 1--
3--ZAD. 11--
4create proc p1
5@ime nvarchar(25),
6@prezime nvarchar(25),
7@id int output
8as
9if not exists(
10select IDKomercijalist from Komercijalist
11where Ime = @ime and Prezime = @prezime)
12begin
13insert into Komercijalist (Ime, Prezime)
14values (@ime, @prezime)
15set @id = SCOPE_IDENTITY()
16end
17else
18set @id=-1
19
20declare @idout int
21exec p1 'Milutin', 'Krmpotic', @idout output
22print @idout
23
24
25--ZAD 12--
26alter proc p2
27@id int
28as
29if not exists(
30select IDProizvod from Proizvod
31where IDProizvod = @id
32)
33begin
34return -1
35end
36else if exists(
37select count(ProizvodID) as broj
38from Proizvod inner join Stavka on Stavka.ProizvodID = Proizvod.IDProizvod
39where Proizvod.IDProizvod = @id
40having count(ProizvodID) <	1
41)
42begin 
43return 0
44end
45else
46begin
47return 1
48end
49
50select*from Proizvod
51
52select*from Stavka
53where ProizvodID = 317
54
55--postoji i prodavan je--
56declare @r int
57exec @r= p2 776
58print @r
59--postoji, nije prodavan
60declare @r int
61exec @r= p2 317
62print @r
63--ne postoji
64declare @r int
65exec @r= p2 22222
66print @r
67
68--ZAD. 13. --
69select*from Potkategorija
70
71create proc cu
72 @idpk int output,
73 @idk int, 
74 @naziv nvarchar(50)
75as
76if exists(
77select*
78from Potkategorija
79where @idpk = Potkategorija.IDPotkategorija)
80update Potkategorija
81set Naziv = @naziv, KategorijaID = @idk
82where IDPotkategorija = @idpk
83else
84insert into Potkategorija (Naziv, KategorijaID)
85values (@naziv, @idk)
86set @idpk = SCOPE_IDENTITY()
87
88
89alter proc dr
90@op char(1),
91@idpk int output
92as
93if @op = 'D'
94begin
95delete from Potkategorija where IDPotkategorija = @idpk
96end
97if @op = 'R'
98begin 
99select*from Potkategorija where IDPotkategorija = @idpk
100end
101
102--umetanje--
103select* from Potkategorija
104declare @out int
105exec cu @out output, 6,	Frogs
106print @out
107--dohvacanje--
108declare @t int
109exec @t = dr 'R', 1
110print @t
111--update--
112declare @out int
113exec cu 39, 6, Frogzes
114print @out
115--delete--
116declare @t int
117exec @t = dr 'D', 40
118
119
120--grupa 2--
121--ZAD 1--
122create proc p1b
123@naziv nvarchar(25),
124@id int output
125as
126if not exists(
127select*
128from Kategorija
129where Kategorija.Naziv = @naziv)
130begin
131insert into Kategorija(Naziv) values (@naziv)
132set @id = SCOPE_IDENTITY()
133end
134else
135set @id=-1
136
137declare @r int
138exec p1b 'Boxes', @r output
139print @r
140
141
142--ZAD 2--
143create function f1
144(
145@id int
146)
147returns int
148as
149begin
150declare @broj int
151select @broj=count(*)
152from Proizvod
153inner join Potkategorija on Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
154inner join Kategorija on Potkategorija.KategorijaID = Kategorija.IDKategorija
155where KategorijaID = @id
156return
157case when @broj is null then 0
158else @broj
159end
160end
161
162select Naziv, dbo.f1(Kategorija.IDKategorija) as Broj 
163from Kategorija
164order by Broj desc
165
166
167--ZAD3--
168alter proc cud
169@op char(1),
170@id int output,
171@tip nvarchar(25),
172@broj nvarchar(25),
173@mj int,
174@god int
175as
176if @op = 'I'
177begin
178if not exists(
179select*
180from KreditnaKartica
181where KreditnaKartica.IDKreditnaKartica = @id
182)
183begin
184insert into KreditnaKartica
185values (@tip, @broj,@mj, @god)
186set @id = SCOPE_IDENTITY()
187end
188else
189
190update KreditnaKartica
191set Tip = @tip, Broj = @broj, IstekMjesec = @mj, IstekGodina = @god
192where KreditnaKartica.IDKreditnaKartica = @id
193end
194if @op = 'D'
195begin
196delete from KreditnaKartica
197where KreditnaKartica.IDKreditnaKartica = @id
198end
199
200create function Ret
201(
202@id int
203)
204returns table
205as
206return 
207select*
208from KreditnaKartica
209where KreditnaKartica.IDKreditnaKartica = @id
210
211select*from KreditnaKartica
212
213declare @r int
214exec cud 'I', @r output, 'VISA', 12312312311, 1 ,1
215print @r
216
217declare @r int
218exec cud 'I', 19252, 'VISA', 123223, 1 ,2
219print @r
220
221select*from Ret(19252)
222
223exec cud 'D', 19252, 'VISA', 123223, 12 ,12
224
225
226
227---ISHOD 5---
228---ZAD 1---
229
230create table Zapisnik
231(
232IDZapisnik int primary key identity,
233Sadrzaj nvarchar(50),
234vrijeme datetime default getdate()
235)
236
237create trigger o1 on Drzava after insert, update, delete
238as
239declare @id int
240if exists(select* from inserted) and not exists(select*from deleted)
241begin
242select @id=IDDrzava from inserted
243insert into Zapisnik (Sadrzaj) values ('Umetnut je novi redak s IDDrzava: ' + cast(@id as nvarchar(10)))
244end
245if exists(select* from deleted) and not exists(select*from inserted)
246begin
247select @id=IDDrzava from deleted
248insert into Zapisnik (Sadrzaj) values ('Obrisan je redak s IDDrzava: ' + cast(@id as nvarchar(10)))
249end
250if exists(select* from deleted) and exists(select*from inserted)
251begin
252select @id=IDDrzava from deleted
253insert into Zapisnik (Sadrzaj) values ('Promijenjen je redak s IDDrzava: ' + cast(@id as nvarchar(10)))
254end
255
256insert into Drzava (Naziv) values ('Kina')
257select*from Zapisnik
258select*from Drzava
259update Drzava
260set Naziv = 'Japan'
261where IDDrzava = 1
262delete from Drzava where IDDrzava = 27
263
264drop trigger o1
265
266
267--ZAD--2
268
269alter trigger o2 on KreditnaKartica after update
270as
271declare @br nvarchar(25)
272declare @oldm int
273declare @oldg int
274declare @newm int 
275declare @newg int
276
277if(
278select count(*)
279from inserted) > 1
280begin
281insert into Zapisnik(Sadrzaj) values ('Promijenjeno više kartica')
282end
283else
284begin
285select @br = Broj 
286from inserted
287select @oldm = IstekGodina, @oldg = IstekGodina 
288from deleted
289select @newm = IstekGodina, @newg = IstekGodina 
290from inserted
291insert into Zapisnik(Sadrzaj) values
292('Promijenjeni podaci kartice ' + cast(@br as nvarchar(20)) + '. Stari trajanje ' + 
293cast(@oldm as nvarchar(10)) + '/' + cast(@oldg as nvarchar(10)) + ', novo trajanje je ' +
294cast(@newm as nvarchar(10)) + '/' + cast(@newg as nvarchar(10)) + '.') 
295end
296
297select*from Zapisnik
298select*from KreditnaKartica
299
300update KreditnaKartica
301set IstekGodina = 10, IstekMjesec = 10
302where IDKreditnaKartica = 1 and IDKreditnaKartica = 2
303
304drop trigger o2
305
306--ISHOD 6--
307--ZAD1--
308create proc proc3
309@xml xml
310as
311declare @d nvarchar(55)
312declare @id int
313declare @idg int
314SELECT @d=t.s.value('@Naziv', 'nvarchar(50)')
315FROM @xml.nodes('/Drzava') AS t(s)
316print @d
317if exists(select * from Drzava where Naziv=@d)
318begin
319set @id=(select IDDrzava from Drzava where Naziv=@d)
320insert into grad(DrzavaID,Naziv)
321SELECT @id,t.s.value('.', 'nvarchar(50)')FROM @xml.nodes('/Drzava/Grad') AS t(s)
322
323end
324else 
325begin
326insert into Drzava (Naziv)values(@d)
327set @id=SCOPE_IDENTITY()
328insert into grad(DrzavaID,Naziv)
329SELECT @id,t.s.value('.', 'nvarchar(50)')FROM @xml.nodes('/Drzava/Grad') AS t(s)
330end
331
332exec proc3'<Drzava Naziv="Hrvatska">
333<Grad>Bjelovar</Grad>
334<Grad>Vinkovci</Grad>
335<Grad>Koprivnica</Grad>
336</Drzava>'
337
338select * from Drzava
339select * from Grad
340
341-- zadatak 3 -- procedura iz xml-a drzavi (koju stvori ako ne postoji) doda gradove
342
343GO
344CREATE PROCEDURE dbo.DrzavaGradXMLInsert
345@data xml
346AS
347DECLARE @Drzava nvarchar(50)
348DECLARE @idDrzava int
349SELECT
350@Drzava = Tablica.Stupac.value('@Naziv','nvarchar(50)')
351FROM @data.nodes('/Drzava') AS Tablica(Stupac)
352IF NOT EXISTS (SELECT IDDrzava FROM Drzava WHERE Naziv = @Drzava) BEGIN
353INSERT INTO Drzava (Naziv)
354VALUES (@Drzava)
355SET @idDrzava = SCOPE_IDENTITY()
356END
357ELSE BEGIN
358SELECT @idDrzava = IDDrzava FROM Drzava WHERE Naziv = @Drzava
359END
360INSERT INTO Grad (Naziv, DrzavaID)
361SELECT
362Tablica.Stupac.value('.', 'nvarchar(50)'),
363@idDrzava
364FROM @data.nodes('/Drzava/Grad') AS Tablica(Stupac)
365
366-- test
367GO
368DECLARE @data XML = '
369<Drzava Naziv="Hrvatska">
370<Grad>Bjelovar</Grad>
371<Grad>Vinkovci</Grad>
372<Grad>Koprivnica</Grad>
373</Drzava>'
374
375SELECT * FROM Drzava AS d INNER JOIN Grad AS g ON g.DrzavaID = d.IDDrzava ORDER BY d.Naziv
376EXECUTE dbo.DrzavaGradXMLInsert @data
377SELECT * FROM Drzava AS d INNER JOIN Grad AS g ON g.DrzavaID = d.IDDrzava ORDER BY d.Naziv
378
379
380-- zadatak 4 -- procedura od tablicnog tipa podataka umece n proizvoda
381-- nije nista posebno pisalo pa je jednostavna procedura, tj proizvodi su bez potkategorije (null je stupac)
382
383CREATE TYPE Product AS TABLE
384(
385Naziv nvarchar(50),
386BrojProizvoda nvarchar(25),
387Boja nvarchar(15), 
388MinimalnaKolicinaNaSkladistu smallint,
389CijenaBezPDV money,
390PotkategorijaID nvarchar(50)
391)
392GO
393
394CREATE PROCEDURE dbo.ProductUDTInsert
395@data Product READONLY
396AS
397INSERT INTO Proizvod (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV)
398SELECT
399d.Naziv,
400d.BrojProizvoda,
401d.Boja,
402d.MinimalnaKolicinaNaSkladistu,
403d.CijenaBezPDV
404FROM @data AS d
405
406GO
407SELECT * FROM Potkategorija
408
409-- test
410DECLARE @data Product 
411INSERT INTO @data (Naziv, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV)
412VALUES('Test1','T001','Zelena',10,120),
413('Test2','T002','Zelena',10,120),
414('Test3','T003','Zelena',10,120),
415('Test4','T004','Zelena',10,120),
416('Test5','T005','Zelena',10,120)
417
418SELECT * FROM Proizvod WHERE PotkategorijaID IS NULL
419EXECUTE dbo.ProductUDTInsert @data
420SELECT * FROM Proizvod WHERE PotkategorijaID IS NULL
421
422
423-- ISHOD 7 ----------------------------------------
424-- zadatak 5 -- procedura prima id kartice, brise nju i sve stavke / racune
425-- transakcija van procedure zove proceduru 3 puta s 3 razl. id-a, ispisat uspjeh/neuspjeh
426GO
427CREATE PROCEDURE dbo.DeleteCreditCard
428@CardID int
429AS
430IF EXISTS (SELECT IDKreditnaKartica FROM KreditnaKartica WHERE IDKreditnaKartica = @CardID) BEGIN
431IF EXISTS (SELECT IDStavka FROM Stavka WHERE RacunID IN (SELECT IDRacun FROM Racun WHERE KreditnaKarticaID = @CardID)) BEGIN
432DELETE FROM Stavka
433WHERE RacunID IN (SELECT IDRacun FROM Racun WHERE KreditnaKarticaID = @CardID)
434DELETE FROM Racun
435WHERE IDRacun IN (SELECT IDRacun FROM Racun WHERE KreditnaKarticaID = @CardID)
436END
437DELETE FROM KreditnaKartica WHERE IDKreditnaKartica = @CardID
438SELECT 'Credit card deleted!'
439END
440ELSE SELECT 'No such credit card!'
441
442GO
443BEGIN TRANSACTION
444BEGIN TRY
445EXECUTE dbo.DeleteCreditCard 1
446EXECUTE dbo.DeleteCreditCard 2
447EXECUTE dbo.DeleteCreditCard 3
448SELECT 'Transaction SUCCESSFUL'
449COMMIT TRANSACTION
450END TRY
451BEGIN CATCH
452SELECT 'Transaction FAILED'
453SELECT ERROR_MESSAGE()
454IF @@TRANCOUNT > 0 BEGIN
455ROLLBACK TRANSACTION
456END
457END CATCH
458
459-- zadatak 6 -- opisite + demo na tablici Drzava za prljavo citanje, kako sprjeciti
460
461-- PRLJAVO CITANJE - Tran#1 prtlja po nekim podacima. Usred tog prtljanja, prije nego tran#1 napravi COMMIT ili ROLLBACK,
462-- tran#2 cita te podatke, kakvi jesu u tom trenu prtljanja. Moguce je da tran#1 na kraju opet nesto promijeni ili odustane
463-- od zivota, sto ce reci da ti podaci koje je tran#2 procitala nisu nikad bili tocni.
464
465-- KORAK 1
466SET TRANSACTION ISOLATION LEVEL READ COMMITTED
467-- KORAK 3
468BEGIN TRAN
469-- KORAK 5
470UPDATE Drzava
471SET Naziv = 'Croatia'
472WHERE IDDrzava = 1
473-- KORAK 7
474ROLLBACK
475
476-- 2. KONEKCIJA -- ovaj dio treba stavit u ZASEBNU KONEKCIJU (new query pa kopi pejst ovih parnih koraka)
477-- KORAK 2
478SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
479-- KORAK 4
480BEGIN TRAN
481-- KOARK 6
482SELECT *
483FROM Drzava
484WHERE IDDrzava = 1
485-- KORAK 8
486ROLLBACK
487
488-- RJESENJE - Za izbjegavanje mogucnosti pojave prljavog citanja, potrebno je konekcije drzati barem na izolacijskom nivou
489-- READ COMMITTED, ili na jos visim razinama (REPEATABLE READ ili SERIALIZABLE)
490
491
492
493
494--ISHOD8--
495
496--1
497select Potkategorija.Naziv,Proizvod.Naziv,sum(Stavka.UkupnaCijena) as 'ukupno'
498from Stavka
499inner join Proizvod on Proizvod.IDProizvod=Stavka.ProizvodID
500inner join Potkategorija on Potkategorija.IDPotkategorija=Proizvod.PotkategorijaID
501group by cube(Potkategorija.Naziv,Proizvod.Naziv)
502
503--2
504select d.Naziv as 'drzava',g.Naziv as 'grad',count(*) as 'ukupno',
505rank() over(order by count(k.IDKupac) desc) as 'rang'
506from Kupac as k
507inner join Grad as g on g.IDGrad = k.GradID
508inner join Drzava as d on d.IDDrzava=g.DrzavaID
509group by grouping sets(d.Naziv,g.Naziv)
510
511
512--3
513select Proizvod.Naziv, Proizvod.CijenaBezPDV / (AVG(Proizvod.CijenaBezPDV) over ()) as 'postotni udio'
514from Proizvod
515inner join Stavka on Stavka.ProizvodID=Proizvod.IDProizvod
516where Proizvod.CijenaBezPDV > 0
517
518--4
519select Proizvod.Naziv,Proizvod.CijenaBezPDV, CijenaBezPDV - lag(CijenaBezPDV,1) over (order by CijenaBezPDV) as 'razlika'
520from Proizvod
521order by CijenaBezPDV asc
522
523--zad1
524
525select p.Naziv as proizvod,pp.Naziv,sum(s.UkupnaCijena) as podkategorija from Stavka as s
526inner join Proizvod as p on p.IDProizvod=s.ProizvodID
527inner join Potkategorija as pp on p.PotkategorijaID=pp.IDPotkategorija
528group by grouping sets (p.Naziv, pp.Naziv, ())
529
530
531--zad2
532
533select g.Naziv as grad,d.Naziv as drzava,
534RANK() over (partition by g.Naziv,d.Naziv order by count(*)) as Ranking
535from kupac as k
536inner join Grad as g on k.GradID=g.IDGrad
537inner join Drzava as d on g.DrzavaID=d.IDDrzava
538group by grouping sets (g.Naziv, d.Naziv)
539
540--zad3
541select p.Naziv,p.CijenaBezPDV/(avg(p.CijenaBezPDV)over())as postotniudio from Proizvod as p
542inner join stavka as s on p.IDProizvod=s.ProizvodID
543where p.CijenaBezPDV > 0
544
545
546-- Zadatak 23.
547/*
548SLOŽENE TABLIÈNE FUNKCIJE
549Napišite složenu tabliènu funkciju koja prima jedan datum i koja vraæa tablicu koja se sastoji od jednog stupca i koja sadržava sljedeæih 5 datuma. 
550Primjerice, ako je zadan 03.12.2011, funkcija treba vratiti 04.12., 05.12, 06.12, 07.12, 08.12.
551*/
552CREATE FUNCTION GetDatume
553(
554@Datum datetime
555)
556RETURNS @RetVal TABLE ( Datum datetime )
557AS
558BEGIN
559DECLARE @i int = 1
560WHILE @i <= 5 BEGIN
561INSERT INTO @RetVal (Datum)
562VALUES (Dateadd(day, @i, @Datum))
563
564SET @i += 1
565END
566
567RETURN
568END
569GO
570
571SELECT * FROM GetDatume(GETDATE())
572SELECT * FROM GetDatume('20111229')
573GO
574-----------------------------------------------------------------------------------
575-----------------------------------------------------------------------------------
576
577-- Zadatak 12.
578/*
579NAREDBA CASE I WHILE
580Dohvatite naziv i cijene svih proizvoda. 
581Za cijene koje su ispod 1000, napišite “Jeftino”, izmeðu 1000 i 2000 
582napišite “Prihvatljivo”, za sve ostale napišite “Skupo”.
583*/
584SELECT 
585Naziv,
586CASE
587WHEN CijenaBezPDV < 1000 THEN 'Jeftino'
588WHEN CijenaBezPDV BETWEEN 1000 AND 2000 THEN 'Prihvatljivo'
589ELSE 'Skupo'
590END AS Procjena
591FROM Proizvod
592GO
593------
594
595
596
597-- Zadatak 5.
598/*
599CRUD OPERACIJE
600Svaka operacija posebno.
601Napravite procedure koje rade CRUD operacije 
602na tablici Student tako da svakoj operaciji 
603dodijelite posebnu proceduru. 
604Iskoristite procedure za umetanje, izmjenu,
605dohvaæanje i brisanje zapisa.
606*/
607
608CREATE PROC InsertStudent
609@IDStudent int OUTPUT, 
610@Ime nvarchar(50), 
611@Prezime nvarchar(50), 
612@JMBAG char(11)
613AS
614INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
615SET @IDStudent = SCOPE_IDENTITY()
616GO
617
618CREATE PROC UpdateStudent
619@IDStudent int, 
620@Ime nvarchar(50), 
621@Prezime nvarchar(50), 
622@JMBAG char(11)
623AS
624UPDATE Student 
625SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
626WHERE IDStudent = @IDStudent
627GO
628
629CREATE PROC DeleteStudent
630@IDStudent int
631AS
632DELETE FROM Student WHERE IDStudent = @IDStudent
633GO
634
635CREATE PROC GetStudent
636@IDStudent int
637AS
638SELECT * FROM Student WHERE IDStudent = @IDStudent
639GO
640
641EXEC GetStudent 1
642
643DECLARE @NoviIDStudenta int
644EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT, 
645@Ime = 'Ana', @Prezime = 'Aniæ', @JMBAG = '11224451253'
646PRINT @NoviIDStudenta
647
648EXEC GetStudent 1
649
650EXEC UpdateStudent 1, 'Ana', 'Aniæ Miriæ', '11224451253'
651
652EXEC GetStudent 1
653
654EXEC DeleteStudent 1
655
656EXEC GetStudent 1
657GO
658-----------------------------------------------------------------------------------
659-- Zadatak 6.
660/*
661CRUD OPERACIJE
662INSERT/UPDATE zajedno, ostalo posebno.
663Napravite procedure koje rade CRUD operacije na tablici Student 
664tako da operacije umetanja i izmjene obavite u jednoj proceduri, 
665a druge dvije operacije obavite u posebnim procedurama. 
666Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
667*/
668CREATE PROC MergeStudent
669@IDStudent int OUTPUT, 
670@Ime nvarchar(50), 
671@Prezime nvarchar(50), 
672@JMBAG char(11)
673AS
674IF Exists(SELECT * FROM Student WHERE IDStudent = @IDStudent)
675UPDATE Student 
676SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
677WHERE IDStudent = @IDStudent
678ELSE
679INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
680SET @IDStudent = SCOPE_IDENTITY()
681GO
682
683CREATE PROC DeleteStudent
684@IDStudent int
685AS
686DELETE FROM Student WHERE IDStudent = @IDStudent
687GO
688
689CREATE PROC GetStudent
690@IDStudent int
691AS
692SELECT * FROM Student WHERE IDStudent = @IDStudent
693GO
694
695EXEC GetStudent 1
696
697DECLARE @NoviIDStudenta int
698EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
699PRINT @NoviIDStudenta
700
701EXEC GetStudent 2
702
703EXEC MergeStudent 2, 'Ana', 'Aniæ Miriæ', '11224451253'
704
705EXEC GetStudent 2
706
707EXEC DeleteStudent 2
708
709EXEC GetStudent 2
710GO
711-----------------------------------------------------------------------------------
712-- Zadatak 7.
713/*
714CRUD OPERACIJE
715UPDATE/INSERT/DELETE zajedno.
716Napravite procedure koje rade CRUD operacije na tablici Student 
717tako da operacije umetanja, izmjene i brisanja obavite u jednoj proceduri, 
718a dohvaæanje u drugoj. 
719Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
720*/
721CREATE PROC ChangeStudent
722@Operacija char(1),
723@IDStudent int OUTPUT, 
724@Ime nvarchar(50), 
725@Prezime nvarchar(50), 
726@JMBAG char(11)
727AS
728IF @Operacija = 'U'
729UPDATE Student 
730SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
731WHERE IDStudent = @IDStudent
732ELSE IF @Operacija = 'I' BEGIN
733INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
734SET @IDStudent = SCOPE_IDENTITY()
735END
736ELSE IF @Operacija = 'D'
737DELETE FROM Student WHERE IDStudent = @IDStudent
738GO
739
740CREATE PROC GetStudent
741@IDStudent int
742AS
743SELECT * FROM Student WHERE IDStudent = @IDStudent
744GO
745
746EXEC GetStudent 3
747
748DECLARE @NoviIDStudenta int
749EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
750PRINT @NoviIDStudenta
751
752EXEC GetStudent 3
753
754EXEC ChangeStudent 'U', 3, 'Ana', 'Aniæ Miriæ', '11224451253'
755
756EXEC GetStudent 3
757
758EXEC ChangeStudent 'D', 3, null, null, null
759
760EXEC GetStudent 3
761GO