MS Sql -Procedure. Dynamic sql

[ MS Sql -Procedure. Dynamic sql ]

 

1. 방법1

select @sql = 'select a,b from rich'; execute immediate(@sql); 또는 EXEC @sql;

 

2. 방법2

SET @tablename = 'sample' SET @execquery = N'SELECT * FROM ' + QUOTENAME(@tablename) EXECUTE sp_executesql @execquery;

 

3. 방법3

SET @strSQL = 'SELECT @MAX_EMP_NO = MAX(EMP_NO) FROM T_EMP WHERE DEPT_CD = @DEPT_CD'; EXEC SP_EXECUTESQL @strSQL, N'@DEPT_CD NVARCHAR(10), @MAX_EMP_NO NVARCHAR(10) OUTPUT' , @DEPT_CD = @DEPT_CD , @MAX_EMP_NO = @MAX_EMP_NO OUTPUT;