-- a) Vytvorte tabulku T1 s 3 alebo 20 (maximalne 30 !!!) stlpcami. DECLARE @sql NVARCHAR(MAX), @i INT, @n INT, @prem NVARCHAR(MAX) SET @n = 20; 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; ALTER 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 IF OBJECT_ID ( 'Usp_1', 'P' ) IS NOT NULL DROP PROCEDURE Usp_1; GO ---- b1) SP bez parametrov: ---- 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; -- No ()!? GO ---- b3a) SP S IN & OUT parametrami: ---- USE OsobaVztah; GO 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) -- cislo iba 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'; -- 1) Najdime minimum dvoch cisel c1, c2 pomocou funkcie! IF OBJECT_ID('f_mini', 'FN') IS NOT NULL DROP FUNCTION f_mini; GO CREATE FUNCTION f_mini( @c1 int, @c2 int ) RETURNS int AS BEGIN DECLARE @mini int; if @c1 < @c2 set @mini = @c1 else set @mini = @c2 RETURN @mini END GO SELECT dbo.f_mini(1, 2) -- dbo. !!!!!!!!!!!!!!! SELECT dbo.f_mini(11, 2) -- dbo. !!!!!!!!!!!!!!! -- 2) Vytvorenie dvoch stlpcov s mini a maxi! select min(x) mini, max(x) maxi from ( select 2 x union select 11 x ) jaj -- 3) Najdime minimum a maximum dvoch cisel c1, c2 pomocou funkcie! IF OBJECT_ID('f_minimaxi', 'IF') IS NOT NULL DROP FUNCTION f_minimaxi; GO CREATE FUNCTION f_minimaxi( @c1 int, @c2 int ) RETURNS Table AS ----BEGIN RETURN( select min(x) mini, max(x) maxi from ( select @c1 x union select @c2 x ) jaj ) ----END GO SELECT * FROM f_minimaxi(22,6); ---- !!! -- 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