11.2b 带输入参数与输出参数的存储过程

📄 一句话概括

输入参数方向是「进去」，OUTPUT 参数方向是「回来」——后者用来把计算结果带回调用方。 RETURN 只能回一个整数，专门报状态码（0 成功、非 0 各类错误），它不是业务数据。 默认值只在「不传这个参数」时生效；传了 NULL 就是 NULL，不会变成默认值。

📝 本章和前面几章的关系

11.2a 建的是「不带参数的过程」，这一节讲参数——过程能不能复用，全看参数设计得好不好。 教材 11.2 的后三小节：输入参数、输出参数、默认值与返回值。 三样东西方向不同，混起来用是新手最常见的错。 演示里三类参数各跑一遍，输出参数和返回值都用 Python 复刻了语义并真跑出结果：三个商品类别的总库存 80／280／12，三个状态码 0／1／2。

一、输入参数：方向是「进去」

CREATE PROCEDURE proc_by_type
    @type_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, gname, price FROM tb_goods WHERE type_id = @type_id;
END;
GO

EXEC proc_by_type @type_id = 2;
GO

参数前那个 @ 是必须的。 漏了它，SQL Server 会把 type_id 当成列名去找， 找不到就报 Must declare the scalar variable。 调用时建议写「参数名 = 值」，而不是只写值。参数一多，位置写错了根本看不出来——两个都是 INT 的时候尤其危险。 过程里用参数，就跟用普通变量一样：WHERE type_id = @type_id。演示里这条查出 2 号类的三件商品：罗技鼠标、机械键盘、移动硬盘。

二、默认值：不传也能跑

CREATE PROCEDURE proc_by_type2
    @type_id INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, gname FROM tb_goods WHERE type_id = @type_id;
END;
GO

不传参数时用默认值 1（笔记本类），传了就用传的。演示里两种都跑了：不传得到 2 行（联想笔记本、戴尔显示器），传 3 得到 1 行（打印机）。 坑在这里：默认值只在「压根不传这个参数」时生效。 如果你显式传了一个 NULL，那它就是 NULL——不会退回去用默认值。 所以过程体里如果拿参数去比较，最好先 ISNULL 一下。 不然 WHERE type_id = @type_id 碰上 NULL 会一行都查不出来（NULL 比较永远是未知）。

三、输出参数：把值带回来

CREATE PROCEDURE proc_stock_sum
    @type_id INT,
    @total INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @total = SUM(stock) FROM tb_goods WHERE type_id = @type_id;
END;
GO

DECLARE @t INT;
EXEC proc_stock_sum @type_id = 1, @total = @t OUTPUT;
SELECT @t AS 总库存;
GO

为什么要这么绕？ 因为调用一个过程时， 它返回的结果集只能「看」，不能直接参与后面的计算。你要用这个数继续算， 只能靠 OUTPUT 参数把它捞出来，塞进一个变量里。 两边都要写 OUTPUT：定义的时候写一次，调用的时候再写一次。 漏了任何一边，值都传不回来，而且不报错——这是最阴的一处。 演示里三个类别的总库存是 80、280、12。 1 号类 30+50；2 号类 200+80+0；3 号类只有打印机 12。 这些是「值」，能直接拿去算——这就是 OUTPUT 的用处。

四、返回值：只回一个整数，专门报状态

CREATE PROCEDURE proc_try_insert
    @gname NVARCHAR(50),
    @price MONEY
AS
BEGIN
    IF @price <= 0 RETURN 1;
    IF EXISTS (SELECT 1 FROM tb_goods WHERE gname = @gname) RETURN 2;
    INSERT INTO tb_goods (gname, price) VALUES (@gname, @price);
    RETURN 0;
END;
GO

RETURN 只能回一个整数。 习惯上 0 表示成功、非 0 表示各类错误。演示里三个状态码一次跑出来：0 成功、1 价格不合法、2 重名。 它跟 OUTPUT 参数不是一回事。 RETURN 是「过程执行完的状态码」， 只回一个数、专门给程序判断要不要继续；OUTPUT 是「业务数据」，可以有很多个。 调用方不接 RETURN 的值，它就悄悄丢掉，不报错。 要接得写 EXEC @rc = proc_name ...—— 很多人写了 RETURN 却从来没接过，等于白写。

五、三样东西放一起对比

东西	方向	能传什么	典型用途
输入参数	进去	任意类型、多个	筛选条件、要插入的值
`OUTPUT` 参数	回来	任意类型、多个	算出来的合计、新插入的 id
`RETURN`	回来	只有整数	成功／失败的状码

