SQLServer存储过程如何传递参数？

SQL Server 带参数的存储过程

存储过程是 SQL Server 中预编译的 SQL 语句集合，可以接受参数、执行操作并返回结果，下面我将详细介绍如何在 SQL Server 中创建和使用带参数的存储过程。

创建带参数的存储过程

基本语法

CREATE PROCEDURE procedure_name
    @parameter1 data_type,
    @parameter2 data_type = default_value,
    @parameter3 data_type OUTPUT
AS
BEGIN
    -- SQL 语句
END

参数类型

1. 输入参数：默认类型，用于向存储过程传递值
2. 输出参数：使用 OUTPUT 关键字，用于从存储过程返回值
3. 返回值：使用 RETURN 语句返回整数值

示例

简单输入参数示例

CREATE PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END

调用方式：

EXEC GetEmployeeById @EmployeeID = 123;
-- 或简写为
EXEC GetEmployeeById 123;

带默认值的参数示例

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT,
    @IsActive BIT = 1
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, HireDate
    FROM Employees
    WHERE DepartmentID = @DepartmentID
    AND IsActive = @IsActive;
END

调用方式：

-- 使用默认值
EXEC GetEmployeesByDepartment @DepartmentID = 5;
-- 指定参数值
EXEC GetEmployeesByDepartment @DepartmentID = 5, @IsActive = 0;

带输出参数的示例

CREATE PROCEDURE GetEmployeeCountByDepartment
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END

调用方式：

DECLARE @Count INT;
EXEC GetEmployeeCountByDepartment @DepartmentID = 5, @EmployeeCount = @Count OUTPUT;
SELECT @Count AS EmployeeCount;

带返回值的示例

CREATE PROCEDURE DeleteEmployee
    @EmployeeID INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
    BEGIN
        DELETE FROM Employees WHERE EmployeeID = @EmployeeID;
        RETURN 0; -- 成功
    END
    ELSE
    BEGIN
        RETURN -1; -- 失败，员工不存在
    END
END

调用方式：

DECLARE @Result INT;
EXEC @Result = DeleteEmployee @EmployeeID = 123;
IF @Result = 0
    PRINT '删除成功';
ELSE
    PRINT '删除失败';

复杂示例（多参数、输出参数和返回值）

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10,2),
    @OldSalary DECIMAL(10,2) OUTPUT,
    @Success BIT OUTPUT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        SELECT @OldSalary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
        IF @OldSalary IS NULL
        BEGIN
            SET @Success = 0;
            RETURN -1;
        END
        UPDATE Employees 
        SET Salary = @NewSalary 
        WHERE EmployeeID = @EmployeeID;
        SET @Success = 1;
        COMMIT TRANSACTION;
        RETURN 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        SET @Success = 0;
        RETURN ERROR_NUMBER();
    END CATCH
END

调用方式：

DECLARE @OldSal DECIMAL(10,2);
DECLARE @Success BIT;
DECLARE @RetVal INT;
EXEC @RetVal = UpdateEmployeeSalary 
    @EmployeeID = 123, 
    @NewSalary = 50000.00, 
    @OldSalary = @OldSal OUTPUT, 
    @Success = @Success OUTPUT;
SELECT 
    @RetVal AS ReturnValue,
    @OldSal AS OldSalary,
    @Success AS Success;

管理存储过程

查看存储过程定义

-- 查看特定存储过程
sp_helptext 'GetEmployeeById'
-- 查看所有存储过程
SELECT name FROM sys.procedures WHERE name LIKE 'Get%';

修改存储过程

ALTER PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, HireDate, DepartmentName
    FROM Employees E
    JOIN Departments D ON E.DepartmentID = D.DepartmentID
    WHERE EmployeeID = @EmployeeID;
END

删除存储过程

DROP PROCEDURE GetEmployeeById;

最佳实践

1. 参数命名：使用有意义的参数名，如 @EmployeeID 而不是 @ID
2. 参数数据类型：选择适当的数据类型，避免使用过大的类型
3. 默认值：为可选参数提供默认值
4. 错误处理：使用 TRY-CATCH 块处理错误
5. 事务管理：对于修改操作，使用事务确保数据一致性
6. 注释：为存储过程添加注释说明其用途和参数

通过合理使用参数，可以创建灵活、可重用的存储过程,提高数据库应用程序的效率和可维护性。