探秘MSSQL存储过程：存储过程的高级特性和实战技巧

CREATE PROCEDURE PlaceOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT
AS
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        -- 执行订单插入操作
        INSERT INTO Orders (CustomerID, ProductID, Quantity) VALUES (@CustomerID, @ProductID, @Quantity)
        -- 执行库存更新操作
        UPDATE Products SET Stock = Stock - @Quantity WHERE ProductID = @ProductID
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        -- 错误处理逻辑
        PRINT 'An error occurred: ' + ERROR_MESSAGE()
    END CATCH
END

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
WITH EXECUTE AS 'dbo' -- 使用dbo用户的权限执行
AS
BEGIN
    IF EXISTS(SELECT * FROM Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        -- 执行薪资更新操作
        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID
    END
    ELSE
    BEGIN
        -- 抛出错误，无权限操作
        RAISERROR('Invalid employee ID', 16, 1)
    END
END

CREATE PROCEDURE GetOrders
    @CustomerID INT,
    @Status INT
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT * FROM Orders WHERE CustomerID = ' + CAST(@CustomerID AS NVARCHAR(10))
    IF @Status IS NOT NULL
    BEGIN
        SET @sql = @sql + ' AND Status = ' + CAST(@Status AS NVARCHAR(10))
    END
    EXEC sp_executesql @sql
END