PROCEDURE *OUTPUT 사용법
CREATE PROCEDURE sp_zul_account
/*
절부분 결제 부분입니다.
결제함과 동시에 절에 보시했다는 정보를 보관합니다.
*/
@member_id varchar(50) ,
@zul varchar(50) ,
@comment varchar(200) ,
@cash int ,
@result int output
AS
Declare @i varchar(50)
--실직적인 결제를 하는부분
--또다른 저장프로시저
exec sp_account @member_id ,@comment , @cash , @i output
if( @i ='1' )
begin
--절에대한 정보를 넣어줍니다
insert into dbo.zul_love_account(zul , member_id , makedate ) values( @zul , @member_id , getdate() )
SET @result='1'
end
else
begin
SET @result='2'
end
GO
asp 코드
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_DBconn_STRING
Command1.CommandText = "dbo.sp_zul_account"
Command1.Parameters.Append Command1.CreateParameter("@member_id", 200, 1,50,member_id)
Command1.Parameters.Append Command1.CreateParameter("@zul", 200, 1,50,zul)
Command1.Parameters.Append Command1.CreateParameter("@comment", 200, 1,50,comment)
Command1.Parameters.Append Command1.CreateParameter("@cash", 3, 1,4,cash)
Command1.Parameters.Append Command1.CreateParameter("@result", 3, 2,4)
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
if Command1.Parameters.Item("@result").Value <>"1" then
response.write "<script>"
response.write "alert('결제금액이 부족합니다');"
response.write "history.back(-1);"
response.write "</script>"
response.End()
else
response.Cookies("zul")=zul
response.Redirect("/account/account_end.asp")
end if
------------------------------------------------------------------------------------------
두개이상의 결과값 받기
------------------------------------------------------------------------------------------
*위에랑 별 차이 엄따 2개란거 말고는 ㅋㅋ
데이타베이스
CREATE PROCEDURE sp_get_login_profile
@member_id varchar(50) ,
@love int output ,
@memo int output
AS
select @memo=Count(*) from memo_receive where memo_to =@member_id and read_class='2'
select @love=cash from member_config where member_id=@member_id
print @memo
GO
asp 코드
<%
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_DBconn_STRING
Command1.CommandText = "dbo.sp_get_login_profile"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@member_id", 200, 1,50,member_id)
Command1.Parameters.Append Command1.CreateParameter("@love", 3, 2,4)
Command1.Parameters.Append Command1.CreateParameter("@memo", 3, 2,4)
Command1.Execute()
%>
<%= Command1.Parameters.Item("@love").Value %>
<%= Command1.Parameters.Item("@memo").Value %>
------------------------------------------------------------------------------------------
쿼리문자열을 만들어서 프로시저안에서 실행하기
------------------------------------------------------------------------------------------
CREATE PROCEDURE sp_get_soc_newwrite
@board_type int ,
@top int
AS
DECLARE @sql varchar(200)
set @sql=' select top '+convert( varchar(50) ,@top)+' * from soc_board where board_type ='''+convert(varchar(50) , @board_type)+''' '
exec (@sql)
GO
[출처] 간단한 저장프로시저 예제|작성자 yo9lee
http://blog.naver.com/yo9lee?Redirect=Log&logNo=100070867875
