MS-SQL 프로시져 모음

@########@@@@@@@@@#####################  - 생성

--프로시져 생성
CREATE PROC up_konan_test6
AS
SELECT * FROM titles

--프로시져 수행
EXEC up_konan_test6

@########@@@@@@@@@##################### - 생성시 변수 받기

--프로시져 생성
CREATE PROC up_konan_test7 
@v_price int
AS
SELECT * FROM titles WHERE price > @v_price

--프로시져 수행
EXEC up_konan_test7 30


@########@@@@@@@@@##################### - 생성받은 변수로 저장


--테스트 테이블 생성
CREATE TABLE konan_test9(
c1 int
, c2 varchar(10)
)

--프로시져 생성
CREATE PROC up_konan_test9
@v_c1 int
, @v_c2 varchar(10)
AS
INSERT INTO konan_test9(c1, c2) VALUES(@v_c1, @v_c2)

--프로시져 수행
EXEC up_konan_test9 1, 'Hi~'

EXEC up_konan_test9 2, '테스트'

--데이터 조회
SELECT * FROM konan_test9


@########@@@@@@@@@##################### - 문자열 생성 방식

--프로시져 생성
CREATE PROC up_konan_test10
@v_tblname varchar(20)
, @v_title_id varchar(20)
AS
--저장할 변수 선언
DECLARE @v_strSQL VARCHAR(200)
--변수 @v_strSQL에 생성된 문자열 저장
SET @v_strSQL = 'SELECT * FROM ' + @v_tblname + 
' WHERE title_id = ''' + @v_title_id + ''''
EXEC(@v_strSQL)
--SELECT @v_strSQL

--프로시져 수행
EXEC up_konan_test10 'titles', 'BU1032'

EXEC @v_strSQL 괄호를 쓰지 않으면 프로시져로 인식함.

@########@@@@@@@@@##################### = 쓸수 없는 사항.


--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test11 
@v_tblname varchar(20)
AS
SELECT * FROM @v_tblname

객체로 인식하게됨.

exec ('SELECT * FROM '+ @v_tblname) => 이런 형태로 써야함.
변수값 저장 = 'SELECT * FROM ' + @v_tblname => 이런 형태로 써야함.


################$@#@@@@@@@@@@@@@@@@@  = > Top 으로 쓸경우


--프로시져 생성 - 수행 안됨
CREATE PROC up_konan_test12
@v_topN int
AS
SELECT top @v_topN * FROM titles

--프로시져 생성 - 프로시져 생성은 가능. 수행은 불가
CREATE PROC up_konan_test12
@v_topN int
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)

--프로시져 수행. 수행 불가
EXEC up_konan_test12 '10'


--프로시져 생성
CREATE PROC up_konan_test13
@v_topN varchar(5) --여기가 틀림
AS
DECLARE @v_strSQL VARCHAR(200)
SET @v_strSQL = 'SELECT top ' + @v_topN + ' * FROM titles'
EXEC(@v_strSQL)

--프로시져 수행
EXEC up_konan_test13 '10'

################$@#@@@@@@@@@@@@@@@@@ - OUTPUT 역시 매개 변수 


--OUTPUT을 사용한 저장 프로시져
CREATE PROC up_konan14
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SET @v_output = (SELECT @@ROWCOUNT)

--프로시져 수행
DECLARE @v_effected_rows int
EXEC up_konan14 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC up_konan14 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows


################$@#@@@@@@@@@@@@@@@@@   - SELECT가 포함된 프로시져의 OUTPUT 매개변수 사용 


--OUTPUT을 사용한 저장 프로시져 SELECT 포함
CREATE PROC up_konan15
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
SET @v_output = (SELECT @@ROWCOUNT)

--프로시져 수행
DECLARE @v_effected_rows int
EXEC up_konan15 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC up_konan15 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows


################$@#@@@@@@@@@@@@@@@@@- 편법을 이용해 2개의 결과셋으로 줄일 수 있습니다

--OUTPUT을 사용한 저장 프로시져 SELECT 포함
CREATE PROC up_konan17
@v_title_id varchar(10)
, @v_output int OUTPUT
AS
SET NOCOUNT ON
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
SET @v_output = (SELECT @@ROWCOUNT)

--프로시져 수행
DECLARE @v_effected_rows int
EXEC up_konan17 'BU1032', @v_effected_rows OUTPUT
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC up_konan17 'BU103X', @v_effected_rows OUTPUT
SELECT @v_effected_rows


바로 SET NOCOUNT ON 이라는 녀석 입니다.
결과셋중 UPDATE 결과셋은 리턴 값이 없는.. 오로지 영향받은 행의 수만 리턴 합니다.
이럴 경우 SET NOCOUNT ON을 이용해 결과 값이 없을 경우 결과 셋으로 처리 안하실
수 있습니다. - 이러면 UPDATE 결과셋이 안넘어 오니 두개의 결과셋이 오지요.
COMPUTE의 결과셋 - (여러개의 결과셋)을 이용할 수 없는 이유와 같다고 보시면 됩니다.
ASP에서는 이를 받아서 처리하실 수 없습니다.
하지만.. VB + DB 나.. ASP + 컴퍼넌트 + DB 를 이용할 경우 사용이 가능합니다.
ADO의 Recordset 메서드 중에서 NextRecoedset을 이용하면 사용이 가능합니다.
하지만. ASP + DB일 경우 사용이 불가 합니다. 이점 주의하세요.
사실 @@ROWCOUNT나 @@ERROR 와 같은 전역 함수는 매우 유용합니다.


################$@#@@@@@@@@@@@@@@@@@ - RETURN을 사용하는 방식 


--RETURN을 사용한 저장 프로시져
CREATE PROC up_konan16
@v_title_id varchar(10)
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
RETURN @@ROWCOUNT

--프로시져 수행
DECLARE @v_effected_rows int
EXEC @v_effected_rows = up_konan16 'BU1032'
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC @v_effected_rows = up_konan16 'BU103X'
SELECT @v_effected_rows


################$@#@@@@@@@@@@@@@@@@@  - SELECT가 포함된 RETURN을 이용하는 방식

--RETURN을 사용한 저장 프로시져 SELECT 포함
CREATE PROC up_konan18
@v_title_id varchar(10)
AS
UPDATE titles SET price = price * 2
WHERE title_id = @v_title_id
SELECT * FROM titles
RETURN @@ROWCOUNT

--프로시져 수행
DECLARE @v_effected_rows int
EXEC @v_effected_rows = up_konan18 'BU1032'
SELECT @v_effected_rows

--영향을 받은 로우의 수가 없을 경우.
DECLARE @v_effected_rows int
EXEC @v_effected_rows = up_konan18 'BU103X'
SELECT @v_effected_rows

　
################$@#@@@@@@@@@@@@@@@@@

RETURN 값을 받기 바로 전에 select 를 실행하면 실행 한 행의 갯수가 출력됨.


################$@#@@@@@@@@@@@@@@@@@ - 프로시져 내부에서 다른 저장 프로시져를 호출 (32개 까지 중첩이 가능)


--up_konan99라는 존재하지 않는 프로시져 수행 
CREATE PROC up_konan20
AS
SELECT TOP 1 * FROM titles
EXEC up_konan99

--오류 메세지
존재하지 않는 개체 'up_konan99'에 의존하고 있으므로 
현재 저장 프로시저의 sysdepends에 행을 추가할 수 없습니다. 
저장 프로시저는 만들어집니다.

--수행결과는?
EXEC up_konan20

--up_konan99 프로시져 생성
CREATE PROC up_konan99
AS
SELECT TOP 10 * FROM titles

--수행결과는?
EXEC up_konan20

EXEC up_konan99


################$@#@@@@@@@@@@@@@@@@@ - 생성된 저장 프로시져를 역추적 

--sysobjects 테이블에서 타입은 프로시져이고 
--이름이 up_konan20 인 프로시져의 name과 id 조회
SELECT name, id FROM sysobjects WHERE xtype = 'P' 
and name = 'up_konan20'

-- < ID 값 - 코난이의 경우 434100587 임.. 님들의 값과 다를 겁니당.

--해당 ID값으로 syscomments 테이블에서 데이터 조회
--코난이와 다른 값을 주셔야 겠지요? ^_^
SELECT id, text FROM syscomments where id = 434100587

--참고로 sysdepends 테이블에서 의존성을 확인해 보자.
SELECT id, depid FROM sysdepends where id = 434100587

--sysdepends 테이블에서 조회된 id로 어떤 DB개체인지 알아보자.
SELECT name, id FROM sysobjects WHERE id = '2121058592'

--코난이의 경우 titles 테이블 이었습니다. ^_^


################$@#@@@@@@@@@@@@@@@@@ - 프로시져 내부에서 커서, Temp 테이블을 사용하는 샘플 [실무예제]

--작성자 : 김대우
--작성일 : 2001년 3월 19일
--목적 : titles 테이블에서 인자보다 큰 가격의 책을 가져와 
--원가보다 10% 비싼 가격으로 뿌려준다.
--인자 : 책 가격 제한 값.

CREATE PROC up_sel_New_Price 
@v_limit_price int
AS
SET NOCOUNT ON
--커서 선언
DECLARE cur_konan_Test CURSOR 
FOR
SELECT title_id, title, price FROM titles
WHERE price > @v_limit_price

--커서 오픈
OPEN cur_konan_Test

--변수 선언
DECLARE @v_title_id VARCHAR(10)
DECLARE @v_title VARCHAR(100)
DECLARE @v_price MONEY

--임시 테이블을 SELECT INTO로 생성
SELECT title_id, title, price INTO #temp_tbl_konan_test FROM titles
WHERE 1 = 2

--첫 로우 FETCH
FETCH NEXT FROM cur_konan_Test INTO @v_title_id, @v_title, @v_price

WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH된 데이터를 tempdb에 삽입
INSERT INTO #temp_tbl_konan_test
VALUES(@v_title_id, @v_title, @v_price)

--다음 로우 FEETCH - 루프
FETCH NEXT FROM cur_konan_Test INTO @v_title_id, @v_title, @v_price
END

--임시 테이블에서 데이터 조회 이때. 가격을 10% 올림
SELECT title_id, title, price * 1.1 FROM #temp_tbl_konan_test

--커서 CLOSE
CLOSE cur_konan_Test

--커서 DEALLOCATE
DEALLOCATE cur_konan_Test


--수행 샘플
--exec up_sel_New_Price 5
--exec up_sel_New_Price 10
--exec up_sel_New_Price 20

################$@#@@@@@@@@@@@@@@@@@


################$@#@@@@@@@@@@@@@@@@@


################$@#@@@@@@@@@@@@@@@@@


　
[출처] 실무에서의 저장 프로시저|작성자 junmo1770


http://blog.daum.net/lords001/582394
[출처] MS-SQL 저장 프로시져|작성자 콩이