저장 프로시저

1. PROCEDURE

	create procedure 프로시저 이름
	as
		 SQL ...
프로시저 실행
execute(exec) 프로시저 명;

CREATE PROCEDURE pr_1
	@empName NVARCHAR(10)
AS
	SELECT *
	  FROM employee 
	WHERE empName = @empName;

EXEC pr_1 '김관우';

2. 입력 매개변수

CREATE PROCEDURE pr_2
	@salary INT,
	@hireDate DATE
AS 
  SELECT *
    FROM employee
  WHERE salary >@salary 
	  AND hireDate > @hireDate;

EXEC pr_2 400, '1999/01/01';
EXEC pr_2 400, '1999/03/01'
EXEC pr_2 @hireDate = '2004/01/01', @salary = 500 ;

3. 입력매개변수에 DEFAULT 지정

CREATE PROCEDURE pr_3
	@salary INT = 450,
	@hireDate DATE = '2004/01/01'
AS
 SELECT *
   FROM employee
 WHERE salary > @salary 
	  AND hireDate > @hireDate

EXEC pr_3 500, '1999/01/01';

4. 현재 IDENTITY 값

CREATE PROCEDURE pr_4
	@text NCHAR(10),
	@outNum INT OUTPUT
AS
	INSERT pr_Tbl VALUES(@text);
	SELECT @outNum = IDENT_CURRENT('pr_Tbl') ; --pr_Tbl의 현재 identity 값

CREATE TABLE pr_Tbl(
	no INT IDENTITY,
	text NCHAR(10)
);

DECLARE @n INT ;
EXEC pr_4 'number 1', @n OUTPUT;
PRINT '현재 pr_Tbl의 no 값 : ' + CAST(@n AS NCHAR(5));

SELECT * 
  FROM pr_Tbl;