SQL Server Stored Procedure 예제 - 있으면 Update, 없으면 Insert

SQL Server에서 Stored Procedure를 만들고 C#에서 호출하는 간단한 예제이다.

CREATE PROCEDURE [dbo].[SP_DAUM_INSUPD_PRODUCT]

@Seq int, @Name nvarchar(50), @ChargeType nchar(1), @ExpAmt int, @RegUser nvarchar(50), @RegDate datetime

WITH 

EXECUTE AS CALLER

AS

BEGIN

  DECLARE @Cnt int

  SELECT @Cnt = COUNT(*)  FROM ATAProductMaster WHERE Seq = @Seq

  IF( @Cnt > 0 )

  BEGIN

    UPDATE ATAProductMaster

       SET [Name] = @Name,

       ChargeType = @ChargeType,

       ExpAmt = @ExpAmt,

       RegUser = @RegUser,

       RegDate = @RegDate

     WHERE Seq = @Seq

  END

  ELSE

  BEGIN

    INSERT INTO ATAProductMaster(Seq,[Name],ChargeType,ExpAmt,RegUser,RegDate)

    VALUES(@Seq,@Name,@ChargeType,@ExpAmt,@RegUser,@RegDate)

  END

END

        public void InsUpd(SqlConnection conn)

        {

            ProcName = "SP_DAUM_INSUPD_PRODUCT";

            SqlCommand cmd = new SqlCommand(ProcName,conn);

            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param = cmd.Parameters.Add("@Seq", SqlDbType.Int);

            param.Value = Seq;

            param = cmd.Parameters.Add("@Name",SqlDbType.NVarChar,50);

            param.Value = Name;

            param = cmd.Parameters.Add("@ChargeType", SqlDbType.NVarChar, 1);

            param.Value = ChargeType;

            param = cmd.Parameters.Add("@ExpAmt", SqlDbType.Int);

            param.Value = ExpAmt;

            param = cmd.Parameters.Add("@RegUser", SqlDbType.NVarChar, 50);

            param.Value = RegUser;

            param = cmd.Parameters.Add("@RegDate", SqlDbType.DateTime);

            param.Value = DateTime.Parse(ConvertDateTimeString(RegDate));

            cmd.ExecuteNonQuery();

        }