Stored Procedure 실행 및 만들기

Stored Procedure는 데이터베이스에 컴파일되어 Database 서버에 저장되는 SQL 문입니다.

Stored Procedure 설명

• Stored Procedure 대부분의 Database에서 지원되는 기능으로 Query 집합체하고 생각하면 됩니다.
• Stored Procedure는 Database 서버에 저장되어 재사용할 수 있습니다. 즉, 반복적인 SQL을 사용해야 할 때 유용합니다.
• DML 문의 SELECT, INSERT, UPDATE, DELETE뿐만 아니라 IF 문, Declare, set, 커서, while 문, dynamicSql문 etc 대부분 사용할 수 있습니다.
• Procedure는 기본적으로 첫 실행 시 컴파일되기 때문에 실행 계획을 재사용할 수 있어 성능 향상에 많은 도움이 됩니다.
• Procedure는 입력 및 출력 매개변수를 사용할 수 있어 실행 결과에 대한 형식을 사용자가 정의해서 있습니다.

procedure 사용예제 입니다.

Stored Procedure 실행 및 예제

Stored Procedure 생성 및 변경 방법.

• CREATE 문을 사용하여 저장 프로시저를 만들 수 있습니다.
• Syntax.

CREATE PROCEDURE <ProcedureName>
   @<ParameterName1> <data type>,
   @<ParameterName2> <data type>
AS   
   SET NOCOUNT ON;
   SELECT <your SELECT statement>;

Stored Procedure 생성.

• create example code입니다.

DROP TABLE IF EXISTS InfoForProcedure;
CREATE TABLE InfoForProcedure (
    UserId int,
    UserNm varchar(255) NOT NULL,
    UserAge  int ,
    UserAddress varchar(500) 
);
 
INSERT INTO dbo.InfoForProcedure(UserId,UserNm,UserAge,UserAddress) 
VALUES (272, N'Ken',34,'New York')
      ,(273, N'Brian',34,'LA')
      ,(274, N'Stephen',23,'SEOUL')
      ,(275, N'Michael',10,'London')
      ,(276, N'Linda',70,'Bedlin');
-- Create a stored procedure  1
CREATE PROC USP_ProcedureDefault
AS
SELECT DB_NAME() AS dbname;
-- EXECUTE
EXECUTE USP_ProcedureDefault;
 
-- Create a stored procedure 2
CREATE PROC USP_ProcedureSample (
   @paramUserId    int
) AS
SELECT *
  FROM InfoForProcedure
 WHERE UserId  = @paramUserId
;
-- EXECUTE
EXECUTE USP_ProcedureSample 273;

Stored Procedure 수정.

• 수정 example code입니다.

-- Modify a Stored Procedure
ALTER PROC USP_ProcedureSample (
   @paramUserId    int
) AS
  SELECT *
    FROM InfoForProcedure
   WHERE UserId  >  @paramUserId
;
-- EXECUTE
EXECUTE USP_ProcedureSample 273;

Stored Procedure 재컴파일.

• 재컴파일 소스 코드입니다.

--- Recompile a Stored Procedure
EXECUTE USP_ProcedureSample 273  WITH RECOMPILE;

Stored Procedure 이름 변경.

• rename example code입니다.

--Rename the stored procedure.  
EXEC sp_rename 'sampleDB.dbo.USP_ProcedureSample'
              ,'USP_ProcedureSample_reName';

 

Stored Procedure 삭제.

• drop example code입니다.

-- Delete a stored procedure
DROP PROCEDURE [USP_ProcedureSample_reName];

Stored Procedure 검색 및 내용 확인

Stored Procedure 검색.

• 프로시저 존재하는지 검색하는 코드입니다.

-- Search procedure name
SELECT name AS procedure_name
	, SCHEMA_NAME(schema_id) AS schema_name
	, type_desc
	, create_date
	, modify_date
FROM sys.procedures
WHERE name = 'USP_ProcedureSample_reName';

명령어로 프로시저 내용 검색 방법.

• Stored Procedure 내용 검색 명령어입니다.

-- Check the Storage Procedure Source 1.
sp_helptext 'USP_ProcedureSample';

Query로 프로시저 내용 검색 방법.

• Stored Procedure 내용 검색입니다 

-- Check the Storage Procedure Source 2.
SELECT OBJECT_NAME(OBJECT_ID) AS  PROC_NM
      ,definition             AS  PROC_SORCE
FROM sys.sql_modules  
WHERE object_id = (OBJECT_ID(N'dbo.USP_ProcedureSample'));