DYNAMIC SQL

------------------------DYNAMIC SQL-------------------

CREATE PROC usp_DYNAMIC(@empno int=null,@ename varchar(20)=null,@job varchar(10)=null,@mgr int=null)

as

BEGIN

DECLARE @SQL NVARCHAR(MAX)='SELECT * FROM EMP WHERE 1=1'

IF(@empno IS NOT NULL)

SET @SQL=@SQL+' and EMPNO='+CAST(@empno AS VARCHAR)

IF(@ename IS NOT NULL)

SET @SQL=@SQL+' OR ENAME='+CHAR(39)+@ename+CHAR(39)

IF(@job IS NOT NULL)

SET @SQL=@SQL+' OR JOB='+CHAR(39)+@job+CHAR(39)

IF(@mgr IS NOT NULL)

SET @SQL=@SQL+' OR MGR='+CAST(@MGR AS VARCHAR)

--print(@SQL)

EXEC(@SQL)

END

EXEC usp_DYNAMIC @empno=7369,@ename='ALLEN',@job='CLERK'

--backup databases

alter PROC backups 
as
BEGIN
DECLARE @DBNAME NVARCHAR(MAX)=''
DECLARE @DATE DATETIME=''
SET @DATE=GETDATE()
DECLARE BACK CURSOR
FOR SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID>4
OPEN BACK
FETCH NEXT FROM BACK INTO @DBNAME 
DECLARE @FULLNAME NVARCHAR(500)=''
DECLARE @SQL NVARCHAR(MAX)=''
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQL=('BACKUP DATABASE '+@DBNAME+' TO DISK=''D:\BACKUP\'+CAST(@DATE AS VARCHAR(100))+'.BAK''')
PRINT(@SQL)
FETCH NEXT FROM BACK INTO @DBNAME
END
CLOSE BACK
DEALLOCATE BACK
END

EXEC backups

Recursive CTE

-------------------------Recursive CTE--------------------

SELECT * INTO #TEMP FROM EMP WHERE 1=2

DECLARE @mgr int

SELECT @mgr=mgr FROM EMP WHERE empno=7369

WHILE(@mgr IS NOT NULL)

BEGIN

INSERT INTO #TEMP SELECT * FROM EMP WHERE empno=@mgr

SELECT @mgr=mgr FROM EMP WHERE empno=@mgr

END

SELECT * FROM #TEMP

-------------------------------------------

;WITH X AS

( 

SELECT * FROM EMP WHERE EMPNO=7839

UNION ALL

SELECT E.* FROM X INNER JOIN EMP E ON X.EMPNO=E.MGR

)

SELECT * FROM X