Rapasoft.eu

/*
-- pre kazdu osobu chceme najst cestu pred3 / pred2 / pred1 / osoba
-- WITH nam vrati cestu predkov (pred3 az pred1)

use osobaVztah;

WITH cestaPredkov(id, cesta)
AS 
(
	SELECT id, CAST('' AS VARCHAR(90)) FROM osoba WHERE otec IS NULL
	UNION ALL
	SELECT o.id,CAST(c.cesta + ' / ' + o2.Priezvisko + ' ' + o2.meno AS VARCHAR(90)) FROM osoba o 
		JOIN cestaPredkov c ON c.id = o.otec
		JOIN osoba o2 ON c.id = o2.id
)
SELECT cp.cesta+' / '+o.meno+' '+o.priezvisko FROM cestaPredkov cp
	JOIN osoba o on cp.id = o.id
ORDER BY cp.id;
--/////////////////////////////////////////////////////////////////////////
--OHRANICENIE ITERACNEHO KROKU

WITH post(k) AS
(
	SELECT 1
		UNION ALL
	SELECT k+1 FROM post
	 WHERE k < 32768 --maximalne je 100, pre 102 uz hadze chybu, treba nastavit toto nizsie
)	
--SELECT * FROM post --toto bez max recursion neprejde

-- mozeme nastavit MAXRECURSION
SELECT * FROM post
OPTION(MAXRECURSION 32767) --, hodnota od 0 - 32767

--/////////////////////////////////////////////////////////////////////////

--DOCASNE TABULKY
-- #, ## tabulky - su ulozene na HDD
-- @ - premenne tabulky - existuju len v pamati

-- a) obycajna tabulka
CREATE TABLE A(X int)
-- b) # - lokalna tabulka - rozpoznava len vramci query sheetu
CREATE TABLE #A(X int)
-- c) ## - globalna tabulka
CREATE TABLE ##A(X int)
-- d) premenna
DECLARE A TABLE (X int)  

USE tempdb
GO

IF OBJECT_ID('#A') IS NOT NULL DROP TABLE #A
GO

CREATE TABLE #A(x int)
GO

INSERT #A VALUES(1)
INSERT #A VALUES(2)
INSERT #A VALUES(3)

SELECT x FROM #A

DECLARE @B TABLE (X int)
INSERT @B VALUES(10)
INSERT @B VALUES(20)
INSERT @B VALUES(30)

SELECT * FROM @B

--/////////////////////////////////////////////////////////////////////////
-- kurzory + pouzitie
-- skalarne funkcie typu f: @@FETCH_STATUS, @@CURSOR_ROW
-- pozn.: @@ sa zacinaju systemove premenne

-- deklaracia
DECLARE kur1 CURSOR
[*] ----> [FORWARD_ONLY, SCROLL] (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)
FOR
SELECT ...
-- otvorenie + pouzitie

--zatvorenie, ulozenie
*/
--priklad:
--1/3:
DECLARE kurDB CURSOR
FORWARD_ONLY 
FOR
SELECT name FROM master.dbo.sysdatabases
 WHERE name NOT IN('master','model','msdb')
--2/3:
DECLARE @nameDB VARCHAR(100)
DECLARE @k INT
SET @k = 0

OPEN kurDB
FETCH NEXT FROM kurDB INTO @nameDB
print @nameDB + ';' + CAST(@@CURSOR_ROWS AS CHAR(10))

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @k = @k + 1
	print CAST(@k AS CHAR(5)) + ' '+ @nameDB
	FETCH NEXT FROM kurDB INTO @nameDB	
END
--3/3:
CLOSE kurDB
DEALLOCATE kurDB