一句话记法：RETURN 报状态，OUTPUT 带数据。 两者可以同时用—— 过程既返回一个状态码，又通过 OUTPUT 带回几个业务值。 参数设计上有一条经验： 能靠 RETURN 表达的，别用 OUTPUT。 状态码只有一个，用 RETURN 最自然；OUTPUT 留给真正的业务数据。

六、动手 Demo：输入参数、默认值、输出参数与 RETURN 状态码

这一段把三类参数各演一遍： 输入参数查一类商品、默认值两种调用对比、OUTPUT 参数带回三个类别的总库存、 RETURN 跑出三种状态码。 注意 OUTPUT 和 RETURN 那两张表：一张是「值」，一张是「状态码」，性质完全不同。

import sqlite3

c = sqlite3.connect(u":memory:")
# ... 建六表、装样例数据 ...

# 输入参数：按类别查
print(c.execute(u"SELECT id, gname, price FROM tb_goods WHERE type_id = ?",
                (2,)).fetchall())

# 默认值：不传用 1，传了就用传的
for tag, v in ((u"不传", 1), (u"传 3", 3)):
    rows = c.execute(u"SELECT gname FROM tb_goods WHERE type_id = ?", (v,)).fetchall()
    print(tag, len(rows), rows)


# OUTPUT 参数：带回一个值，不是结果集
def proc_stock_sum(type_id):
    return c.execute(u"SELECT SUM(stock) FROM tb_goods WHERE type_id = ?",
                     (type_id,)).fetchone()[0]


print([proc_stock_sum(i) for i in (1, 2, 3)])


# RETURN：只回状态码
def proc_try_insert(gname, price):
    if price <= 0:
        return 1
    if c.execute(u"SELECT 1 FROM tb_goods WHERE gname = ?", (gname,)).fetchone():
        return 2
    return 0


print(proc_try_insert(u"新商品", 99), proc_try_insert(u"又一个", -5),
      proc_try_insert(u"联想笔记本", 4999))

实跑输出：

--------------------------------------------------------------------
输入参数、输出参数、默认值与返回值
--------------------------------------------------------------------


  ① 输入参数：方向是「进去」
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE PROCEDURE proc_by_type
  |     @type_id INT
  | AS
  | BEGIN
  |     SET NOCOUNT ON;
  |     SELECT id, gname, price FROM tb_goods WHERE type_id = @type_id;
  | END;
  | GO
  | EXEC proc_by_type @type_id = 2;
  | GO

  调用时建议写「参数名 = 值」，而不是只写值——参数一多，位置写错了根本看不出来。

  1> SELECT id, gname, price FROM tb_goods WHERE type_id = 2
  id    gname     price
  ---------------------
  1003  罗技鼠标  89.9
  1004  机械键盘  399
  1005  移动硬盘  549
  (3 行受影响)
  本沙箱等价写法就是这一条——把 2 换成变量，就是过程里的样子。

  ② 默认值：不传也能跑
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE PROCEDURE proc_by_type2
  |     @type_id INT = 1
  | AS
  | BEGIN
  |     SET NOCOUNT ON;
  |     SELECT id, gname FROM tb_goods WHERE type_id = @type_id;
  | END;
  | GO
  | EXEC proc_by_type2;
  | GO

  不传参数时用默认值 1（笔记本类），传了就用传的。演示里两种都跑：

  EXEC proc_by_type2 不传（走默认值 1） -> 2 行：联想笔记本、戴尔显示器

  EXEC proc_by_type2 传 3 -> 1 行：打印机
  默认值有个坑：它只在「不传这个参数」时生效。传了 NULL 就是 NULL，不会变成默认值。

  ③ 输出参数：把值带回来
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE PROCEDURE proc_stock_sum
  |     @type_id INT,
  |     @total INT OUTPUT
  | AS
  | BEGIN
  |     SET NOCOUNT ON;
  |     SELECT @total = SUM(stock) FROM tb_goods WHERE type_id = @type_id;
  | END;
  | GO
  | DECLARE @t INT;
  | EXEC proc_stock_sum @type_id = 1, @total = @t OUTPUT;
  | SELECT @t AS 总库存;
  | GO

  OUTPUT 参数的意义：过程不返回结果集，而是把计算结果塞回给调用者的变量。
  为什么要这么绕？因为 CALL 一个过程时，结果集只能「看」，不能直接参与后面的计算。
  要用这个数继续算，只能靠 OUTPUT 参数把它捞出来。演示里用 Python 复刻这个语义：
