/**********************************************************************************************
-- Title : [2k5] 프로지서에서의 RETURN 및 OUTOUT 사용(IDENTITY 반환, VB.Net 코드)
-- Reference : hanbitbook.co.kr
-- Key word : return output create proc procedure 프로시저 identity 반환 VB.Net 코드
**********************************************************************************************/
/*
-- RETURN
*/
use adventureworks;
go
drop proc usp_return;
go
create proc usp_return
@addrid int
as
declare @usergrade nvarchar(5);
select @usergrade = addressline2 from adventureworks.person.address
where addressid = @addrid;
if (@usergrade <> '')
return 0; -- 성공일 경우, 그냥 return만 써도 0을 돌려줌
else
return -1; -- 실패일 경우(즉, 해당 이름의 id가 없을 경우)
go
-- 확인
declare @retval int ;
exec @retval=usp_return 15907 ;
select @retval;
go
declare @retval int ;
exec @retval=usp_return 15906 ;
select @retval;
go
/*
-- OUTPUT
*/
use tempdb;
go
drop table testebl;
go
create table testtbl (id int identity, txt nchar(10));
go
drop proc usp_users4;
go
create procedure usp_users4
@txtvalue nchar(10),
@outvalue int output
as
insert into testtbl values(@txtvalue);
select @outvalue = ident_current('testtbl'); -- 테이블의 identity 값
go
-- 확인
declare @myvalue int;
exec usp_users4 '테스트값1', @myvalue output;
print '현재 입력된 id 값 ==> ' + cast(@myvalue as char(5));
go
/*
-- VB.Net에서 OUTPUT 처리 코드
-- http://msdn.microsoft.com/ko-kr/library/yy6y35y8(VS.80).aspx
*/
' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand("SampleProc", connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = command.Parameters.Add( _
"RETURN_VALUE", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = command.Parameters.Add( _
"@InputParm", SqlDbType.NVarChar, 12)
parameter.Value = "Sample Value"
parameter = command.Parameters.Add( _
"@OutputParm", SqlDbType.NVarChar, 28)
parameter.Direction = ParameterDirection.Output
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Console.WriteLine( _
"{0}, {1}", reader.GetName(0), reader.GetName(1))
Do While reader.Read()
Console.WriteLine( _
"{0}, {1}", reader.GetInt32(0), reader.GetString(1))
Loop
reader.Close()
connection.Close()
Console.WriteLine( _
" @OutputParm: {0}", command.Parameters("@OutputParm").Value)
Console.WriteLine( _
"RETURN_VALUE: {0}", command.Parameters("RETURN_VALUE").Value)