Skrypt T-SQLa tworzący obiekty w bazie danych i wykonujący kilka operacji testowych.

/*
 Projekt
*/
CREATE DATABASE Projekty
GO
USE Projekty
GO

/*
Tworzenie tabel w bazie danych
*/
CREATE TABLE Projekt
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  kod   VARCHAR(20),
  nazwa VARCHAR(200),
  status INT
)

CREATE TABLE Zadanie
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  idProjekt  INT,
  idPracownik INT,
  nazwa VARCHAR(200),
  priorytet INT,
  status INT
)

CREATE TABLE Pracownik
(
  id    INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  imie VARCHAR(40),
  nazwisko VARCHAR(40),
  stanowisko VARCHAR(80)
)

CREATE TABLE StatusProjektu
(
  id    INT,
  nazwa VARCHAR(50)
)

CREATE TABLE PriorytetZadania
(
  id    INT,
  nazwa VARCHAR(50)
)

GO

/*
Uzupelnienie tabel danymi
*/

INSERT INTO Pracownik (imie, nazwisko, stanowisko) VALUES ('Jan', 'Kowalski', 'specjalista');
INSERT INTO Pracownik (imie, nazwisko, stanowisko) VALUES ('Ewa', 'Nowak', 'referentka');
GO

INSERT INTO StatusProjektu VALUES (1, 'rozpoczety')
INSERT INTO StatusProjektu VALUES (2, 'realizowany')
INSERT INTO StatusProjektu VALUES (3, 'zakonczony')
GO

INSERT INTO PriorytetZadania VALUES (1, 'wysoki')
INSERT INTO PriorytetZadania VALUES (2, 'sredni')
INSERT INTO PriorytetZadania VALUES (3, 'niski')
GO

INSERT INTO Projekt (kod, nazwa, status) VALUES ('BAZA', 'projekt bazy danych', 1)
INSERT INTO Projekt (kod, nazwa, status) VALUES ('SQL', 'skrypt sql', 2)
GO

/*
Procedura dodawania zadania do projektu
*/
CREATE PROCEDURE DodajZadanie
  @kodProjektu VARCHAR(20),
  @idPracownika INT,
  @nazwaZadania VARCHAR(200),
  @priorytetZadania INT
AS
BEGIN
  DECLARE @idProjektu INT
  
  -- wyszukanie id projektu
  SELECT @idProjektu = id FROM Projekt WHERE kod = @kodProjektu
  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    -- dodanie zadania status = rozpoczety
    INSERT INTO Zadanie (idProjekt, idPracownik, nazwa, priorytet)
      VALUES (@idProjektu, @idPracownika, @nazwaZadania, @priorytetZadania)
  END
  ELSE
  BEGIN
    RETURN(0)
  END
END
GO

/*
Procedura zmiany statusu projektu o podanym kodzie
*/
CREATE PROCEDURE ZmienStatusProjektu
  @kodProjektu VARCHAR(20),
  @nowyStatus INT
AS
BEGIN
  DECLARE @idProjektu INT
  
  -- wyszukanie id projektu
  SELECT @idProjektu = id FROM Projekt WHERE kod = @kodProjektu
  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    UPDATE Projekt SET status = @nowyStatus WHERE id = @idProjektu
  END
  ELSE
  BEGIN
    RETURN(0)
  END
END
GO

/*
Procedura usuwanie projektu o podanym kodzie (transakcja)
*/
CREATE PROCEDURE UsunProjekt
  @kodProjektu VARCHAR(20)
AS
BEGIN
  DECLARE @idProjektu INT
  
  -- wyszukanie id projektu
  SELECT @idProjektu = id FROM Projekt WHERE kod = @kodProjektu
  IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
  BEGIN
    BEGIN TRANSACTION

    DELETE FROM Projekt WHERE id = @idProjektu
    IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
    BEGIN
      DELETE FROM Zadanie WHERE idProjekt = @idProjektu
      IF ((@@ERROR = 0) AND ( @@ROWCOUNT > 0))
      BEGIN
        COMMIT TRANSACTION
      END
      ELSE
      BEGIN
        ROLLBACK TRANSACTION
      END
    END
    ELSE
    BEGIN
      ROLLBACK TRANSACTION
    END
  END
  ELSE
  BEGIN
    RETURN(0)
  END
END
GO

/*
Funkcja zwracajaca nazwe statusu projektu/zadania podanego w id
*/
CREATE FUNCTION PodajStatus(@idStatusu INT)
RETURNS VARCHAR(50)
AS
BEGIN
  DECLARE @nazwaStatusu  VARCHAR(50)

  SELECT @nazwaStatusu = nazwa FROM StatusProjektu WHERE id = @idStatusu
  RETURN (@nazwaStatusu)  
END
GO

/*
Funkcja zwracajaca nazwe priorytetu zadania podanego w id
*/
CREATE FUNCTION PodajPriorytet(@idPriorytet INT)
RETURNS VARCHAR(50)
AS
BEGIN
  DECLARE @nazwaPriorytetu  VARCHAR(50)

  SELECT @nazwaPriorytetu = nazwa FROM PriorytetZadania WHERE id = @idPriorytet
  RETURN (@nazwaPriorytetu)  
END
GO

/*
Po dodanie zadania automatyczne nasanie mu statusu=1, tzn. rozpoczety
*/
CREATE TRIGGER InicjujStatusZadania
ON Zadanie
AFTER INSERT
AS
  UPDATE Zadanie SET status = 1 WHERE id = (SELECT id FROM Inserted)
GO

/*
Widok listy projektow
*/
CREATE VIEW listaProjektow
AS  
  SELECT
    Projekt.kod,
    Projekt.nazwa,
    dbo.PodajStatus(status) AS statusNazwa
  FROM
    Projekt
GO

/*
Widok listy zadan 
*/
CREATE VIEW listaZadan
AS  
  SELECT
    Zadanie.nazwa,
    Pracownik.nazwisko,
    Pracownik.imie,
    dbo.PodajPriorytet(Zadanie.priorytet) AS priorytetNazwa,
    dbo.PodajStatus(Zadanie.status) AS statusNazwa
  FROM
    Zadanie, Pracownik
  WHERE
    Zadanie.idPracownik = Pracownik.id
GO

-- wywolanie procedur

EXECUTE DodajZadanie 'BAZA', 1, 'Projekt bazy danych', 1
GO
EXECUTE DodajZadanie 'BAZA', 2, 'Dokumentacja bazy danych', 3
GO
EXECUTE DodajZadanie 'SQL', 1, 'Procedura dodajaca zadanie', 1
GO
EXECUTE ZmienStatusProjektu 'BAZA', 3
GO

SELECT * FROM listaProjektow
SELECT * FROM listaZadan
GO

-- usuniecie projektu SQL

EXEC UsunProjekt 'SQL'
GO

SELECT * FROM listaProjektow
SELECT * FROM listaZadan
GO