SQLServer存储过程参数的指定
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
EXECUTE myproc @first=1,@second=2,@third=3 或按照位置传递，而不命名参数名称： EXECUTE myproc 1,2,3 执行存储过程时指定参数名称，将允许按任何顺序提供参数。 如果未指定参数名称，那么必须按照与定义存储过程参数时相同
收 稿 日 期 ：2008-10-30 作 者 简 介 ：吉 文 龙 （1969-），男 ，山 西 晋 城 人 ，中 级 职 称 ，研 究 方 向 ：计 算 机 科 学 与 技 术 。
存储过程与客户端 SQL 命令操作的应用程序相比，具有下列优点： 1）使用服务器中的存储过程，可以大大提高系统效率。 对于客户端来说，只要调用已在服务器中存放的存储过程，通过网络发 送该过程名和参数，数据库服务器就可以执行该过程，在执行完成后只返回结果集数据给客户端应用程序，而无需再在网上传送大 量的命令和中间结果数据，这可以降低网络负担，而且又可以充分利用服务器的高性能来提高运算速度。 2）提高了系统的可维护性。 由于存储过程是以代码形式存在的一种数据库对象，所以它的创建和删除都很简单，而且对存储过 程的创建和删除不会影响到库中的其它数据对象。 例如在银行报表系统中，有此计算规则往往会随着时间和客户要求的改变而改 变，如果将这些业务规则的处理程序放到客户应用程序中去做，在规则发生改变时需要修改大量的源程序代码，然后重新进行编译 和链接，这样不仅增加了客户程序的维护难度，而且降低了系统的效率。 但如果将这此规则放在服务器的存储过程中，而由客户端 应用程序来调用此过程，情况将大为改观。 当某个业务的计算规则发生变化时，只需要修改或重写对应的服务器存储过程就可以 了，从而提高了系统的可维护性。 3）增强了系统的安全性。 将存储过程用在安全性上就是利用其”授权”的特性，因为存储过程就像数据库中的其它对象，如”表” 和”字段”那样可以被操作，所以只有当一个用户被授某个权限后，他才可以做与自身的权限相符合的动作。 4）增强 SQL 语言的功能和灵话性，由于 SQL 语言自身的限制，它不能声明变量，不能使用条件语句和循环语句。 存储过程利用 流控制语句和内部函数实现了 SQL 语言木身所不能做到的事，等于从另一个方而提高了 SQL 语言的功能和灵话性。 存储过程是 SQL Server 数据库中的子程序，可以使用 Transact-SQL 的 强 大 功 能 建 立 包 含 变 量 和 条 件 的 复 杂 查 询 ，最 重 要 的 是 可以传入和传出参数，其代码执行效率高、执行速度快，能增强数据的一致性及安全性，因而它具有很强的实用性。 一个存储过程可 有多达 2100 个参数，每个参数都有名称、数据类型、方向和默认值。 那如何为存储过程指定参数呢？
274 数据库与信息管理
本栏目责任编辑：闻翔军
吉文龙：SQL Server 存储过程参数的指定
的顺序提供参数。 另外，必须提供给定参数前面的所有参数，既使这些参数可选且有默认值。 例如，如果 myproc 的参数都是可选的， 那么执行 myproc 时可以仅提供第 1 个和第 2 个参数的值，但不能仅提供第 2 个和第 3 个参数的值 。 这 是 必 须 的 ，否 则 SQL Server 2000 将无法识别正在被指定的参数。
2.2 指定数据类型
存储过程中的参数要定义数据类型，这与表中的列几乎一样。 可以用 SQL Server 2000 的任何一种数据类型（包括 text 和 image
类型）定义存储过程参数，也可以用用户定义的数据类型定义存储过程参数。 对于存储过程来说，数据类型 cursor 只能用作 OUTPUT 参数。 参数的数据类型确定了该参数所接受值的类型和范围。 例如，如果用 tinyint 数据类型定义参数，则该参数将只接受 0-255 范围
关键词：存储过程；参数
中图分类号：TP311 文献标识码：A 文章编号：1009-3044(2009)02-0274-03
SQL Server Stored Procedure Parameters Specified
JI Wen-long
(Jincheng Institute of Technology, Jincheng 048026, China)
Key words: stored procedure; parameters
1 SQL Server 存储过程介绍
存储过程是由一系列 SQL 语句和流程控制语句组成的。 存储过程通过参数传递，进行判断，声明变量，以及返回信息来扩充标 准 SQL 语言的功能。 你可以把存储过程看成是以数据库对象形式存储在 SQL Server 中的一段程序或函数。 当执行存储过程时，该存 储过程是在 SQL Server 上运行，而不是在客户端发送请求。存储过程可以是一个简单的 SQL 语句，如 select * from authors。存储过程 也可以是由一系列用来对数据库表实现复杂商务规则的 SQL 语句和控制流语言语句所组成。
参考文献：
[1] 邹建.中文版 SQL SERVER 2000 开发与管理应用实例[M].北京：人民邮电出版社,2005. [2] 何文华.SQL SERVER 2000 应用开发程序[M].北京：电子工业出版社,2004. [3] 汤化平.SQL SERVER 实训教程[M].北京：清华大学出版社,2005.
2.4 指定默认值
通过为可选参数指定默认值，可创建带有可选参数的存储过程。 执行该存储过程时，如果未指定其它值，则使用默认值。
如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误，因
此指定默认值是必要的。 如果不能为参数指定合适的默认值，则可以指定 NULL 作为参数的默认值，并在未提供参数值而执行存储过程的情况下，使存
ISSN 1009-3044
CCoommppuutteerr KKnnoowwlleeddggee aAnndd TTeecchhnnoollooggyy 电电脑脑知知识识与与技技术术 Vol.5,No.2,January 2009, pp.274-276
E-mail: jslt@cccc.net.cn 第 5 卷第 2 期http:/(/2w0w0w9.d年nzs1.n月et).cn
储过程返回一条自定义消息。 如 果 默 认 值 是 包 含 嵌 入 空 格 或 标 点 符 号 的 字 符 串 ，或 者 以 数 字 开 头 (例 如 ，5xxx)，那 么 该 默 认 值 必 须 用 单 引 号 引 起 来 。 下例显示在未提供 @title 参数值的情况下执行存储过程时，get_sales_for_title 过程进行的特殊处理。 CREATE PROCEDURE get_sales_for_title @title varchar(80)=NULL --默认值为 NULL @ytd_sales int OUTPUT AS --检验 @title 参数的有效性 IF @title IS NULL BEGIN PRINT ‘ERROR :You nust specify a title value.’ RETURN END --得 到 指 定 标 题 的 商 品 并 将 它 传 给 输 出 参 数 SELECT @ytd_sales=ytd_sales FROM titles WHERE title=@title RETURN GO 下例显示 3 个参数 @first、@second 和 @third 均有默认值的过程 myproc，以及在用其它参数值执行该存储过程时所显示的值。 CREATE PROCEDURE myproc @first int=NULL --默认值为 NULL @second int=2 --默认值为 2 @third int=3 --默认值为 3 AS --显 示 这 些 值 SELECT @first,@second,@third GO EXECUTE myproc --没有提供参数 GO
显示： NULL 2 3 EXECUTE myproc 10,20,30 --提供了所有参数 GO 显示：
本栏目责任编辑：闻翔军
数据库与信息管理 275
Computer Knowledge and Technology 电脑知识与技术
第 5 卷第 2 期 (2009 年 1 月)
10 20 30 EXECUTE myproc @second=500 --仅给出了第二个参数 GO 显示： NULL 500 3 EXECUTE myproc 40,@third=50 --仅给出了第三个参数 GO 显示： 40 2 50
2 存储过程参数的指定
2.1 指定名称
每个存储过程参数都必须用惟一的名称进行定义。与标准的 Transact-SQL 变量相同，存储过程名称必须以单个 @ 字符开头，并 且必须遵从对象标识符规则。 可在存储过程中使用参数名称以获得参数值并更改它。
既可以通过显式指定参数名称并赋予适当值，也可以通过提供在 CREATE PROCEDURE 语 句 中 给 定 的 参 数 值 （不 指 定 参 数 名 称）来向存储过程传递值。 例如，如果存储过程 myproc 应有 3 个命名为 @first、@second 和 @third 的参数，则可将传递至该存储过程 的值赋予参数名称，如：
Abstract: Stored Procedures is in the SQL Server database subroutine, incoming and outgoing parameters of a stored procedure may be as many as 2100 parameters, each parameter specified when the need for the name, data type, direction and defaults.
（上接第 260 页） 为了反映不同商品和部门间的联系，我们给出一个关
联表，图 5 所示。 在表中，三元组是基本单元。 一个三元组 由 3 个参数构成，例如(1,c,d) 。 第一个参数同 bitmap 中普 通的 bit 相同。第二个参数表示第三层商品。最后一个参数 表是第二层部门。 从这些参数，我们可以看到不同项之间 的联系。 尤其是，这个关联可能会在不同层之间发生。
内的数值。 如果用与数据类型不兼容的值执行存储过程，将会返回一个错误。
2.3 指定参数方向
当调用存储过程的程序执行该存储过程时，所有过程参数都可接受输入值。 下列存储过程 get_sales_for_title 使用了输入参数。该存储过程中的 @title 参数接收调用程序所指定的书名作为输入值。SELECT 语句使用此 @title 参数获得正确的 ytd_sales 值并显示该值。 CREATE PROCEDURE get_sales_for_title @title varchar(80) --输入参数 AS --得 到 指 定 标 题 的 商 品 SELECT “YTD_SALES”=ytd_sales FROM titles WHERE title=@title RETURN GO 如果在存储过程定义中为某参数指定 OUTPUT 关键字，则存储过程将在其退出时向调用程序返回此参数的当前值。 调用程序 也必须使用 OUTPUT 关键字执行该存储过程，才能将该参数值保存到变量中以便在调用程序中使用。
执行存储过程时，输入参数既可以将它们的值设置为常量也可以使用变量。 输出参数和返回代码必须将其值返回变量。 参数和 返回代码可以与 Tracsact-SQL 变量或应用程序变量交换数据值。 如果从批处理或脚本调用存储过程，则参数和返回代码值可以使 用在同一个批处理中定义的 Tracsact-SQL 变量。
Tel:+86-551-5690963 5690964
SQL Sewk.baidu.comver 存储过程参数的指定
吉文龙
（晋城职业技术学院，山西 晋城 048062）
摘要：存储过程是 SQL Server 数据库中的子程序，可以传入和传出参数，一个存储过程可有多达 2100 个参数，每个参数在指定时需 要有名称、数据类型、方向和默认值。
收 稿 日 期 ：2008-10-30 作 者 简 介 ：吉 文 龙 （1969-），男 ，山 西 晋 城 人 ，中 级 职 称 ，研 究 方 向 ：计 算 机 科 学 与 技 术 。
存储过程与客户端 SQL 命令操作的应用程序相比，具有下列优点： 1）使用服务器中的存储过程，可以大大提高系统效率。 对于客户端来说，只要调用已在服务器中存放的存储过程，通过网络发 送该过程名和参数，数据库服务器就可以执行该过程，在执行完成后只返回结果集数据给客户端应用程序，而无需再在网上传送大 量的命令和中间结果数据，这可以降低网络负担，而且又可以充分利用服务器的高性能来提高运算速度。 2）提高了系统的可维护性。 由于存储过程是以代码形式存在的一种数据库对象，所以它的创建和删除都很简单，而且对存储过 程的创建和删除不会影响到库中的其它数据对象。 例如在银行报表系统中，有此计算规则往往会随着时间和客户要求的改变而改 变，如果将这些业务规则的处理程序放到客户应用程序中去做，在规则发生改变时需要修改大量的源程序代码，然后重新进行编译 和链接，这样不仅增加了客户程序的维护难度，而且降低了系统的效率。 但如果将这此规则放在服务器的存储过程中，而由客户端 应用程序来调用此过程，情况将大为改观。 当某个业务的计算规则发生变化时，只需要修改或重写对应的服务器存储过程就可以 了，从而提高了系统的可维护性。 3）增强了系统的安全性。 将存储过程用在安全性上就是利用其”授权”的特性，因为存储过程就像数据库中的其它对象，如”表” 和”字段”那样可以被操作，所以只有当一个用户被授某个权限后，他才可以做与自身的权限相符合的动作。 4）增强 SQL 语言的功能和灵话性，由于 SQL 语言自身的限制，它不能声明变量，不能使用条件语句和循环语句。 存储过程利用 流控制语句和内部函数实现了 SQL 语言木身所不能做到的事，等于从另一个方而提高了 SQL 语言的功能和灵话性。 存储过程是 SQL Server 数据库中的子程序，可以使用 Transact-SQL 的 强 大 功 能 建 立 包 含 变 量 和 条 件 的 复 杂 查 询 ，最 重 要 的 是 可以传入和传出参数，其代码执行效率高、执行速度快，能增强数据的一致性及安全性，因而它具有很强的实用性。 一个存储过程可 有多达 2100 个参数，每个参数都有名称、数据类型、方向和默认值。 那如何为存储过程指定参数呢？
274 数据库与信息管理
本栏目责任编辑：闻翔军
吉文龙：SQL Server 存储过程参数的指定
的顺序提供参数。 另外，必须提供给定参数前面的所有参数，既使这些参数可选且有默认值。 例如，如果 myproc 的参数都是可选的， 那么执行 myproc 时可以仅提供第 1 个和第 2 个参数的值，但不能仅提供第 2 个和第 3 个参数的值 。 这 是 必 须 的 ，否 则 SQL Server 2000 将无法识别正在被指定的参数。
2.2 指定数据类型
存储过程中的参数要定义数据类型，这与表中的列几乎一样。 可以用 SQL Server 2000 的任何一种数据类型（包括 text 和 image
类型）定义存储过程参数，也可以用用户定义的数据类型定义存储过程参数。 对于存储过程来说，数据类型 cursor 只能用作 OUTPUT 参数。 参数的数据类型确定了该参数所接受值的类型和范围。 例如，如果用 tinyint 数据类型定义参数，则该参数将只接受 0-255 范围
关键词：存储过程；参数
中图分类号：TP311 文献标识码：A 文章编号：1009-3044(2009)02-0274-03
SQL Server Stored Procedure Parameters Specified
JI Wen-long
(Jincheng Institute of Technology, Jincheng 048026, China)
Key words: stored procedure; parameters
1 SQL Server 存储过程介绍
存储过程是由一系列 SQL 语句和流程控制语句组成的。 存储过程通过参数传递，进行判断，声明变量，以及返回信息来扩充标 准 SQL 语言的功能。 你可以把存储过程看成是以数据库对象形式存储在 SQL Server 中的一段程序或函数。 当执行存储过程时，该存 储过程是在 SQL Server 上运行，而不是在客户端发送请求。存储过程可以是一个简单的 SQL 语句，如 select * from authors。存储过程 也可以是由一系列用来对数据库表实现复杂商务规则的 SQL 语句和控制流语言语句所组成。
参考文献：
[1] 邹建.中文版 SQL SERVER 2000 开发与管理应用实例[M].北京：人民邮电出版社,2005. [2] 何文华.SQL SERVER 2000 应用开发程序[M].北京：电子工业出版社,2004. [3] 汤化平.SQL SERVER 实训教程[M].北京：清华大学出版社,2005.
2.4 指定默认值
通过为可选参数指定默认值，可创建带有可选参数的存储过程。 执行该存储过程时，如果未指定其它值，则使用默认值。
如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误，因
此指定默认值是必要的。 如果不能为参数指定合适的默认值，则可以指定 NULL 作为参数的默认值，并在未提供参数值而执行存储过程的情况下，使存
ISSN 1009-3044
CCoommppuutteerr KKnnoowwlleeddggee aAnndd TTeecchhnnoollooggyy 电电脑脑知知识识与与技技术术 Vol.5,No.2,January 2009, pp.274-276
E-mail: jslt@cccc.net.cn 第 5 卷第 2 期http:/(/2w0w0w9.d年nzs1.n月et).cn
储过程返回一条自定义消息。 如 果 默 认 值 是 包 含 嵌 入 空 格 或 标 点 符 号 的 字 符 串 ，或 者 以 数 字 开 头 (例 如 ，5xxx)，那 么 该 默 认 值 必 须 用 单 引 号 引 起 来 。 下例显示在未提供 @title 参数值的情况下执行存储过程时，get_sales_for_title 过程进行的特殊处理。 CREATE PROCEDURE get_sales_for_title @title varchar(80)=NULL --默认值为 NULL @ytd_sales int OUTPUT AS --检验 @title 参数的有效性 IF @title IS NULL BEGIN PRINT ‘ERROR :You nust specify a title value.’ RETURN END --得 到 指 定 标 题 的 商 品 并 将 它 传 给 输 出 参 数 SELECT @ytd_sales=ytd_sales FROM titles WHERE title=@title RETURN GO 下例显示 3 个参数 @first、@second 和 @third 均有默认值的过程 myproc，以及在用其它参数值执行该存储过程时所显示的值。 CREATE PROCEDURE myproc @first int=NULL --默认值为 NULL @second int=2 --默认值为 2 @third int=3 --默认值为 3 AS --显 示 这 些 值 SELECT @first,@second,@third GO EXECUTE myproc --没有提供参数 GO
显示： NULL 2 3 EXECUTE myproc 10,20,30 --提供了所有参数 GO 显示：
本栏目责任编辑：闻翔军
数据库与信息管理 275
Computer Knowledge and Technology 电脑知识与技术
第 5 卷第 2 期 (2009 年 1 月)
10 20 30 EXECUTE myproc @second=500 --仅给出了第二个参数 GO 显示： NULL 500 3 EXECUTE myproc 40,@third=50 --仅给出了第三个参数 GO 显示： 40 2 50
2 存储过程参数的指定
2.1 指定名称
每个存储过程参数都必须用惟一的名称进行定义。与标准的 Transact-SQL 变量相同，存储过程名称必须以单个 @ 字符开头，并 且必须遵从对象标识符规则。 可在存储过程中使用参数名称以获得参数值并更改它。
既可以通过显式指定参数名称并赋予适当值，也可以通过提供在 CREATE PROCEDURE 语 句 中 给 定 的 参 数 值 （不 指 定 参 数 名 称）来向存储过程传递值。 例如，如果存储过程 myproc 应有 3 个命名为 @first、@second 和 @third 的参数，则可将传递至该存储过程 的值赋予参数名称，如：
Abstract: Stored Procedures is in the SQL Server database subroutine, incoming and outgoing parameters of a stored procedure may be as many as 2100 parameters, each parameter specified when the need for the name, data type, direction and defaults.
（上接第 260 页） 为了反映不同商品和部门间的联系，我们给出一个关
联表，图 5 所示。 在表中，三元组是基本单元。 一个三元组 由 3 个参数构成，例如(1,c,d) 。 第一个参数同 bitmap 中普 通的 bit 相同。第二个参数表示第三层商品。最后一个参数 表是第二层部门。 从这些参数，我们可以看到不同项之间 的联系。 尤其是，这个关联可能会在不同层之间发生。
内的数值。 如果用与数据类型不兼容的值执行存储过程，将会返回一个错误。
2.3 指定参数方向
当调用存储过程的程序执行该存储过程时，所有过程参数都可接受输入值。 下列存储过程 get_sales_for_title 使用了输入参数。该存储过程中的 @title 参数接收调用程序所指定的书名作为输入值。SELECT 语句使用此 @title 参数获得正确的 ytd_sales 值并显示该值。 CREATE PROCEDURE get_sales_for_title @title varchar(80) --输入参数 AS --得 到 指 定 标 题 的 商 品 SELECT “YTD_SALES”=ytd_sales FROM titles WHERE title=@title RETURN GO 如果在存储过程定义中为某参数指定 OUTPUT 关键字，则存储过程将在其退出时向调用程序返回此参数的当前值。 调用程序 也必须使用 OUTPUT 关键字执行该存储过程，才能将该参数值保存到变量中以便在调用程序中使用。
执行存储过程时，输入参数既可以将它们的值设置为常量也可以使用变量。 输出参数和返回代码必须将其值返回变量。 参数和 返回代码可以与 Tracsact-SQL 变量或应用程序变量交换数据值。 如果从批处理或脚本调用存储过程，则参数和返回代码值可以使 用在同一个批处理中定义的 Tracsact-SQL 变量。
Tel:+86-551-5690963 5690964
SQL Sewk.baidu.comver 存储过程参数的指定
吉文龙
（晋城职业技术学院，山西 晋城 048062）
摘要：存储过程是 SQL Server 数据库中的子程序，可以传入和传出参数，一个存储过程可有多达 2100 个参数，每个参数在指定时需 要有名称、数据类型、方向和默认值。