RETURN_VALUE &Output Parameter
DBMS에 데이터를 입력한 후 결과 값을 반환 받고 싶은 경우가 있다.
RETURN_VALUE &Output Parameter
SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MovieConnectionString"].ToString());
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "movie_insert";
SqlParameter param = new SqlParameter();
param = comm.Parameters.Add("@company_No", SqlDbType.Int);
param.Value = int.Parse(production_DDL.SelectedValue);
param = comm.Parameters.Add("@subject", SqlDbType.NVarChar);
param.Value = movieSubject_TB.Text;
param = comm.Parameters.Add("@makeStartDate", SqlDbType.SmallDateTime);
param.Value = startDate_TB.Text;
param = comm.Parameters.Add("@makeEndDate", SqlDbType.SmallDateTime);
param.Value = endDate_TB.Text;
param = comm.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
param = comm.Parameters.Add("@outputParam", SqlDbType.NVarChar, 20);
param.Direction = ParameterDirection.Output;
comm.ExecuteNonQuery();
string rowNo = comm.Parameters["RETURN_VALUE"].Value.ToString();
Response.Write("RETURN_VALUE 값="+rowNo + " OutputParam 값=" + comm.Parameters["@outputParam"].Value.ToString());
저장 프로시져
GREATE ALTER PROCEDURE dbo.movie_insert
@company_No int,
@subject nvarchar(50),
@makeStartDate smalldatetime,
@makeEndDate smalldatetime,
@outputParam nvarchar(20) output
AS
insert into movie values(@company_No, @subject, @makeStartDate, @makeEndDate)
--@outputParam 값
select @outputParam=(select subject from movie where No=@@IDENTITY)
--RETURN_VALUE 값
RETURN @@IDENTITY
결과
RETURN_VALUE 값=15 OutputParam 값=영화 제목