//프로시저 생성
create procedure sp_user //proc로 줄여쓸 수 있다.
as
begin
select u.id,name,age,login
from userinfo u, userlog l
where u.id=l.id
end
//프로시저 출력
exec sp_user
//개체 확인
sp_helptext sp_user
//프로시저 삭제
drop proc sp_user
//#으로 테이블과 마찬가지로 임시프로시저 생성가능 (연결이 끊어지면 사라진다.)
create procedure #sp_user ~~
//임시 프로시저 출력
#sp_user
엔터프라이즈에 가서 도구 안에 마법사를 가면 쉽게 프로시저를 만들 수 있다. 프로시저는 주로 삽입, 삭제, 업데이트에 쓰인다.
조회는 주로 뷰를 이용한다. 역시 엔터프라이즈에 가서 새뷰를 이용하면 보면서 쉽게 만들 수 있다.
뷰는 프로시저와 달리 테이블처럼 출력 할 수 있다.
select * from 뷰명
//매개변수(패러미터)가 있는 프로시저
create proc sp_user1
@age int
as
begin
select u.id,name,age,login
from userinfo u, userlog l
where u.id=l.id and age>@age
end
//출력시
sp_user1 25 //프로시저명 뒤에 매개변수값을 줘야한다.
//패러미터 값과 초기값을 가지는 프로시저 만들기
create proc sp_user3
@age int = 25, // 값이 없을 경우 age=25의 초기값을 지닌다.
@sex char(1) = 'm'
as
begin
select u.id,name,age,login
from userinfo u, userlog l
where u.id=l.id and age = @age and sex = @sex
end
sp_user3 22,'f'
//프로시저 문자열을 이용하기
create proc sp_test
@tb varchar(20),
@titleid varchar(20)
as
begin
declare @sql varchar(100)
set @sql = 'select * from ' + @tb + ' where title_id ='''+ @titleid+''''
exec(@sql) //exec는 @sql을 실행한다는 뜻.
end
sp_test 'titles','bu1032'
//프로시저의 문자열 이용 (@num 값의 타입을 int가 아닌 varchar로 받아야 한다.)
if exists(select * from dbo.sysobjects where id=object_id('sp_extables'))
drop proc sp_extables
go
create proc sp_extables
@num varchar(10)
as
begin
declare @str varchar(100)
set @str='select top ' +@num+ ' * from extable order by idx desc'
exec(@str)
end
sp_extables '10'
//output이 있는 프로시저 (위의 것은 input만 있었고 지금부터.. 중요~~)
create proc sp_getusername
@id varchar(10),
@name varchar(10) output,
@login int output
as
begin
select @name=name, @login=login from userinfo u, userlog l
where u.id=l.id and u.id=@id
end
declare @name varchar(10), @login int
exec sp_getusername 'sumi',@name output, @login output
select @name, @login //sumi라는 id를 가진 사람의 name과 login을 출력하게 된다.
[출처] Mssql 프로시저의 기본~~|작성자 셰프
