SQL Stored Procedure에서의 에러 처리
SQL Server의 Stored Procedure (저장 프로시져, SP)에서 에러 처리를 하기 위해서 흔히 TSQL의 RAISERROR를 사용한다. (SQL 2012에는 THROW를 사용할 수도 있다) RASIERROR는 파라미터로 Message, Severity, State 등을 갖는데, 여기서 Severity는 중요한 의미를 갖는다. 만약 Severity가 0부터 10사이인 경우 이는 Information 혹은 Warning에 해당되서 TSQL의 TRY...CATCH 블력이 있더라도 CATCH 블럭으로 이동하지 않고 다음 TSQL 문장으로 진행한다. 하지만 먄약 Severity가 11이상인 경우 이는 에러에 해당되어 CATCH 블럭으로 이동하게 된다. 아래는 Informational Severity 와 Error Severity를 함께 사용한 Stored Procedure 예제이다.
예제
CREATE PROC sp_GetData
(
@type int,
@output varchar(50) OUTPUT
)
AS
BEGIN TRY
DECLARE @data varchar(100)
IF @type < 0 OR @type > 10
-- 프로그래밍 에러 : Severity 11-16
RAISERROR('Error: invalid input in @type', 16, 1)
SELECT TOP 1 @data = data
FROM Table1
WHERE [TYPE] = @type
ORDER BY Id
IF LEN(@data) > 50
BEGIN
SET @output = SUBSTRING(@data, 0, 50)
-- Info 혹은 Warning : Severity 0-10
RAISERROR('Info: truncate data (max=50)', 2, 1)
END
ELSE
SET @output = @data
RETURN 1
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0
GO
(
@type int,
@output varchar(50) OUTPUT
)
AS
BEGIN TRY
DECLARE @data varchar(100)
IF @type < 0 OR @type > 10
-- 프로그래밍 에러 : Severity 11-16
RAISERROR('Error: invalid input in @type', 16, 1)
SELECT TOP 1 @data = data
FROM Table1
WHERE [TYPE] = @type
ORDER BY Id
IF LEN(@data) > 50
BEGIN
SET @output = SUBSTRING(@data, 0, 50)
-- Info 혹은 Warning : Severity 0-10
RAISERROR('Info: truncate data (max=50)', 2, 1)
END
ELSE
SET @output = @data
RETURN 1
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
RETURN 0
GO
C#에서 Stored Procedure 에러 처리
C# / ADO.NET 프로그램 입장에서 보면, Informational Severity 레벨의 정보 혹은 경고들은 Exception (SqlException)으로 처리되지 않고 C#의 catch 블럭에서 잡을 수 없다. 이 정보/경고를 받기 위해서는 SqlConnection 객체의 InfoMessage 이벤트를 핸들링해줘야 한다. 반면 Stored Procedure에서 발생한 Severity 11이상의 에러들은 C#의 catch 블력으로 잡히게 되고, 일반적으로 SqlException으로 잡을 수 있다. SP를 어떻게 작성하는가에 따라 다르겠지만, 일반적으로 Informational인 경우 SP결과를 정상적으로 처리하고, Error인 경우 SP 결과를 무효화한다.
예제
static void Main(string[] args)
{
CallSP(2); // Normal
CallSP(4); // Info
CallSP(200); // Error
}
static void CallSP(int type)
{
string strCn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=true";
try
{
using (SqlConnection conn = new SqlConnection(strCn))
{
conn.Open();
// Informational 이벤트를 핸들링 (Severity:0-10)
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
SqlCommand cmd = new SqlCommand("sp_GetData", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Input param
SqlParameter pInput = new SqlParameter("@type", SqlDbType.Int);
pInput.Direction = ParameterDirection.Input;
pInput.Value = type;
cmd.Parameters.Add(pInput);
// Output param
SqlParameter pOutput = new SqlParameter("@output", SqlDbType.VarChar, 50);
pOutput.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pOutput);
// Run SP
cmd.ExecuteNonQuery();
Console.WriteLine(pOutput.Value); // output
Console.WriteLine();
}
}
catch (SqlException e) // 에러 : Severity=11+ 인 경우
{
Console.WriteLine("(Error) No Output for SP call - {0}", e.Message);
}
catch (Exception ex)
{
throw ex;
}
}
static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("(Informational Message) {0}", e.Message);
}
{
CallSP(2); // Normal
CallSP(4); // Info
CallSP(200); // Error
}
static void CallSP(int type)
{
string strCn = "Data Source=.;Initial Catalog=MyDB;Integrated Security=true";
try
{
using (SqlConnection conn = new SqlConnection(strCn))
{
conn.Open();
// Informational 이벤트를 핸들링 (Severity:0-10)
conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
SqlCommand cmd = new SqlCommand("sp_GetData", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Input param
SqlParameter pInput = new SqlParameter("@type", SqlDbType.Int);
pInput.Direction = ParameterDirection.Input;
pInput.Value = type;
cmd.Parameters.Add(pInput);
// Output param
SqlParameter pOutput = new SqlParameter("@output", SqlDbType.VarChar, 50);
pOutput.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pOutput);
// Run SP
cmd.ExecuteNonQuery();
Console.WriteLine(pOutput.Value); // output
Console.WriteLine();
}
}
catch (SqlException e) // 에러 : Severity=11+ 인 경우
{
Console.WriteLine("(Error) No Output for SP call - {0}", e.Message);
}
catch (Exception ex)
{
throw ex;
}
}
static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("(Informational Message) {0}", e.Message);
}
실행 샘플
