SQL Server 存储过程语法详解与调用方法

存储过程的核心概念与优势

存储过程是预编译并存储在数据库中的一组 SQL 命令，作为可执行对象存在。将其与应用程序代码（如 ASP、Java 或 PHP）分离具有显著优势：

• 性能提升：存储过程通常在数据库端预编译，执行速度快；同时减少客户端与数据库之间的网络交互次数。
• 安全性增强：隐藏底层表结构和复杂 SQL 逻辑，防止 SQL 注入风险及数据结构泄露。
• 代码复用：相同的业务逻辑只需编写一次，可在多个应用模块中调用。

存储过程分类与调用策略

根据输入参数和返回结果的不同，存储过程可分为四类。在 ASP/VBScript 环境中，通常使用 ADODB.Command 对象进行调用，设置 CommandType = 4 以表明执行的是存储过程名称。

第1步：只返回单一记录集的存储过程

目的：查询数据并返回结果集供前端显示。
T-SQL 示例：创建一个过程返回用户列表。

CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
    select * from dbo.[userinfo]
end
go

ASP 调用方法：支持 Command、Connection 或 Recordset 对象。

1. 使用 Command 对象（推荐）：

DIM MyComm, MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 
MyComm.CommandText = "getUserList"
MyComm.CommandType = 4 ' 4 表示存储过程
MyComm.Prepared = true
Set MyRst = MyComm.Execute
Set MyComm = Nothing

2. 简化写法（直接通过 Connection 执行）：

DIM MyConn, MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open MyConStr
Set MyRst = MyConn.Execute("getUserList", , 4)

第2步：没有输入输出的存储过程

目的：执行操作（如删除、更新），不返回数据。
T-SQL 示例：清空用户表。

CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
    delete from dbo.[userinfo]
end
go

ASP 调用方法：无需获取 Recordset，直接执行即可。

DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr
MyComm.CommandText = "delUserAll"
MyComm.CommandType = 4
MyComm.Prepared = true
MyComm.Execute ' 直接执行，不保存返回结果
Set MyComm = Nothing

第3步：有返回值的存储过程

目的：执行操作后反馈成功/失败状态，常用于事务处理。
T-SQL 示例：使用事务确保数据一致性，返回 1 (成功) 或 0 (失败)。

CREATE PROCEDURE dbo.delUserAll_Status
as
set nocount on
begin
    BEGIN TRANSACTION
    delete from dbo.[userinfo]
    IF @@error=0
    begin
        COMMIT TRANSACTION
        return 1
    end
    ELSE
    begin
        ROLLBACK TRANSACTION
        return 0
    end
end
go

ASP 调用方法：需通过 CreateParameter 声明返回值参数，类型通常为整型（adInteger, 代码 3 或 adSmallInt 代码 2）。

注意：系统保留 -1 到 -99 的返回值代码，自定义返回值建议从 1 开始。

第4步：有输入和输出参数的存储过程

目的：传入条件（输入参数）并获取特定结果（输出参数）。

CREATE PROCEDURE dbo.getUserName
    @UserID int,
    @UserName varchar(40) output
as
set nocount on
begin
    if @UserID is null return
    select @UserName=username
    from dbo.[userinfo]
    where UserID = @UserID
end
go

ASP 调用方法：必须严格区分输入（adInput, 代码 1）和输出（adOutput, 代码 2）参数，并按顺序追加。

关键参数类型对照表

在使用 CreateParameter 时，常用的 ADO 数据类型常量如下：

• adInteger: 3 (长整型)
• adSmallInt: 2 (短整型)
• adVarChar: 200 (变长字符串)
• adChar: 129 (定长字符串)
• adDate: 133 (日期时间)

I/O 类型常数：adInput=1, adOutput=2, adInputOutput=3, adReturnValue=4。

总结

合理使用存储过程能有效隔离业务逻辑与数据访问层。在实际开发中，推荐使用 ADODB.Command 对象配合参数化调用来处理所有类型的存储过程，特别是涉及输入输出参数时，这是唯一可靠的方式。以上就是 SQL Server 存储过程语法及调用的详细内容，更多关于数据库优化的资料请关注本站其它相关文章！