Dunk

●●●學習筆記及問題處理過程●●●

2012-08-29

[SQL]錯誤處理

[SQL]錯誤處理

練習一下

問題描述

將STORE PROCEDURE加入錯誤處理機制

目的

練習錯誤處理語法及簡化前端頁面程式處理

優點 : 簡化前端程式的條件檢核

缺點 : 增加資料庫計算負擔

執行SQL

-- sp_addmessage
-- http://msdn.microsoft.com/zh-tw/library/ms178649.aspx
-- sp_dropmessage
-- http://msdn.microsoft.com/zh-tw/library/ms174369.aspx
-- RAISERROR
-- http://msdn.microsoft.com/zh-tw/library/ms178592(v=sql.105).aspx

USE master
GO

-- 顯示系統所有的message
-- 1033:英文 1028:繁體中文
SELECT *
FROM
	sys.messages
WHERE
	language_id IN (1033, 1028)
ORDER BY
	2
GO

-- 增加自訂錯誤訊息
-- 一定要先建立英文版本(us_english)後才能新增其他的版本
IF exists
(SELECT message_id
 FROM
	 sys.messages
 WHERE
	 message_id = 50001) EXEC sp_dropmessage @msgnum = 50001, @lang = 'all';
EXEC sp_addmessage @msgnum = 50001, @severity = 10, @msgtext = N'Employee data already exist!(%s)', @lang = 'us_english', @with_log = 'TRUE', @replace = 'replace';
EXEC sp_addmessage @msgnum = 50001, @severity = 10, @msgtext = N'員工資料已經存在!(%1!)', @lang = '繁體中文', @replace = 'replace';
GO

-- 測試自訂訊息是否建立
DECLARE @STAFF char(6)
SET @STAFF = '123456';
RAISERROR (50001,10,1,@STAFF);
GO

-- 建立初始資料表格
USE testdb
GO

IF exists
(SELECT name
 FROM
	 sys.tables
 WHERE
	 name = 'EMPLOYEE_PARTIAL') DROP TABLE dbo.EMPLOYEE_PARTIAL
GO
CREATE TABLE dbo.EMPLOYEE_PARTIAL
(
	STAFF nvarchar(6) primary key not null,
	NAME nvarchar(30)
);

-- 檢查測試表格是否已建立
SELECT *
FROM
	dbo.EMPLOYEE_PARTIAL
	
-- 無錯誤處理的的STORE PROCEDURE(採用系統預設訊息)
IF exists
(SELECT name
 FROM
	 sys.procedures
 WHERE
	 name = 'AddEmployeePartialOld') DROP PROC dbo.AddEmployeePartialOld
GO
CREATE PROC dbo.AddEmployeePartialOld
	@STAFF nvarchar(6), 
	@NAME nvarchar(30),
	@MESSAGE nvarchar(30) output
AS
BEGIN
INSERT INTO dbo.EMPLOYEE_PARTIAL (STAFF
								, NAME)
VALUES
	(@STAFF, @NAME);
SELECT @MESSAGE = '員工資料新增完畢!員編:' + @STAFF;	
END
GO

-- 測試錯誤發生
-- 第二次會失敗
-- 訊息 2627，層級 14，狀態 1，程序 AddEmployeePartialOld，行 7
-- 違反 PRIMARY KEY 條件約束 'PK__EMPLOYEE_PARTIAL__3D14070F'。無法在物件 'dbo.EMPLOYEE_PARTIAL' 中插入重複的索引鍵。
-- 陳述式已經結束。
-- (1 個資料列受到影響)

DECLARE @message nvarchar(30);
EXEC dbo.AddEmployeePartialOld '123456', N'林大貓', @message OUTPUT;
SELECT @message AS [處理結果];



-- 有錯誤處理的的STORE PROCEDURE
IF exists
(SELECT name
 FROM
	 sys.procedures
 WHERE
	 name = 'AddEmployeePartialErrorHandling') DROP PROC dbo.AddEmployeePartialErrorHandling
GO
CREATE PROC dbo.AddEmployeePartialErrorHandling
	@STAFF nvarchar(6), 
	@NAME nvarchar(30),
	@MESSAGE nvarchar(30) output
AS
BEGIN TRY
INSERT INTO dbo.EMPLOYEE_PARTIAL (STAFF
								, NAME)
VALUES
	(@STAFF, @NAME);
SELECT @MESSAGE = '員工資料新增完畢!員編:' + @STAFF;	
END TRY
BEGIN CATCH
 IF ERROR_NUMBER() = 2627