| 调用 | OUTPUT 参数带回的值 |
|---|---:|
| EXEC proc_stock_sum @type_id = 1, @total = @t OUTPUT | 80 |
| EXEC proc_stock_sum @type_id = 2, @total = @t OUTPUT | 280 |
| EXEC proc_stock_sum @type_id = 3, @total = @t OUTPUT | 12 |

  三类商品总库存：80、280、12。这些都是「值」，不是结果集——能直接拿去算，这就是 OUTPUT 的用处。

  ④ 返回值：只回一个整数，专门报状态
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE PROCEDURE proc_try_insert
  |     @gname NVARCHAR(50),
  |     @price MONEY
  | AS
  | BEGIN
  |     IF @price <= 0 RETURN 1;          -- 1：价格不合法
  |     IF EXISTS (SELECT 1 FROM tb_goods WHERE gname = @gname) RETURN 2;  -- 2：重名
  |     INSERT INTO tb_goods (gname, price) VALUES (@gname, @price);
  |     RETURN 0;                          -- 0：成功
  | END;
  | GO

  RETURN 只能回一个整数，习惯上 0 表示成功、非 0 表示各类错误。
  它跟 OUTPUT 参数不是一回事：RETURN 是「过程执行完的状态码」，OUTPUT 是「业务数据」。
| 调用 | RETURN 状态 | 含义 |
|---|---:|---|
| proc_try_insert '新商品', 99 | 0 | 成功 |
| proc_try_insert '又一个', -5 | 1 | 价格不合法 |
| proc_try_insert '联想笔记本', 4999 | 2 | 重名 |

  三个状态码 0、1、2 一次跑出来。调用方拿到 1 就该提示用户，拿到 0 才继续往下走。
  ! SQL Server 里 RETURN 的值要用 EXEC @rc = proc_name 接；不接的话它悄悄丢掉，不报错。

第一段三行：罗技鼠标 89.9、机械键盘 399、移动硬盘 549。 移动硬盘库存是 0、也已下架，但 type_id 还是 2——所以按类别查它照样出现。 要排除下架的，WHERE 里还得加 is_on = 1。 第二段两个数：不传得到 2 行，传 3 得到 1 行。 这就是默认值的效果—— 同一句 SQL，传不传参数走不同分支。 第三段 [80, 280, 12] 是 OUTPUT 的语义：三个数，不是三张结果集。 能直接拿去算（比如加起来 372），而结果集不行——结果集只能看。 最后一行 0 1 2 是 RETURN 的语义。 注意 proc_try_insert 里那条成功的分支没有真的插入 （演示里只返回状态码）——真实的过程里它会 INSERT 之后再 RETURN 0。

30 秒实验

给 proc_by_type 加第二个参数 @max_price MONEY，调用时不传它。 会报「缺少参数」——除非你给它写了默认值。
把 EXEC proc_stock_sum @type_id = 1, @total = @t OUTPUT 里那个 OUTPUT 删掉，想一想会怎样。 @t 拿不到值，还是声明时的 NULL，而且不报错。
写一个过程，既 RETURN 状态码、又用两个 OUTPUT 参数带回合计和条数。 这就是「批量操作 + 返回统计」的标准形态。

常见误区

「OUTPUT 参数跟 RETURN 差不多，用一个就行」。RETURN 只能回一个整数， 业务数据（金额、id、统计）只能走 OUTPUT。
「默认值能挡住 NULL」。挡不住。 显式传 NULL 就是 NULL——要挡得在过程体里 ISNULL(@x, 默认值)。
「OUTPUT 只在定义时写一次就行」。调用时也得写。 漏了不报错，值就是拿不到——查半天查不出原因的那种错。

自测三问（点开对答案）

1. `OUTPUT` 参数和 `RETURN` 有什么区别？ —— **`RETURN` 只回一个整数，用来报状态码（0 成功、非 0 各类错误）；** **`OUTPUT` 可以有很多个、任意类型，用来带业务数据。** 2. 默认值在什么情况下不生效？ —— **显式传了 `NULL` 的时候。** 默认值只在「压根不传这个参数」时生效， **传 `NULL` 就是 `NULL`，不会退回默认值。** 3. 调用带 `OUTPUT` 参数的过程时，漏写 `OUTPUT` 会怎样？ —— **不报错，但值拿不到。** 调用方的变量还是原来的值（通常是 `NULL`）—— **这种错最难查，因为没有错误信息。**

下一篇 11.3 讲管理：查看过程正文、ALTER 改定义、DROP 删掉、WITH RECOMPILE 重新编译， 以及嵌套调用为什么会让排错变难。

☐ （本篇完，下一篇 11.3 管理存储过程）