💼 정보 ver1.0

RETURN_VALUE &Output Parameter

James Wetzel 2010. 7. 21. 11:00
728x90
반응형


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 값=영화 제목

728x90
반응형