-- Ulozeme procedury

/*
Vyhody
- modularne programovanie
- rychlejsi vypocet - kesovanie exekucneho planu
- redukcia vymen po sieti
- je to prostriedok na zvysenie bezpecnosti

Nevyhody
- limitovane programovanie - cykly
- portabilita - na druhy DBMS, napr. Oracle
*/

-- Systemove ulozeme procedury
EXEC sp_databases; -- SELECT * FROM sys.databases

USE OsobaVztah;
GO

EXEC sp_tables; -- SELECT * FROM sys.tables;
EXEC sp_tables @TABLE_OWNER = dbo;

EXEC sp_columns Osoba;
EXEC sp_columns @table_name = Osoba, @column_name = 'm%';

EXEC sp_helptext sp_columns;
EXEC sp_helptext sp_executesql;

-- Premenujme databazu Cisla na databazu CislaDB pomocou systemovej procedury
EXECUTE sp_renamedb 'Cisla','CislaDB';
EXECUTE sp_renamedb 'CislaDB','Cisla';

-- a) Vytvorte tabulku T1 s 3 alebo 20 (maximalne 30 !!!) stlpcami.

DECLARE @sql NVARCHAR(MAX), @i INT, @n INT, @prem NVARCHAR(MAX);
SET @n = 3;
SET @sql = N'CREATE TABLE T1(id INT NOT NULL IDENTITY PRIMARY KEY';
SET @i = 1;
WHILE @i <= @n
BEGIN
	SET @sql = @sql + N', col' + CAST(@i AS NVARCHAR(2)) + N' INT';
	SET @i = @i + 1;
END
SET @sql = @sql + N');';
PRINT @sql;
EXEC sp_executesql @sql;

CREATE PROCEDURE dbo.usp_create_table @n INT, @out NVARCHAR(MAX) OUT
AS
BEGIN
	IF OBJECT_ID('T1') IS NOT NULL 
        DROP TABLE T1;
	DECLARE @sql NVARCHAR(MAX), @i INT
	SET @sql = N'CREATE TABLE T1(id INT NOT NULL IDENTITY PRIMARY KEY';
	SET @i = 1;
	WHILE @i <= @n
		BEGIN
			SET @sql = @sql + N', col' + CAST(@i AS NVARCHAR(2)) + N' INT';
			SET @i = @i + 1;
		END
	SET @sql = @sql + N');';
	SET @out = @sql
END
GO

DECLARE @out NVARCHAR(MAX)
EXEC dbo.usp_create_table 5, @out OUTPUT;
print  @out
EXEC sp_executesql @out;

SELECT * FROM T1;

INSERT INTO T1 DEFAULT VALUES;

SELECT * FROM T1;

---- b) Uzivatelske ulozene procedury:
---- b1) SP bez parametrov
---- Maxi, mini:
---- b2) SP s IN @parametrom
---- b3) SP S IN & OUT parametrami
---- b4) SP ako "ciselny" vyraz - RETURN cislo
---- b5) Co pouziva/od coho zavisi object/SP

USE OsobaVztah;
GO

---- b1) SP bez parametrov:
----
IF OBJECT_ID ( 'usp_1', 'P' ) IS NOT NULL DROP PROCEDURE usp_1;
GO

CREATE PROCEDURE usp_1
AS
-- Begin
    SELECT * FROM Osoba
	where id BETWEEN 1 AND 4;
-- END
GO -- !!!

EXECUTE usp_1;
GO

--DECLARE @k int;
--EXECUTE @k = Usp_1;  SELECT @k;


---- b2) SP s IN parametrom
----

IF OBJECT_ID ( 'usp_2', 'P' ) IS NOT NULL DROP PROCEDURE usp_2;
GO

CREATE PROCEDURE usp_2
	@od int, @do int
--	(@od int, @do int) 
AS
    SELECT * FROM Osoba
	where id BETWEEN @od AND @do;
GO

EXECUTE usp_2 1, 4;
GO


---- b3a) SP S IN & OUT parametrami:
----

IF OBJECT_ID ( 'usp_3', 'P' ) IS NOT NULL DROP PROCEDURE usp_3;
GO

CREATE PROCEDURE usp_3
	@od int, @do int, @max numeric OUTPUT -- OUT
AS
    SET @max = (SELECT Max(Vyska) FROM Osoba
				WHERE id BETWEEN @od AND @do)
GO

Declare @max numeric
EXECUTE usp_3 1, 4, @max OUTPUT
SELECT @max

---- b3b) SP s jednym IN & dvomi OUT parametrami:
----
USE OsobaVztah;
GO

IF OBJECT_ID ( 'usp_3b', 'P' ) IS NOT NULL DROP PROCEDURE usp_3b;
GO

CREATE PROCEDURE usp_3b
	@od int, @do int, @min numeric output, @max numeric OUTPUT -- OUT
AS
    SET @max = (SELECT Max(Vyska) FROM Osoba
				WHERE id BETWEEN @od AND @do)
    SET @min = (SELECT Min(Vyska) FROM Osoba
				WHERE id BETWEEN @od AND @do)
GO

Declare @max numeric, @min numeric
EXECUTE Usp_3b 1, 4, @max OUTPUT, @min OUTPUT
SELECT @min, @max

---- b4) SP ako "ciselny" vyraz - RETURN cislo
----
IF OBJECT_ID ( 'usp_4', 'P' ) IS NOT NULL DROP PROCEDURE usp_4;
GO

CREATE PROCEDURE usp_4
	@od int, @do int, @max numeric OUTPUT
AS
    SET @max = (SELECT Max(Vyska) FROM Osoba
				  WHERE id BETWEEN @od AND @do)
	RETURN	   (SELECT MIN(Vyska) FROM Osoba
				  WHERE id BETWEEN @od AND @do)
GO

DECLARE @max numeric, @min numeric

EXECUTE @min = Usp_4 1, 4, @max OUTPUT
SELECT @max Maxi, @min Mini

EXECUTE Usp_4 1, 4, @max OUTPUT
SELECT @max M


---- b5) Co pouziva/Od coho zavisi Usp_4:
----
EXEC sp_depends @objname = 'usp_4';

---- !!!
-- Priklad. See in HELP: B. Creating an inline table-valued function

------ http://www.sqlservercentral.com/articles/Stored+Procedures/usingparameterswithstoredprocedures/2004/

IF OBJECT_ID ( 'usp_suma', 'P' ) IS NOT NULL DROP PROCEDURE usp_suma;
GO

CREATE PROC usp_suma 
	@x int = 5, @y int, @z varchar(30) OUTPUT 
--	(@x int = 5, @y int, @z varchar(30) OUTPUT)
as
begin
   Declare @sum int
   Set @sum = @x + @y
   Set @z = 'Suma je ' + cast( @sum as varchar(30))
   Return @sum
end
GO

Declare @a int, @b int, @c int, @cs varchar(30)
----SELECT @a = 1, @b = 2 -- <=>
SET @a = 1
SET @b = 2
Exec usp_suma @a, @b, @cs OUTPUT
SELECT @cs

Exec @c = usp_suma @a, @b, @cs OUTPUT
print @c
print @cs

-- !!!
EXEC sp_helptext usp_suma