sql存储过程完全教程

wojkxlq

　　sql存储过程完全教程
　　目录
　　1.sql存储过程概述
　　2.SQL存储过程创建E2C~,x0@iC|4W7c*H
　　3.sql存储过程及应用)z,e1A{3o
　　4.各种存储过程使用指南A~k?/L cS`MdRE_
　　5.ASP中存储过程调用的两种方式及比较,Q0_'{ a&b&J M a}
　　6.SQL存储过程在.NET数据库中的应用
　　7.使用SQL存储过程要特别注意的问题
　　2006-8-5 19:54 天涯风云
　　1.sql存储过程概述
　　在大型数据库系统中存储过程和触发器具有很重要的作用。无论是存储过程还是触发器都是SQL 语句和流程控制语句的集合。就本质而言触发器也是一种存储过程。存储过程在运算时生成执行方式所以以后对其再运行时其执行速度很快。SQL Server 2000 不仅提供了用户自定义存储过程的功能而且也提供了许多可作为工具使用的系统存储过程。5li1`8F2i8QD
　　存储过程的概念
　　2]{F ]9\l/d
　　存储过程Stored Procedure是一组为了完成特定功能的SQL 语句集经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。1cz$PxXC5g;I
　　yyRyrf
　　在SQL Server 的系列版本中存储过程分为两类系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀并且系统存储过程主要是从系统表中获取信息从而为系统管理员管理SQL Server 提供支持。通过系统存储过程MS SQL Server 中的许多管理性或信息性的活动如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中但是仍可以在其它数据库中对其进行调用在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能如查询用户所需数据信息的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。lO3h4h0WQ6P
　　tv+xR*P#[n9b
　　存储过程的优点QsYcs @
　　当利用MS SQL Server 创建一个应用程序时Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程有两种方法。其一是在本地存储Transaction- SQL 程序并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中并创建应用程序来调用存储过程对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集结果集的格式由调用者确定返回状态值给调用者指明调用是成功或是失败包括针对数据库的操作语句并且可以在一个存储过程中调用另一存储过程。
　　Pjs8k8yZKP
　　我们通常更偏爱于使用第二种方法即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序原因在于存储过程具有以下优点3o0z5UO u3o#\D/\*i
　　6K \R1i(j6S r
　　1 存储过程允许标准组件式编程u*pE8RsV5mI
　　spzPj6N[K&]
　　存储过程在被创建以后可以在程序中被多次调用而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改但对应用程序源代码毫无影响因为应用程序源代码只包含存储过程的调用语句从而极大地提高了程序的可移植性。9M7|+KS;`TM
　　YF\8`0p EHTa-gT9G6a
　　2 存储过程能够实现较快的执行速度!k2T B"{Y ?
　　|0F%j)S-A Tw(M
　　如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的在首次运行一个存储过程时查询优化器对其进行分析、优化并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化因此速度相对要慢一些。3kU.Y&h%Wk$X
　　l`%C3{UO6n,`
　　3 存储过程能够减少网络流量)@(ZVg^(bXDSM
　　对于同一个针对数据数据库对象的操作如查询、修改如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程那么当在客户计算机上调用该存储过程时网络中传送的只是该调用语句否则将是多条SQL 语句从而大大增加了网络流量降低网络负载。.B5g/M.Z!b8\1qv!{3R
　　4 存储过程可被作为一种安全机制来充分利用va.n(|p{y/F
　　系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的限制避免非授权用户对数据的访问保证数据的安全。我们将在14 章“SQLServer 的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍
　　注意存储过程虽然既有参数又有返回值但是它与函数不同。存储过程的返回值只是指明执行是否成功并且它不能像函数那样被直接调用也就是在调用存储过程时在存储过程名字前一定要有EXEC保留字。
　　2006-8-5 19:54 天涯风云
　　2.SQL存储过程创建
　　创建存储过程存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。
　　可以创建一个过程供永久使用或在一个会话中临时使用局部临时过程或在所有会话中临时使用全局临时过程。
　　Z"SDbC;THr-n GV
　　也可以创建在 Microsoft? SQL Server? 启动时自动运行的存储过程。
　　m4Y1x+VbAB"z
　　语法!F+}-CjGe Y)r^$q8y
　　CREATE PROC [ EDURE ] procedure_name [ ; number ]
　　[ { @parameter data_type }
　　[ VARYING ] [ = default ] [ OUTPUT ]
　　] [ ,...n ]
　　%h"ueu R~W
　　[ WITH'i:uA&MX \7LI1A
　　{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] "?Ib+QS.B
　　[ FOR REPLICATION ]
　　AS sql_statement [ ...n ]
　　2006-8-5 19:55 天涯风云
　　参数
　　procedure_name!JWN(?0x
　　新存储过程的名称。过程名必须符合标识符规则且对于数据库及其所有者必须唯一。-~D8q-~'g/FRy
　　要创建局部临时过程可以在 procedure_name 前面加一个编号符 (#procedure_name)要创建全局临时过程可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称包括 # 或 ##不能超过 128 个字符。指定过程所有者的名称是可选的。W"^2igZ~"sE^
　　Z m{\"cA)QW:Js
　　;number
　　是可选的整数用来对同名的过程分组以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符则数字不应包含在标识符中只应在 procedure_name 前后使用适当的定界符。2D|r8}Z [[q
　　@parameter)EA'X|J
　　过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值除非定义了该参数的默认值。存储过程最多可以有 2.100 个参数。B.tZ"O;b}x!`f'Dd
　　n1xg5xK9z!cC:X
　　使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身相同的参数名称可以用在其它过程中。默认情况下参数只能代替常量而不能用于代替表名、列名或其它数据库对象的名称。
　　data_type
　　i+sbm'Tz\| N5@)P
　　参数的数据类型。所有数据类型包括 text、ntext 和 image均可以用作存储过程的参数。不过cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor也必须同时指定 VARYING 和 OUTPUT 关键字。
　　说明　对于可以是 cursor 数据类型的输出参数没有最大数目的限制。
　　"tTqqH8mN:yK.P i
　　VARYING
　　指定作为输出参数支持的结果集由存储过程动态构造内容可以变化。仅适用于游标参数。
　　default
　　参数的默认值。如果定义了默认值不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字那么默认值中可以包含通配符%、_、[] 和 [^]。0M`x x[y5c+R
　　;T+m+ls"d-pc TH
　　OUTPUT$ht qK4yL
　　表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。Q*h E:}3nn+S
　　cDM&rP]S
　　n
　　表示最多可以指定 2.100 个参数的占位符。'Dc#@7d.za+K
　　{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}1^a1\lETq7Y
　　RECOMPILE 表明 SQL Server 不会缓存该过程的计划该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时请使用 RECOMPILE 选项。
　　j yu'r6d-WAI:Gm
　　ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。X^$q8AL
　　P#s k&D1lR
　　说明　在升级过程中SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。 L njZ%u f
　　xW6D$Yz {KZJ
　　(F0v{I7H D!D7ObP
　　FOR REPLICATION5oau'PbV%U
　　.znT2j"D1e&R
　　指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。:cM|8x1@9Hss9bq
　　AS~&LU @$Y!{jhY
　　En B$h:N(q*e/fQ2c
　　指定过程要执行的操作。
　　(rp|{%f!vd$?H3n
　　sql_statementAW$S8ZR0W1s/~
　　CS5h-HOEI'a
　　过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
　　n
　　cIA1F0q'x
　　是表示此过程可以包含多条 Transact-SQL 语句的占位符。
　　8g @n,DW'A
　　注释_]aGs,L
　　存储过程的最大大小为 128 MB。
　　2006-8-5 19:55 天涯风云
　　用户定义的存储过程只能在当前数据库中创建临时过程除外临时过程总是在 tempdb 中创建。在单个批处理中CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合使用。 *Z.wlj&?
　　R z1H!d`

　　默认情况下参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或>
　　建议在存储过程的任何 CREATE TABLE 或>　　在创建或更改存储过程时SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时将使用这些原始设置。因此所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。
　　}$R0_ YG a2PG
　　其它 SET 选项例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置应在过程开头添加一条 SET 语句以确保设置正确。从存储过程中执行 SET 语句时该设置只在存储过程完成之前有效。之后设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项而不会影响存储过程的逻辑。~Ky6Nz p Io N E)u
　　5O3\ \k-t's
　　天涯风云  19:30:43
　　说明　SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串由兼容级别设置控制。如果兼容级别小于或等于 65SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70则 SQL Server 将空字符串解释为空字符串。,vR6eb F0N
　　获得有关存储过程的信息
　　若要显示用来创建过程的文本请在过程所在的数据库中执行 sp_helptext并使用过程名作为参数。
　　+DTDMt:\c n
　　;ov"}x@Nj
　　说明　使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看。w"yT0nC x)@$@
　　若要显示有关过程引用的对象的报表请使用 sp_depends。
　　若要为过程重命名请使用 sp_rename。
　　2006-8-5 19:55 天涯风云
　　引用对象W$v$u;H,\%l*j!U
　　SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时只进行语法检查。执行时如果高速缓存中尚无有效的计划则编译存储过程以生成执行计划。只有在编译过程中才解析存储过程中引用的所有对象。因此如果语法正确的存储过程引用了不存在的对象则仍可以成功创建但在运行时将失败因为所引用的对象不存在。0[5] t y%S v@J
　　延迟名称解析和兼容级别V[4[#`%J L[M5v
　　SQL Server 允许 Transact-SQL 存储过程在创建时引用不存在的表。这种能力称为延迟名称解析。不过如果 Transact-SQL 存储过程引用了该存储过程中定义的表而兼容级别设置通过执行 sp_dbcmptlevel 来设置为 65则在创建时会发出警告信息。而如果在运行时所引用的表不存在将返回错误信息。9sOy]T _#b q)]
　　执行存储过程
　　成功执行 CREATE PROCEDURE 语句后过程名称将存储在 sysobjects 系统表中而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次执行时将编译该过程以确定检索数据的最佳访问计划。
　　SP)t LEQ?
　　使用 cursor 数据类型的参数0Nj2R3V~2IRl
　　存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了 cursor 数据类型也必须指定 VARYING 和 OUTPUT 参数。如果为某个参数指定了 VARYING 关键字则数据类型必须是 cursor并且必须指定 OUTPUT 关键字。
　　说明　cursor 数据类型不能通过数据库 API例如 OLE DB、ODBC、ADO 和 DB-Library绑定到应用程序变量上。因为必须先绑定 OUTPUT 参数应用程序才可以执行存储过程所以带有 cursor OUTPUT 参数的存储过程不能通过数据库 API 调用。只有将 cursor OUTPUT 变量赋值给 Transact-SQL 局部 cursor 变量时才可以通过 Transact-SQL 批处理、存储过程或触发器调用这些过程。 V-H!djK ^7|
　　c0h!l O G:~:BUD
　　Cursor 输出参数
　　在执行过程时以下规则适用于 cursor 输出参数
　　对于只进游标游标的结果集中返回的行只是那些存储过程执行结束时处于或超出游标位置的行例如 6k7D$I"B@
　　在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。 "cY$~ Zni
　　过程提取结果集 RS 的头 5 行。9u C;U| r9w"?9R4w
　　rTkB$L#et
　　+qqsIB?}
　　过程返回到其调用者。9@E-m)T&Ru E&OCM
　　返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成调用者中的游标处于 RS 的第一行之前。
　　对于只进游标如果存储过程完成后游标位于第一行的前面则整个结果集将返回给调用批处理、存储过程或触发器。返回时游标将位于第一行的前面。k:x W P{@6f.ao
　　对于只进游标如果存储过程完成后游标的位置超出最后一行的结尾则为调用批处理、存储过程或触发器返回空结果集。 ^8VL8h8NlU
　　{ N6I2l;_Xz;Q#U1p
　　说明　空结果集与空值不同。za9y:W$E
　　对于可滚动游标在存储过程执行结束时结果集中的所有行均会返回给调用批处理、存储过程或触发器。返回时游标保留在过程中最后一次执行提取时的位置。
　　o*fM9O:l
　　对于任意类型的游标如果游标关闭则将空值传递回调用批处理、存储过程或触发器。如果将游标指派给一个参数但该游标从未打开过也会出现这种情况。 6V} o4r6Q
　　]u {c&gsP#LQ
　　说明　关闭状态只有在返回时才有影响。例如可以在过程中关闭游标稍后再打开游标然后将该游标的结果集返回给调用批处理、存储过程或触发器。n,O'Ma2Al E%qv
　　临时存储过程l7|jr@
　　SQL Server 支持两种临时过程局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。
　　b!v$_N7\h,VC
　　临时过程用 # 和 ## 命名可以由任何用户创建。创建过程后局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过程则所有用户均可以访问该过程权限不能显式废除。只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户才可以在该数据库中显式创建临时过程不使用编号符命名。可以授予或废除这些过程中的权限。 z{|k-BAJ6H
　　'Y-GB*{n;a'? c
　　说明　频繁使用临时存储过程会在 tempdb 中的系统表上产生争用从而对性能产生负面影响。建议使用 sp_executesql 代替。sp_executesql 不在系统表中存储数据因此可以避免这一问题。
　　2006-8-5 19:56 天涯风云
　　自动执行存储过程
　　SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。 ,r_/T&p`-?w
　　&T*H^7[H
　　对启动过程的数目没有限制但是要注意每个启动过程在执行时都会占用一个连接。如果必须在启动时执行多个过程但不需要并行执行则可以指定一个过程作为启动过程让该过程调用其它过程。这样就只占用一个连接。
　　s+PgfKCbP(@
　　在启动时恢复了最后一个数据库后即开始执行存储过程。若要跳过这些存储过程的执行请将启动参数指定为跟踪标记 4022。如果以最低配置启动 SQL Server使用 -f 标记则启动存储过程也不会执行。v J8tH+Ur/O:O
　　若要创建启动存储过程必须作为 sysadmin 固定服务器角色的成员登录并在 master 数据库中创建存储过程。
　　使用 sp_procoption 可以
　　"[C6XZ,H#~~u$M
　　将现有存储过程指定为启动过程。
　　&A)C8VPJL
　　停止在 SQL Server 启动时执行过程。TP|.F:^!T0U}
　　-YIf9ni
　　$F QCxpz
　　查看 SQL Server 启动时执行的所有过程的列表。
　　存储过程嵌套
　　存储过程可以嵌套即一个存储过程可以调用另一个存储过程。在被调用过程开始执行时嵌套级将增加在被调用过程执行结束后嵌套级将减少。如果超出最大的嵌套级会使整个调用过程链失败。可用 @@NESTLEVEL 函数返回当前的嵌套级。
　　3N]MT0`r7l"{
　　若要估计编译后的存储过程大小请使用下列性能监视计数器。 gl1m.Ub#L7d$N
　　[X%[?IrRV
　　GL0zr%i a
　　* 各种分类的高速缓存对象均可以使用这些计数器包括特殊 sql、准备 sql、过程、触发器等。
　　sql_statement 限制u&hob4E
　　除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外这两个语句必须是批处理中仅有的语句任何 SET 语句均可以在存储过程内部指定。所选择的 SET 选项在存储过程执行过程中有效之后恢复为原来的设置。 1[qyOso7g
　　如果其他用户要使用某个存储过程那么在该存储过程内部一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括
　　k:{z?;f:_jtb
　　ALTER TABLE
　　0Zzg _%bE"C
　　CREATE INDEX
　　CREATE TABLE
　　*Nij@%_$~JU
　　所有 DBCC 语句]?5R{H o&@2CI
　　DROP TABLEXr A I(ZL%R;m
　　%LL:ronxM/j
　　DROP INDEX
　　Cg)m}}%E9el
　　TRUNCATE TABLEs,p){9SHJ7u4\
　　,NX+y%p"V1l
　　UPDATE STATISTICS r&m,|'h [
　　权限
　　CREATE PROCEDURE 的权限默认授予 sysadmin 固定服务器角色成员和 db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者该所有者可以为其它数据库用户设置执行权限。
　　2006-8-5 19:57 天涯风云
　　示例.PWN W M2t;Cr
　　A. 使用带有复杂 SELECT 语句的简单过程
　　下面的存储过程从四个表的联接中返回所有作者提供了姓名、出版的书籍以及出版社。该存储过程不使用任何参数。 B m ]},w'?7T
　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjects [] H#XF#bW/Cln
　　WHERE name = \'au_info_all\' AND type = \'P\')
　　DROP PROCEDURE au_info_all
　　GO
　　CREATE PROCEDURE au_info_all
　　AS&r;jbRBx u}9cD

　　SELECT au_lname, au_fname,>
　　FROM authors a INNER JOIN>
　　ON a.au_id = ta.au_id INNER JOIN>　　ON t.title_id = ta.title_id INNER JOIN publishers p
　　ON t.pub_id = p.pub_id'Y,a4Yq5HK
　　GO#}}n V(r#LS
　　*U1|na/Vo H\
　　au_info_all 存储过程可以通过以下方法执行
　　EXECUTE au_info_all fikmN | a
　　-- Or@Gd|G7Zx
　　EXEC au_info_all(t-tL eN7T5?*{I
　　2e3e|\:e
　　如果该过程是批处理中的第一条语句则可使用:Ml.~ n5fU
　　au_info_alljB E4z2HW ~ av2W7mXkm
　　天涯风云  19:31:29J2NN2B;TL3Y
　　B. 使用带有参数的简单过程
　　下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。eV0S\O;Q ar%]c
　　USE pubs8@4X"A2ldWvA H4guZ
　　IF EXISTS (SELECT name FROM sysobjects A y[:nXHC
　　WHERE name = \'au_info\' AND type = \'P\')q6Z"v0C\ Jt
　　DROP PROCEDURE au_info"Q0rV:U)V\
　　GO
　　USE pubs
　　GO
　　CREATE PROCEDURE au_info ,Z-TKcw&b0@ O
　　@lastname varchar(40), :{9e7^M*RPa
　　@firstname varchar(20)
　　AS

　　SELECT au_lname, au_fname,>
　　FROM authors a INNER JOIN>
　　ON a.au_id = ta.au_id INNER JOIN>　　ON t.title_id = ta.title_id INNER JOIN publishers p
　　ON t.pub_id = p.pub_id
　　WHERE　au_fname = @firstname
　　AND au_lname = @lastname
　　GO
　　mzu a kN{6[\{&`
　　au_info 存储过程可以通过以下方法执行
　　G8?N3N#J7b1YT+|%b
　　EXECUTE au_info \'Dull\', \'Ann\' z?&L3L m t6wz:F
　　-- Ora5q y.w;C8H M9x
　　EXECUTE au_info @lastname = \'Dull\', @firstname = \'Ann\'
　　-- Or
　　EXECUTE au_info @firstname = \'Ann\', @lastname = \'Dull\'
　　-- OrZy'@7C4{"u.j

　　EXEC au_info \'Dull\', \'Ann\'L1|*t @nF>　　-- Or
　　EXEC au_info @lastname = \'Dull\', @firstname = \'Ann\'~;ERQ:R5X4p
　　-- Or|5H0]eO*Fo)r4FMO
　　EXEC au_info @firstname = \'Ann\', @lastname = \'Dull\'
　　:p q;d8q0L(n
　　如果该过程是批处理中的第一条语句则可使用'XF m%e\K+I%a
　　au_info \'Dull\', \'Ann\'
　　-- Or3d)[N/`'w z*\ZF1o,K0Y
　　au_info @lastname = \'Dull\', @firstname = \'Ann\'
　　-- Or,NrpK0} l
　　au_info @firstname = \'Ann\', @lastname = \'Dull\'U$FtC V&v n,Uw$`
　　天涯风云  19:31:41
　　C. 使用带有通配符参数的简单过程
　　下面的存储过程从四个表的联接中只返回指定的作者提供了姓名、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配如果没有提供参数则使用预设的默认值。
　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = \'au_info2\' AND type = \'P\')O1zl#N^l
　　DROP PROCEDURE au_info2JLo{*|J g m
　　GO
　　USE pubs(t-gLcp6n
　　GOboTZ3nI7T
　　CREATE PROCEDURE au_info2| c_^N#zwx#LR)PH)j
　　@lastname varchar(30) = \'D%\',3}|ZLNb5o5t
　　@firstname varchar(18) = \'%\'
　　AS },U'SX'JB(SS

　　SELECT au_lname, au_fname,>
　　FROM authors a INNER JOIN>
　　ON a.au_id = ta.au_id INNER JOIN>　　ON t.title_id = ta.title_id INNER JOIN publishers p7muq-G"US/L,I
　　ON t.pub_id = p.pub_id&R6k/d M|4}k$[
　　WHERE au_fname LIKE @firstname
　　AND au_lname LIKE @lastname
　　GO
　　au_info2 存储过程可以用多种组合执行。下面只列出了部分组合
　　x)]y8KB
　　EXECUTE au_info2
　　-- Or&Wj9pejtMC+M
　　EXECUTE au_info2 \'Wh%\' US oP6j.eA]H(@+]
　　-- Or
　　EXECUTE au_info2 @firstname = \'A%\'
　　-- Or
　　EXECUTE au_info2 \'[CK]ars[OE]n\' v&x!q Pvdb~%Y
　　-- OrV+t"R"o)N4h+y9S)N
　　EXECUTE au_info2 \'Hunter\', \'Sheryl\'2iL:J,~K w5Zs1m'y
　　-- Or
　　EXECUTE au_info2 \'H%\', \'S%\'
　　--------------------------------------------------------------------------------,o \O Le|
　　[2@ Lr([
　　4fmuv A
　　0]c$[E!F`
　　天涯风云  19:32:22 P1rp)m|/`#u~o
　　D. 使用 OUTPUT 参数 so!dRT#Z"|1Q4n
　　OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)并使用一个可选的输入参数和一个输出参数。!M&s!lYa}!a*z8g5\
　　首先创建过程
　　6TVsa,S-]
　　USE pubs
　　GO9n8R){2Z$R5q$~ H%t
　　IF EXISTS(SELECT name FROM sysobjects
　　WHERE name = \'titles_sum\' AND type = \'P\'),{:^_/atA

　　DROP PROCEDURE>　　GO$t7L'AY$xGh&G4b
　　USE pubs
　　GO

　　CREATE PROCEDURE>　　AS

　　SELECT \'Title Name\' =>
　　FROM>
　　WHERE>　　SELECT @@SUM = SUM(price)-{a K\7X-Oo

　　FROM>
　　WHERE>　　GO
　　接下来将该 OUTPUT 参数用于控制流语言。
　　}/oA1cK|tI'B
　　d^:Y;uS%C a:Q
　　? h y3_[1Nz
　　说明　OUTPUT 变量必须在创建表和使用该变量时都进行定义。_+fU*M.b,k
　　参数名和变量名不一定要匹配不过数据类型和参数位置必须匹配除非使用 @@SUM = variable 形式。 A^`x)V0nR.E
　　MCBa4N
　　DECLARE @@TOTALCOST money

　　EXECUTE>　　IF @@TOTALCOST < 200
　　BEGIN'r)b/omr
　　PRINT \' \'

　　PRINT \'All of these>　　END.ov {Z,zc*G"d%[/m
　　ELSEje$iP w?

　　SELECT \'The total cost of these>　　+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
　　9w&?'Ik-p
　　下面是结果集
　　iZ#si/i
　　Title Name　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
　　------------------------------------------------------------------------
　　The Busy Executive\'s Database Guide
　　The Gourmet Microwave
　　The Psychology of Computer Cooking
　　(3 row(s) affected))t8yFH!a5M"Ps
　　A$h5kQyT G
　　Warning, null value eliminated from aggregate.

　　All of these>　　天涯风云  19:32:33ez#}(K/IL~`
　　E. 使用 OUTPUT 游标参数
　　OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。8b5s p2XMMH FX

　　首先创建以下过程在>　　*[7^;q(J1n
　　USE pubs%LC"D Z"{6hTK%P7L
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = \'titles_cursor\' and type = \'P\')b:f7p:O.u UX

　　DROP PROCEDURE>　　GO

　　CREATE PROCEDURE>　　AS)e\y%@0W-OV
　　SET @titles_cursor = CURSORY'iwO V0r*]
　　FORWARD_ONLY STATIC FOR9D#V F a0Uj#Q O
　　SELECT *

　　FROM>　　}O1Io g0A'Y%{ y/~
　　OPEN @titles_cursor
　　GO
　　1DUc"G:A B
　　接下来执行一个批处理声明一个局部游标变量执行上述过程以将游标赋值给局部变量然后从该游标提取行。
　　USE pubs4Z{!~x Z N9g |L
　　GO5xM:SJTV%Y
　　DECLARE @MyCursor CURSORCsQa y9]0a$j

　　EXEC>　　WHILE (@@FETCH_STATUS = 0)
　　BEGIN
　　FETCH NEXT FROM @MyCursor;V{:@,U^7`.^ u&O
　　END
　　CLOSE @MyCursor9Ac*O?F^0Z wGX
　　DEALLOCATE @MyCursor0z z8N[5PhT N\
　　GO
　　F6E#k0hC9bd;f
　　天涯风云  19:32:43)Zw\V/l$i
　　F. 使用 WITH RECOMPILE 选项Mf`1t2p.hGcO;e
　　如果为过程提供的参数不是典型的参数并且新的执行计划不应高速缓存或存储在内存中WITH RECOMPILE 子句会很有帮助。Q[[c&C1Y-K~k
　　?7AdSl2s
　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjectsp U6r%m)B\
　　WHERE name = \'titles_by_author\' AND type = \'P\')

　　DROP PROCEDURE>　　GO|n/n W%m:Ja

　　CREATE PROCEDURE>　　WITH RECOMPILEW2P h)K_2fFa9a
　　AS
　　SELECT RTRIM(au_fname) + \' \' + RTRIM(au_lname) AS \'Authors full name\',

　　>
　　FROM authors a INNER JOIN>
　　ON a.au_id = ta.au_id INNER JOIN>　　ON ta.title_id = t.title_id
　　WHERE au_lname LIKE @@LNAME_PATTERN
　　GO {/L-N$o5R ?
　　A-H1f:LCSU
　　天涯风云  19:32:59
　　G. 使用 WITH ENCRYPTION 选项
　　WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程使用 sp_helptext 系统存储过程获取关于加密过程的信息然后尝试直接从 syscomments 表中获取关于该过程的信息。
　　IF EXISTS (SELECT name FROM sysobjectsTZ9`i/td O#J
　　WHERE name = \'encrypt_this\' AND type = \'P\') X{}Zs
　　DROP PROCEDURE encrypt_this
　　GO
　　USE pubs k$[8y NU)J4P
　　GO.vJ-kT.Z
　　CREATE PROCEDURE encrypt_thisn9WfR"M0\S
　　WITH ENCRYPTIONd?9zHC:nC
　　AS
　　SELECT *
　　FROM authors
　　GO
　　EXEC sp_helptext encrypt_this[`Y4Pj/|8d
　　xF+l;q3B
　　下面是结果集
　　The object\'s comments have been encrypted.HehN H
　　o$DG%qL3pMie6b@;U
　　接下来选择加密存储过程内容的标识号和文本。t:H7N+i7Q
　　SELECT c.id, c.text
　　FROM syscomments c INNER JOIN sysobjects oZK8C'q F1ko.}0C*G%Xx
　　ON c.id = o.id
　　WHERE o.name = \'encrypt_this\'
　　QC$v qe"IlV0r8C
　　下面是结果集 \J!S G-w @ksN

　　说明　text 列的输出显示在单独一行中。执行时该信息将与>　　2Z](`[1@(g
　　id　　　　 text　　　　　　　　　　　　　　　　　　　　　　　　　　　　
　　---------- ------------------------------------------------------------
　　1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????.KH(pJ,}"G`
　　!d Q%xvx*_C
　　(1 row(s) affected)
　　K I xoyG;K3uC$K
　　天涯风云  19:33:105F L-pb9?
　　H. 创建用户定义的系统存储过程
　　下面的示例创建一个过程显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数该过程将返回表名以 sys 开头的所有表及索引。I'A8bO9]6kc#q
　　K+e cdV$E1FN-b+D
　　IF EXISTS (SELECT name FROM sysobjects.B*HX"a ~ pV
　　WHERE name = \'sp_showindexes\' AND type = \'P\')oW\i{^7\6V
　　DROP PROCEDURE sp_showindexes+R(pe4m`
　　GO
　　USE master0_D&F ~k8^x,kd
　　GO
　　CREATE PROCEDURE sp_showindexes3O'v`5MTD:H
　　@@TABLE varchar(30) = \'sys%\'
　　AS
　　SELECT o.name AS TABLE_NAME,U&@s U0T;r*]
　　i.name AS INDEX_NAME,
　　indid AS INDEX_ID
　　FROM sysindexes i INNER JOIN sysobjects oCG6q Bc
　　ON o.id = i.id
　　WHERE o.name LIKE @@TABLE
　　GO　　　　 5RfD#kmF Bi^
　　USE pubs
　　EXEC sp_showindexes \'emp%\'
　　GO~u|)bl#Z
　　n On|0AjL]
　　下面是结果集
　　AFG!C!_:aY
　　TABLE_NAME　　　 INDEX_NAME　　　 INDEX_ID
　　---------------- ---------------- ----------------
　　employee　　　　 employee_ind　　 1
　　employee　　　　 PK_emp_id　　　　2P'p6jC G6B
　　R6["bZl
　　(2 row(s) affected)[cI#r c5Y
　　%g8y_*z9Mm(N5G#[Mx
　　天涯风云  19:33:24
　　I. 使用延迟名称解析5eIz9`8q
　　下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在但每个存储过程都可创建。
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = \'proc1\' AND type = \'P\')(Y~ X3P7vnZ
　　DROP PROCEDURE proc1
　　GO
　　-- Creating a procedure on a nonexistent table.LYaQwoH
　　USE pubsJ[M:X,wpY:@a
　　GO
　　CREATE PROCEDURE proc1
　　AS
　　SELECT *YZj-~0Aw/Sy't0uB
　　FROM does_not_exist)]3Ed#\)j*a5M J
　　GO　
　　-- Here is the statement to actually see the text of the procedure.
　　SELECT o.id, c.text jet1y5Jz)IJC
　　FROM sysobjects o INNER JOIN syscomments c 7M`3R/|4gH
　　ON o.id = c.idq!^of#Q2e8q D
　　WHERE o.type = \'P\' AND o.name = \'proc1\'9rw X$OJFm,aQ{
　　GO
　　USE masterA)M X0f!dMq,u)~
　　GO
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = \'proc2\' AND type = \'P\')
　　DROP PROCEDURE proc2
　　GO
　　-- Creating a procedure that attempts to retrieve information from a
　　-- nonexistent column in an existing table.
　　USE pubs#OUa6}4dA
　　GOM1r i `1r8Qd
　　CREATE PROCEDURE proc2{ k)]I3f/@
　　AS#}tK"r'L7F5wT ~(OB C
　　DECLARE @middle_init char(1)3{%k:@u'Ui}
　　SET @middle_init = NULL
　　SELECT au_id, middle_initial = @middle_init)@:n!b6c!d8zd
　　FROM authorsOgu%Kx9O @&Y)|
　　GO　1F-U4A1IR0U.o
　　-- Here is the statement to actually see the text of the procedure.B/W]kp3b
　　SELECT o.id, c.text
　　FROM sysobjects o INNER JOIN syscomments c
　　ON o.id = c.id
　　WHERE o.type = \'P\' and o.name = \'proc2\'
　　[ 本帖最后由 天涯风云 于 2006-8-9 10:08 编辑 ]
　　2006-8-5 19:59 天涯风云
　　3.sql存储过程及应用
　　一、简介1aEF`Z+lcf
　　3J tsId T { Rn$x:y
　　存储过程Stored Procedure 是一组为了完成特定功能的SQL 语句集经编译后
　　存储在数据库中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行T!c{4Q*W3|
　　它 F-M#}*zi B
　　在SQL Server 的系列版本中存储过程分为两类系统提供的存储过程和用户自定义存储过程
　　。(]Q4D}!Sj
　　系统SP主要存储master 数据库中并以sp_为前缀并且系统存储过程主要是从系统表中获取j J$ec-}}3|v
　　信息从而为系统管理员管理SQL Server。 用户自定义存储过程是由用户创建并能完成'}4|u"m3c{xc-U~
　　某一特定功能如查询用户所需数据信息的存储过程。F2RQ H d DAo
　　存储过程具有以下优点
　　1.存储过程允许标准组件式编程(模块化设计) ALz1h4l;b
　　存储过程在被创建以后可以在程序中被多次调用而不必重新编写该存储过程的SQL语句而
　　且数
　　据库专业人员可随时对存储过程进行修改但对应用程序源代码毫无影响。因为应用程序源代!s {@F `r)oL
　　码只包含存Hs7D?m(eO,y
　　储过程的调用语句从而极大地提高了程序的可移植性。
　　e{"?h8E?)vW~
　　2.存储过程能够实现快速的执行速度
　　如果某一操作包含大量的Transaction-SQL 代码,或分别被多次执行那么存储过程要比批处理
　　的
　　执行速度快很多因为存储过程是预编译的在首次运行一个存储过程时查询优化器对其进
　　K'eps U-_z7Ix
　　行分析优 oZjea
　　化并给出最终被存在系统表中的执行计划而批处理的Transaction-SQL 语句在每次运行时
　　都要进行
　　编译和优化因此速度相对要慢一些。'L n"ww5q
　　3.存储过程能够减少网络流量
　　对于同一个针对数据数据库对象的操作如查询修改如果这一操作所涉及到的Transaction-SQL e A;C%Gx k&I
　　语句被组织成一存储过程那么当在客户计算机上调用该存储过程时网络中传送的只是该调
　　aMP3L udp/{j
　　用语句否
　　则将是多条SQL 语句从而大大增加了网络流量降低网络负载。 VhPa-_3O9M/O
　　4.存储过程可被作为一种安全机制来充分利用
　　系统管理员通过对执行某一存储过程的权限进行限制从而能够实现对相应的数据访问权限的8NV}lG
　　限
　　制。
　　6pX(Id|O8Q6M`
　　二、变量R-I^Aw:~(`
　　@IyQP5B L{]|.PA5iJ
　　'D7FO9j r
　　三、流程控制语句(if else | select case | while )
　　Select ... CASE 实例WY_7S X F%X X r
　　DECLARE @iRet INT, @PKDisp VARCHAR(20)
　　SET @iRet = '1'#m-x_%`3?(jtO2N
　　Select @iRet = "J[{vC6C
　　CASE2{ \y]b,R.I#hB
　　WHEN @PKDisp = '一' THEN 1
　　WHEN @PKDisp = '二' THEN 2
　　WHEN @PKDisp = '三' THEN 3Upm5[T
　　WHEN @PKDisp = '四' THEN 4V`bJ*KQ
　　WHEN @PKDisp = '五' THEN 5vKgx&vBQ i@\8E
　　ELSE 100
　　ENDq As9K?3l'k3l0w
　　四、存储过程格式;g;[3V4o3p2s
　　创建存储过程
　　Create Proc dbo.存储过程名
　　存储过程参数
　　AS A;N"WC!@e"i6R
　　执行语句
　　RETURN
　　执行存储过程
　　GOU?:DTv9YWs/r/W
　　*********************************************************/3|@TJ*P^/C
　　-- 变量的声明,sql里面声明变量时必须在变量前加@符号VC6Ut7H
　　DECLARE @I INT y@W h y2c T4T-k
　　h:aCS0Sm?-T4z
　　-- 变量的赋值变量赋值时变量前必须加setr$[`iT K*o
　　SET @I = 30Be ^!U(U$p
　　on6f4gwg9Lg'j
　　-- 声明多个变量
　　DECLARE @s varchar(10),@a INT
　　'lT~x#m$bq
　　-- Sql 里if语句
　　IF 条件 BEGINb_:Q?IAo }
　　执行语句
　　END
　　ELSE BEGINw2@z^(Q }4a%d y,Ls
　　执行语句W!r L$lUR{
　　ENDFo2_9\*Ig)Yz ZLO
　　:x)?1|Y$N:^%}
　　DECLARE @d INT
　　set @d = 1`0@'L"IbY ~9R'h
　　IF @d = 1 BEGIN
　　_;f)u`u4r
　　-- 打印
　　PRINT '正确'v3h9Iw~
　　END
　　ELSE BEGIN$?kmWiM
　　PRINT '错误'
　　END
　　xA4CoW!^/D
　　-- Sql 里的多条件选择语句.%kU"w'b}/W?m"d
　　DECLARE @iRet INT, @PKDisp VARCHAR(20)
　　SET @iRet = 1
　　Select @iRet =
　　CASEvH6|s$osFz A&MJe
　　WHEN @PKDisp = '一' THEN 1
　　WHEN @PKDisp = '二' THEN 2/N2zT&K"]2@8W
　　WHEN @PKDisp = '三' THEN 3
　　WHEN @PKDisp = '四' THEN 47O/yD7O ]0grKC*q
　　WHEN @PKDisp = '五' THEN 5+a$D.]%Lk A
　　ELSE 1009F7K7Ue;Fa D
　　END
　　Dr r$@:I.W
　　-- 循环语句
　　WHILE 条件 BEGIN
　　执行语句
　　END7t;^pml
　　DECLARE @i INT2PI1a S!lY,n
　　SET @i = 1
　　WHILE @i 0 then  qW^\p)Ld8Z[e
　　page = 1 g0w jBg7DZ2s
　　end if
　　pagesize=20’每页的条数 /N+Qei2s a
　　set cmd = server.CreateObject("adodb.command") [:PHj-U#f6G
　　cmd.ActiveConnection = conn W)n(ML7L*Q4f#z
　　cmd.CommandType = 4 w'qt,o4Rjbe_
　　cmd.CommandText = "p_SplitPage" WU Gc] JVd
　　cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql) 7b,`|x q0cLtGW+q
　　cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page)
　　cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, pageSize)
　　cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount) oJ/|ht
　　cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount)
　　set rs = cmd.Execute p&N_3{cuK!nJ7G
　　set rs = rs.NextRecordSet 1l!_xpf"A
　　pageCount = cmd.Parameters("@pageCount").value
　　recordCount = cmd.Parameters("@recordCount").value `f9_*j-kC+X0zY
　　if pageCount = 0 then pageCount = 1 *FVu]N&K|
　　if page>pageCount then  X3iN)eu b PH
　　response.Redirect("?page="&pageCount)
　　end if
　　set rs = cmd.Execute
　　我们如此就可以实现对数据的读取并可以进行有效的分页,但是我们往往会发现一个问题,如果我们构造的SQL语句如果使用的是select * from tab ...的话就经常出现无法读取数据的错误,或者是读取出来,但是有的数据无法显示的错误经过仔细的检查发现,如果是排列在SQL语句的前列的数据可以被读取,而如果不按照SQL读取出来的字段进行顺序读取,就会出现数据丢失的情况,所以唯一的途径就是进行顺序读取.如:

　　对于select>
　　>　　newsTitle = rs("newsTitle") Ee.e;b#tu
　　... ;de&H1dkF
　　分析出现这个的原因是:SQL数据库在构造虚拟表的时候就是以一种先进先出的原则,把所有的数据排列在一个内存段之中,通过顺序的读取,将数据逐一的读取,而如果跳过某个具体的字段获取下一个字段的信息,系统就会将原来的那个字段的信息丢失,以释放内存,这是出于系统构造的简单性和系统的内存最低化的要求,所以这样也保证了有限的内存资源得到最充分的发挥,这也是为什么存储过程比普通的SQL要快的原因。
　　原文网址链接http://www.cnblogs.com/wangjiayue/archive/2013/07/25/3214171.html