[C#] 프로시저 호출 방법

1.
/**************************************************************************************************
* Stored Procedure에서 OUTPUT 변수 미선언
* SqlDataReader 객체 사용하여 SELECT 된 데이타 얻어옴.
* SqlCommand.ExecuteScalar 를 활용해도 좋을듯.
**************************************************************************************************/
[WebMethod]
public int UserConfirm(string sabun, string pass)
{
 try
 {
  dbConn = new SqlConnection("server=localhost;uid=sa;pwd=xxxx;database=xxxx");
  dbConn.Open();

  SqlCommand cmd = new SqlCommand();
  cmd.Connection = dbConn;
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "dbo.SP_MEMBER_CONFIRM";    
  
  cmd.Parameters.Add("@sabun", SqlDbType.VarChar, 10);
  cmd.Parameters.Add("@pass", SqlDbType.VarChar, 8);

  cmd.Parameters["@sabun"].Value = sabun;
  cmd.Parameters["@pass"].Value = pass;

  int result_value = 0 ;
  SqlDataReader reader = cmd.ExecuteReader();
  if(reader.Read())
   result_value = reader.GetInt32(0);
  
  reader.Close();
  return result_value;
 }
 catch(Exception e)
 {
  System.Console.WriteLine(e.Message);
  return 4;
 }
 finally
 {
  dbConn.Close();
 }
}

####################################################################################################

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROC SP_MEMBER_CONFIRM
    @sabun varchar(10),  /* Client 사번 */
    @pass  varchar(8)    /* Client 비밀번호 */
AS 
    BEGIN
 DECLARE @chk_sabun varchar(10)
 DECLARE @chk_pass  varchar(8)
 DECLARE @tot_cnt NUMERIC
 DECLARE @opt int /* return 값 */

 SET @chk_sabun = (SELECT SABUN FROM MEMBER WHERE SABUN = @sabun)
 
 if @chk_sabun is not null /* 사번이 있는경우 */
     BEGIN
  set @chk_pass = (SELECT PASS FROM MEMBER WHERE SABUN = @sabun and PASS = @pass)

  IF @chk_pass is not null /* 계정 일치 */
    BEGIN
   UPDATE MEMBER SET LAST_DATE = REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-',''), FAIL_CNT = 0
   WHERE SABUN = @sabun and PASS = @pass
   SET @opt = 1
    END
  ELSE /* 비밀번호 인증오류 */
    BEGIN
   set @tot_cnt = (SELECT FAIL_CNT FROM MEMBER WHERE SABUN = @sabun)
   UPDATE MEMBER SET FAIL_CNT=(@tot_cnt + 1) WHERE SABUN = @sabun
   SET @opt = 3
    END
     END
 ELSE /* 사번이 없는경우 */
  SET @opt = 2

 SELECT @opt AS OPT
    END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


2.
/**************************************************************************************************
* Stored Procedure에서 OUTPUT 변수 선언
* 파라메터에 저장된 값을 얻어옴.
* SP 뿐 아니라 C# 코드에서도 OUTPUT TYPE을 명시해줘야함.
* OUTPUT 변수에 값만 SET해주면 되고 별도로 RETURN 이나 SELECT가 필요없다.
**************************************************************************************************/

[WebMethod]
public int UserConfirm(string sabun, string pass)
{
 try
 {
  dbConn = new SqlConnection("server=localhost;uid=sa;pwd=xxxx;database=xxxx");
  dbConn.Open();

  SqlCommand cmd = new SqlCommand();
  cmd.Connection = dbConn;
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "dbo.SP_MEMBER_CONFIRM";    
  
  cmd.Parameters.Add("@sabun", SqlDbType.VarChar, 10);
  cmd.Parameters.Add("@pass", SqlDbType.VarChar, 8);

  cmd.Parameters["@sabun"].Value = sabun;
  cmd.Parameters["@pass"].Value = pass;

  SqlParameter myParameter = cmd.Parameters.Add("@opt", SqlDbType.Int);
  myParameter.Direction = ParameterDirection.Output;

  cmd.ExecuteNonQuery();

  int result_value = 0 ;
  result_value = Convert.ToInt32(cmd.Parameters["@opt"].Value);
  
  return result_value;
 }
 catch(Exception e)
 {
  System.Console.WriteLine(e.Message);
  return 4;
 }
 finally
 {
  dbConn.Close();
 }
}

####################################################################################################

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROC SP_MEMBER_CONFIRM
    @sabun varchar(10),  /* Client 사번 */
    @pass  varchar(8),    /* Client 비밀번호 */
    @opt int OUTPUT       /* return 값 */
AS 
    BEGIN
 DECLARE @chk_sabun varchar(10)
 DECLARE @chk_pass  varchar(8)
 DECLARE @tot_cnt NUMERIC

 SET @chk_sabun = (SELECT SABUN FROM MEMBER WHERE SABUN = @sabun)
 
 if @chk_sabun is not null /* 사번이 있는경우 */
     BEGIN
  set @chk_pass = (SELECT PASS FROM MEMBER WHERE SABUN = @sabun and PASS = @pass)

  IF @chk_pass is not null /* 계정 일치 */
    BEGIN
   UPDATE MEMBER SET LAST_DATE = REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-',''), FAIL_CNT = 0 WHERE SABUN = @sabun and PASS = @pass
   SET @opt = 1
    END
  ELSE /* 비밀번호 인증오류 */
    BEGIN
   set @tot_cnt = (SELECT FAIL_CNT FROM MEMBER WHERE SABUN = @sabun)
   UPDATE MEMBER SET FAIL_CNT=(@tot_cnt + 1) WHERE SABUN = @sabun
   SET @opt = 3
    END
     END
 ELSE /* 사번이 없는경우 */
  SET @opt = 2
    END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


////////////////////////////
데브피아에서 퍼왔는데 정확히 위치를 모르겠네...