[ASP] 간단한 저장프로시저 예제

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