SELECT @MESSAGE = '員工資料已存在!員編:' + @STAFF;
END CATCH
GO

-- 測試有錯誤處理的STORE PROCEDURE
DECLARE @message nvarchar(30);
EXEC dbo.AddEmployeePartialErrorHandling '123456', N'林大貓', @message OUTPUT;
SELECT @message AS [處理結果];



-- 有判斷條件處理的的STORE PROCEDURE(採用自訂訊息)
IF exists
(SELECT name
 FROM
	 sys.procedures
 WHERE
	 name = 'AddEmployeePartialCondition') DROP PROC dbo.AddEmployeePartialCondition
GO
CREATE PROC dbo.AddEmployeePartialCondition
	@STAFF nvarchar(6), 
	@NAME nvarchar(30),
	@MESSAGE nvarchar(30) output
AS
BEGIN
IF EXISTS
(SELECT STAFF
 FROM
	 dbo.EMPLOYEE_PARTIAL
 WHERE
	 STAFF = @STAFF)
BEGIN
SELECT @MESSAGE = '員工資料已存在!員編:' + @STAFF;
RAISERROR (50001,10,1,@STAFF);
RETURN;
END
INSERT INTO dbo.EMPLOYEE_PARTIAL (STAFF
								, NAME)
VALUES
	(@STAFF, @NAME);
SELECT @MESSAGE = '員工資料新增完畢!員編:' + @STAFF;	
END
GO

-- 測試有判斷條件處理的的STORE PROCEDURE
-- 採用自訂訊息會將訊息存在EVENLOG
DECLARE @message nvarchar(30);
EXEC dbo.AddEmployeePartialCondition '123456', N'林大貓', @message OUTPUT;
SELECT @message AS [處理結果];


-- 刪除測試相關資料資料

DROP TABLE dbo.EMPLOYEE_PARTIAL;
DROP PROC dbo.AddEmployeePartialOld;
DROP PROC dbo.AddEmployeePartialErrorHandling;
DROP PROC dbo.AddEmployeePartialCondition;
GO

前端網頁

aspx


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="STAFF"></asp:Label>
        <asp:TextBox ID="txt_STAFF" runat="server">123456</asp:TextBox>
        <br />
        <asp:Label ID="Label2" runat="server" Text="NAME"></asp:Label>
        <asp:TextBox ID="txt_NAME" runat="server">林大貓</asp:TextBox>
        <br />
        <asp:Button ID="btn_Old" runat="server" onclick="btn_Old_Click" 
            Text="insert old" />
        <asp:Button ID="btn_ErrorHandling" runat="server" 
            onclick="btn_ErrorHandling_Click" Text="insert error handling" />
        <asp:Button ID="btn_ConditionCheck" runat="server" 
            onclick="btn_ConditionCheck_Click" Text="insert condtional check" />
        <br />
        <asp:Label ID="lab_State" runat="server" style="color: #0000FF"></asp:Label>
    
    </div>
    </form>
</body>
</html>

cs

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btn_Old_Click(object sender, EventArgs e)
    {
        InsertData("AddEmployeePartialOld");
    }
    protected void btn_ErrorHandling_Click(object sender, EventArgs e)
    {
        InsertData("AddEmployeePartialErrorHandling");
    }
    protected void btn_ConditionCheck_Click(object sender, EventArgs e)
    {
        InsertData("AddEmployeePartialCondition");
    }

    /// <summary>
    /// 輸入測試資料.
    /// </summary>
    /// <param name="procedureName">Name of the procedure.</param>
    private void InsertData(string procedureName)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testdb"].ToString()))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(string.Empty, conn))
            {
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                cmd.Parameters.Add("@STAFF", SqlDbType.NVarChar).Value = txt_STAFF.Text.Trim();
                cmd.Parameters.Add("@NAME", SqlDbType.NVarChar).Value = txt_NAME.Text.Trim();

                SqlParameter lastName = cmd.Parameters.Add("@MESSAGE", SqlDbType.VarChar, 30);
                lastName.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                lab_State.Text = lastName.Value.ToString();
            }
        }

    }
}

執行結果

當有重複資料輸入時(沒有做任何條件檢查或者錯誤處理)

pic1

當有重複資料輸入時(條件檢查或者錯誤處理)

pic2

參考資料

德瑞克老師的上課筆記

[SQL Server] 如何接收 Store Procedure 的傳回值