SQL异常处理机制 SQL错误捕获完整指南

BEGIN TRANSACTION;

BEGIN TRY
    -- 你的SQL语句
    INSERT INTO Products (ProductName, Price) VALUES ('New Product', 10.00);

    -- 如果成功，提交事务
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- 如果发生错误，回滚事务
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- 记录错误日志
    INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
    VALUES (ERROR_MESSAGE(), GETDATE());

    -- 可以选择抛出异常，或者返回一个错误码
    THROW;
END CATCH;

CREATE PROCEDURE AddProduct
    @ProductName VARCHAR(255),
    @Price DECIMAL(10, 2),
    @ErrorCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    BEGIN TRY
        INSERT INTO Products (ProductName, Price) VALUES (@ProductName, @Price);
        SET @ErrorCode = 0; -- 成功
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
        VALUES (ERROR_MESSAGE(), GETDATE());

        SET @ErrorCode = ERROR_NUMBER(); -- 返回错误代码
    END CATCH;
END
GO

-- 调用存储过程
DECLARE @ErrCode INT;
EXEC AddProduct 'Another Product', 20.00, @ErrCode OUTPUT;

IF @ErrCode <> 0
BEGIN
    -- 处理错误
    SELECT @ErrCode AS ErrorCode;
END

// C#中使用参数化查询的例子
string productName = txtProductName.Text;
decimal price = decimal.Parse(txtPrice.Text);

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "INSERT INTO Products (ProductName, Price) VALUES (@ProductName, @Price)";
    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@ProductName", productName);
    command.Parameters.AddWithValue("@Price", price);

    connection.Open();
    command.ExecuteNonQuery();
}

// C#中使用重试机制的例子
int retryCount = 3;
TimeSpan retryInterval = TimeSpan.FromSeconds(5);

for (int i = 0; i < retryCount; i++)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            // 执行SQL语句
            break; // 成功连接，跳出循环
        }
    }
    catch (SqlException ex)
    {
        // 处理连接错误
        if (i == retryCount - 1)
        {
            // 达到最大重试次数，抛出异常
            throw;
        }
        else
        {
            // 等待一段时间后重试
            Thread.Sleep(retryInterval);
        }
    }
}