USE AdventureWorksOBP

CREATE PROC pDrzave 
AS 
	SELECT * FROM Drzava

DROP PROC pProizvod

EXEC pProizvod @idp=420

CREATE OR ALTER PROC pProizvod
	@idp INT
AS
	SELECT * FROM Proizvod
	WHERE IDProizvod=@idp


CREATE OR ALTER PROC pProizvodBoja 
	@idp INT,
	@boja NVARCHAR(15) OUTPUT
AS
	SELECT @boja=p.Boja FROM Proizvod as p
	WHERE p.IDProizvod = @idp

DECLARE @farba NVARCHAR(15)
EXEC pProizvodBoja 317, @farba OUTPUT
PRINT @farba


CREATE OR ALTER PROC pOsobe
	@ido INT
AS
	IF EXISTS (SELECT * FROM Kupac WHERE IDKupac=@ido)
		BEGIN
			RETURN 0
		END
	ELSE
		BEGIN
			RETURN 2
		END
DECLARE @retVal INT
EXEC @retVal=pOsobe 1
PRINT @retVal

CREATE OR ALTER PROC pPrint
AS
	SELECT TOP 5 *
	FROM Racun as r
	SELECT TOP 5 *
	FROM Proizvod as p
	SELECT TOP 5 *
	FROM Stavka as s

CREATE OR ALTER PROC pZadatak
	@nazivd NVARCHAR(50),
	@nazivg NVARCHAR(50)
AS
	IF NOT EXISTS (SELECT * FROM Drzava as d WHERE d.Naziv=@nazivd)
		BEGIN
			INSERT INTO Drzava(Naziv) VALUES (@nazivd)
		END
	DECLARE @iddrzave INT
	SET @iddrzave = (SELECT d.IDDrzava FROM Drzava as d WHERE d.Naziv = @nazivd)
	INSERT INTO Grad(Naziv, DrzavaID) VALUES (@nazivg, @iddrzave)

CREATE OR ALTER PROC pNovo
	@naziv NVARCHAR(50),
	@identityp INT OUTPUT
AS
	DECLARE @id INT
	SELECT @id=k.IDKategorija FROM Kategorija as k WHERE k.Naziv = @naziv
	IF @id IS NULL
		BEGIN
			INSERT INTO Kategorija(Naziv) VALUES (@naziv)
			SET @identityp = SCOPE_IDENTITY()
		END
	ELSE
		BEGIN
			RETURN -1
		END