我的大学

sql存储过程基础语法这是一个储存过程的基础的创建和简单应用，希望对大家有点帮助，不足之处肯定会有，算是抛砖引玉吧。
CREATE PROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。
可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。
也可以创建在 Microsoft® SQL Server™ 启动时自动运行的存储过程。
语法CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]
[ WITH { RECOMPILE ENCRYPTION RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
参数procedure_name
新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。
要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。
;number
是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。
@parameter
过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。
使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。
data_type
参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。
说明对于可以是 cursor 数据类型的输出参数，没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
default
参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。
OUTPUT
表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。
n
表示最多可以指定 2.100 个参数的占位符。
{RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION}
RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。
ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。
说明在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。
FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。
AS
指定过程要执行的操作。
sql_statement
过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。
n
是表示此过程可以包含多条 Transact-SQL 语句的占位符。
注释存储过程的最大大小为 128 MB。
用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。在单个批处理中，CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合使用。
默认情况下，参数可为空。如果传递 NULL 参数值并且该参数在 CREATE 或 ALTER TABLE 语句中使用，而该语句中引用的列又不允许使用 NULL，则 SQL Server 会产生一条错误信息。为了防止向不允许使用 NULL 的列传递 NULL 参数值，应向过程中添加编程逻辑或为该列使用默认值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 关键字)。
建议在存储过程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL，例如在创建临时表时。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项控制 SQL Server 为列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话）。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同，则为第二个连接创建的表列可能会有不同的为空性，并且表现出不同的行为方式。如果为每个列显式声明了 NULL 或 NOT NULL，那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。
在创建或更改存储过程时，SQL Server 将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行存储过程时，将使用这些原始设置。因此，所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被忽略。在存储过程中出现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的功能。
其它 SET 选项（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在创建或更改存储过程时不保存。如果存储过程的逻辑取决于特定的设置，应在过程开头添加一条 SET 语句，以确保设置正确。从存储过程中执行 SET 语句时，该设置只在存储过程完成之前有效。之后，设置将恢复为调用存储过程时的值。这使个别的客户端可以设置所需的选项，而不会影响存储过程的逻辑。
说明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串，由兼容级别设置控制。如果兼容级别小于或等于 65，SQL Server 就将空字符串解释为单个空格。如果兼容级别等于 70，则 SQL Server 将空字符串解释为空字符串。有关更多信息，请参见 sp_dbcmptlevel。
获得有关存储过程的信息若要显示用来创建过程的文本，请在过程所在的数据库中执行 sp_helptext，并使用过程名作为参数。
说明使用 ENCRYPTION 选项创建的存储过程不能使用 sp_helptext 查看。
若要显示有关过程引用的对象的报表，请使用 sp_depends。
若要为过程重命名，请使用 sp_rename。
引用对象SQL Server 允许创建的存储过程引用尚不存在的对象。在创建时，只进行语法检查。执行时，如果高速缓存中尚无有效的计划，则编译存储过程以生成执行计划。只有在编译过程中才解析存储过程中引用的所有对象。因此，如果语法正确的存储过程引用了不存在的对象，则仍可以成功创建，但在运行时将失败，因为所引用的对象不存在。有关更多信息，请参见延迟名称解析和编译。
延迟名称解析和兼容级别SQL Server 允许 Transact-SQL 存储过程在创建时引用不存在的表。这种能力称为延迟名称解析。不过，如果 Transact-SQL 存储过程引用了该存储过程中定义的表，而兼容级别设置（通过执行 sp_dbcmptlevel 来设置）为 65，则在创建时会发出警告信息。而如果在运行时所引用的表不存在，将返回错误信息。有关更多信息，请参见 sp_dbcmptlevel 和延迟名称解析和编译。
执行存储过程成功执行 CREATE PROCEDURE 语句后，过程名称将存储在 sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中。第一次执行时，将编译该过程以确定检索数据的最佳访问计划。
使用 cursor 数据类型的参数存储过程只能将 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 批处理、存储过程或触发器调用这些过程。
Cursor 输出参数在执行过程时，以下规则适用于 cursor 输出参数：
对于只进游标，游标的结果集中返回的行只是那些存储过程执行结束时处于或超出游标位置的行，例如：在过程中的名为 RS 的 100 行结果集上打开一个非滚动游标。
过程提取结果集 RS 的头 5 行。
过程返回到其调用者。
返回到调用者的结果集 RS 由 RS 的第 6 到 100 行组成，调用者中的游标处于 RS 的第一行之前。对于只进游标，如果存储过程完成后，游标位于第一行的前面，则整个结果集将返回给调用批处理、存储过程或触发器。返回时，游标将位于第一行的前面。
对于只进游标，如果存储过程完成后，游标的位置超出最后一行的结尾，则为调用批处理、存储过程或触发器返回空结果集。
说明空结果集与空值不同。
对于可滚动游标，在存储过程执行结束时，结果集中的所有行均会返回给调用批处理、存储过程或触发器。返回时，游标保留在过程中最后一次执行提取时的位置。
对于任意类型的游标，如果游标关闭，则将空值传递回调用批处理、存储过程或触发器。如果将游标指派给一个参数，但该游标从未打开过，也会出现这种情况。
说明关闭状态只有在返回时才有影响。例如，可以在过程中关闭游标，稍后再打开游标，然后将该游标的结果集返回给调用批处理、存储过程或触发器。
临时存储过程SQL Server 支持两种临时过程：局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。
临时过程用 # 和 ## 命名，可以由任何用户创建。创建过程后，局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。如果创建了全局临时过程，则所有用户均可以访问该过程，权限不能显式废除。只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户，才可以在该数据库中显式创建临时过程（不使用编号符命名）。可以授予或废除这些过程中的权限。
说明频繁使用临时存储过程会在 tempdb 中的系统表上产生争用，从而对性能产生负面影响。建议使用 sp_executesql 代替。sp_executesql 不在系统表中存储数据，因此可以避免这一问题。
自动执行存储过程SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员创建，并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。
对启动过程的数目没有限制，但是要注意，每个启动过程在执行时都会占用一个连接。如果必须在启动时执行多个过程，但不需要并行执行，则可以指定一个过程作为启动过程，让该过程调用其它过程。这样就只占用一个连接。
在启动时恢复了最后一个数据库后，即开始执行存储过程。若要跳过这些存储过程的执行，请将启动参数指定为跟踪标记 4022。如果以最低配置启动 SQL Server（使用 -f 标记），则启动存储过程也不会执行。有关更多信息，请参见跟踪标记。
若要创建启动存储过程，必须作为 sysadmin 固定服务器角色的成员登录，并在 master 数据库中创建存储过程。
使用 sp_procoption 可以：
将现有存储过程指定为启动过程。
停止在 SQL Server 启动时执行过程。
查看 SQL Server 启动时执行的所有过程的列表。存储过程嵌套存储过程可以嵌套，即一个存储过程可以调用另一个存储过程。在被调用过程开始执行时，嵌套级将增加，在被调用过程执行结束后，嵌套级将减少。如果超出最大的嵌套级，会使整个调用过程链失败。可用 @@NESTLEVEL 函数返回当前的嵌套级。
若要估计编译后的存储过程大小，请使用下列性能监视计数器。
性能监视器对象名性能监视计数器名称 SQLServer：缓冲区管理器高速缓存大小（页面数） SQLServer：高速缓存管理器高速缓存命中率高速缓存页高速缓存对象计数*
* 各种分类的高速缓存对象均可以使用这些计数器，包括特殊 sql、准备 sql、过程、触发器等。
有关更多信息，请参见 SQL Server:Buffer Manager 对象和 SQL Server:Cache Manager 对象。
sql_statement 限制除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（这两个语句必须是批处理中仅有的语句），任何 SET 语句均可以在存储过程内部指定。所选择的 SET 选项在存储过程执行过程中有效，之后恢复为原来的设置。
如果其他用户要使用某个存储过程，那么在该存储过程内部，一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括：
ALTER TABLE
CREATE INDEX
CREATE TABLE
所有 DBCC 语句
DROP TABLE
DROP INDEX
TRUNCATE TABLE
UPDATE STATISTICS 权限CREATE PROCEDURE 的权限默认授予 sysadmin 固定服务器角色成员和 db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。执行存储过程的权限授予过程的所有者，该所有者可以为其它数据库用户设置执行权限。
示例A. 使用带有复杂 SELECT 语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info_all' AND type = 'P') DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_idGO
au_info_all 存储过程可以通过以下方法执行：
EXECUTE au_info_all-- OrEXEC au_info_all
如果该过程是批处理中的第一条语句，则可使用：
au_info_all
B. 使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info' AND type = 'P') DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t 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 = @lastnameGO
au_info 存储过程可以通过以下方法执行：
EXECUTE au_info 'Dull', 'Ann'-- OrEXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'-- OrEXEC au_info 'Dull', 'Ann'-- OrEXEC au_info @lastname = 'Dull', @firstname = 'Ann'-- OrEXEC au_info @firstname = 'Ann', @lastname = 'Dull'
如果该过程是批处理中的第一条语句，则可使用：
au_info 'Dull', 'Ann'-- Orau_info @lastname = 'Dull', @firstname = 'Ann'-- Orau_info @firstname = 'Ann', @lastname = 'Dull'
C. 使用带有通配符参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%'AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_idWHERE au_fname LIKE @firstname AND au_lname LIKE @lastnameGO
au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：
EXECUTE au_info2-- OrEXECUTE au_info2 'Wh%'-- OrEXECUTE au_info2 @firstname = 'A%'-- OrEXECUTE au_info2 '[CK]ars[OE]n'-- OrEXECUTE au_info2 'Hunter', 'Sheryl'-- OrEXECUTE au_info2 'H%', 'S%'
D. 使用 OUTPUT 参数OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。
首先，创建过程：
USE pubsGOIF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND type = 'P') DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUTASSELECT 'Title Name' = titleFROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price)FROM titlesWHERE title LIKE @@TITLEGO
接下来，将该 OUTPUT 参数用于控制流语言。
说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。
参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。
DECLARE @@TOTALCOST moneyEXECUTE titles_sum 'The%', @@TOTALCOST OUTPUTIF @@TOTALCOST < 200 BEGIN PRINT ' ' PRINT 'All of these titles can be purchased for less than $200.'ENDELSE SELECT 'The total cost of these titles is $' + RTRIM(CAST(@@TOTALCOST AS varchar(20)))
下面是结果集：
Title Name ------------------------------------------------------------------------ The Busy Executive's Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
E. 使用 OUTPUT 游标参数OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。
首先，创建以下过程，在 titles 表上声明并打开一个游标：
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P')DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUTASSET @titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM titles
OPEN @titles_cursorGO
接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。
USE pubsGODECLARE @MyCursor CURSOREXEC titles_cursor @titles_cursor = @MyCursor OUTPUTWHILE (@@FETCH_STATUS = 0)BEGIN FETCH NEXT FROM @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursorGO
F. 使用 WITH RECOMPILE 选项如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITH RECOMPILE 子句会很有帮助。
USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_authorGOCREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'WITH RECOMPILEASSELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS TitleFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_idWHERE au_lname LIKE @@LNAME_PATTERNGO
G. 使用 WITH ENCRYPTION 选项WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'encrypt_this' AND type = 'P') DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTIONASSELECT * FROM authorsGO
EXEC sp_helptext encrypt_this
下面是结果集：
The object's comments have been encrypted.
接下来，选择加密存储过程内容的标识号和文本。
SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o ON c.id = o.idWHERE o.name = 'encrypt_this'
下面是结果集：
说明 text 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在同一行中。
id text ---------- ------------------------------------------------------------
(1 row(s) affected)
H. 创建用户定义的系统存储过程下面的示例创建一个过程，显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 sys 开头的所有表（及索引）。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_showindexes' AND type = 'P') DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes @@TABLE varchar(30) = 'sys%'AS SELECT o.name AS TABLE_NAME, i.name AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes i INNER JOIN sysobjects o ON o.id = i.id WHERE o.name LIKE @@TABLEGO USE pubsEXEC sp_showindexes 'emp%'GO
下面是结果集：
TABLE_NAME INDEX_NAME INDEX_ID ---------------- ---------------- ----------------employee employee_ind 1employee PK_emp_id 2
(2 row(s) affected)
I. 使用延迟名称解析下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在，但每个存储过程都可创建。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc1' AND type = 'P') DROP PROCEDURE proc1GO-- Creating a procedure on a nonexistent table.USE pubsGOCREATE PROCEDURE proc1AS SELECT * FROM does_not_existGO -- Here is the statement to actually see the text of the procedure.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = 'P' AND o.name = 'proc1'GOUSE masterGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'proc2' AND type = 'P') DROP PROCEDURE proc2GO-- Creating a procedure that attempts to retrieve information from a-- nonexistent column in an existing table.USE pubsGOCREATE PROCEDURE proc2AS DECLARE @middle_init char(1) SET @middle_init = NULL SELECT au_id, middle_initial = @middle_init FROM authorsGO -- Here is the statement to actually see the text of the procedure.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = 'P' and o.name = 'proc2'
=======================================================================================================================结束===========================================================================================================================
还有另外一个参考的储存过程例子
有关ASP与存储过程的文章不少，这些资料旨在提供一点帮助，仅限于此，现在我基本上通过调用存储过程访问SQL Server，以下的文字虽不敢保证绝对正确，但都是实践的总结，希望对大家能有帮助。存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。这样做的好处至少有三个：第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。第三、有利于SQL语句的重用。
在ASP中，一般通过command对象调用存储过程，根据不同情况，本文也介绍其它调用方法。为了方便说明，根据存储过程的输入输出，作以下简单分类：1. 只返回单一记录集的存储过程假设有以下存储过程(本文的目的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)： /*SP1*/CREATE PROCEDURE dbo.getUserListasset nocount onbeginselect * from dbo.[userinfo]endgo 以上存储过程取得userinfo表中的所有记录，返回一个记录集。通过command对象调用该存储过程的ASP代码如下:
'**通过Command对象调用存储过程**DIM MyComm,MyRstSet MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串MyComm.CommandText = "getUserList" '指定存储过程名MyComm.CommandType = 4 '表明这是一个存储过程MyComm.Prepared = true '要求将SQL命令先行编译Set MyRst = MyComm.ExecuteSet MyComm = Nothing 存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。在以上代码中，CommandType属性表明请求的类型，取值及说明如下：-1 表明CommandText参数的类型无法确定1 表明CommandText是一般的命令类型2 表明CommandText参数是一个存在的表名称4 表明CommandText参数是一个存储过程的名称
还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下： '**通过Connection对象调用存储过程**DIM MyConn,MyRstSet MyConn = Server.CreateObject("ADODB.Connection")MyConn.open MyConStr 'MyConStr是数据库连接字串Set MyRst = MyConn.Execute("getUserList",0,4) '最后一个参断含义同CommandTypeSet MyConn = Nothing '**通过Recordset对象调用存储过程**DIM MyRstSet MyRst = Server.CreateObject("ADODB.Recordset")MyRst.open "getUserList",MyConStr,0,1,4'MyConStr是数据库连接字串,最后一个参断含义与CommandType相同 2. 没有输入输出的存储过程请看以下存储过程： /*SP2*/CREATE PROCEDURE dbo.delUserAllasset nocount onbegindelete from dbo.[userinfo]endgo 该存储过程删去userinfo表中的所有记录，没有任何输入及输出，调用方法与上面讲过的基本相同，只是不用取得记录集： '**通过Command对象调用存储过程**DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串MyComm.CommandText = "delUserAll" '指定存储过程名MyComm.CommandType = 4 '表明这是一个存储过程MyComm.Prepared = true '要求将SQL命令先行编译MyComm.Execute '此处不必再取得记录集Set MyComm = Nothing 当然也可通过Connection对象或Recordset对象调用此类存储过程，不过建立Recordset对象是为了取得记录集，在没有返回记录集的情况下，还是利用Command对象吧。3. 有返回值的存储过程在进行类似SP2的操作时，应充分利用SQL Server强大的事务处理功能，以维护数据的一致性。并且，我们可能需要存储过程返回执行情况，为此，将SP2修改如下： /*SP3*/CREATE PROCEDURE dbo.delUserAllasset nocount onbeginBEGIN TRANSACTIONdelete from dbo.[userinfo]IF @@error=0 beginCOMMIT TRANSACTIONreturn 1endELSEbeginROLLBACK TRANSACTIONreturn 0end returnendgo 以上存储过程，在delete顺利执行时，返回1，否则返回0，并进行回滚操作。为了在ASP中取得返回值，需要利用Parameters集合来声明参数： '**调用带有返回值的存储过程并取得返回值**DIM MyComm,MyParaSet MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串MyComm.CommandText = "delUserAll" '指定存储过程名MyComm.CommandType = 4 '表明这是一个存储过程MyComm.Prepared = true '要求将SQL命令先行编译'声明返回值Set Mypara = MyComm.CreateParameter("RETURN",2,4)MyComm.Parameters.Append MyParaMyComm.Execute'取得返回值DIM retValueretValue = MyComm(0) '或retValue = MyComm.Parameters(0)Set MyComm = Nothing
在MyComm.CreateParameter("RETURN",2,4)中，各参数的含义如下：第一个参数("RETURE")为参数名。参数名可以任意设定，但一般应与存储过程中声明的参数名相同。此处是返回值，我习惯上设为"RETURE"；第二个参数(2)，表明该参数的数据类型，具体的类型代码请参阅ADO参考，以下给出常用的类型代码：adBigInt: 20 ;adBinary : 128 ; adBoolean: 11 ;adChar: 129 ;adDBTimeStamp: 135 ;adEmpty: 0 ;adInteger: 3 ;adSmallInt: 2 ; adTinyInt: 16 ;adVarChar: 200 ;对于返回值，只能取整形，且-1到-99为保留值；第三个参数(4)，表明参数的性质，此处4表明这是一个返回值。此参数取值的说明如下：0 : 类型无法确定； 1: 输入参数；2: 输入参数；3：输入或输出参数；4: 返回值
以上给出的ASP代码，应该说是完整的代码，也即最复杂的代码，其实 Set Mypara = MyComm.CreateParameter("RETURN",2,4)MyComm.Parameters.Append MyPara
可以简化为 MyComm.Parameters.Append MyComm.CreateParameter("RETURN",2,4) 甚至还可以继续简化，稍后会做说明。对于带参数的存储过程，只能使用Command对象调用(也有资料说可通过Connection对象或Recordset对象调用，但我没有试成过)。4. 有输入参数和输出参数的存储过程返回值其实是一种特殊的输出参数。在大多数情况下，我们用到的是同时有输入及输出参数的存储过程，比如我们想取得用户信息表中，某ID用户的用户名，这时候，有一个输入参数----用户ID，和一个输出参数----用户名。实现这一功能的存储过程如下： /*SP4*/CREATE PROCEDURE dbo.getUserName@UserID int,@UserName varchar(40) outputasset nocount onbeginif @UserID is null returnselect @UserName=username from dbo.[userinfo] where userid=@UserIDreturnendgo 调用该存储过程的ASP代码如下： '**调用带有输入输出参数的存储过程**DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.Command")MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串MyComm.CommandText = "getUserName" '指定存储过程名MyComm.CommandType = 4 '表明这是一个存储过程MyComm.Prepared = true '要求将SQL命令先行编译'声明参数MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)MyComm.Parameters.append MyComm.CreateParameter("@UserName",200,2,40)MyComm.Execute'取得出参UserName = MyComm(1)Set MyComm = Nothing 在以上代码中，可以看到，与声明返回值不同，声明输入参数时需要5个参数，声明输出参数时需要4个参数。声明输入参数时5个参数分别为：参数名、参数数据类型、参数类型、数据长度、参数值。声明输出参数时，没有最后一个参数：参数值。需要特别注意的是：在声明参数时，顺序一定要与存储过程中定义的顺序相同，而且各参数的数据类型、长度也要与存储过程中定义的相同。如果存储过程有多个参数，ASP代码会显得繁琐，可以使用with命令简化代码： '**调用带有输入输出参数的存储过程(简化代码)**DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.Command")with MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串.CommandText = "getUserName" '指定存储过程名.CommandType = 4 '表明这是一个存储过程.Prepared = true '要求将SQL命令先行编译.Parameters.append .CreateParameter("@UserID",3,1,4,UserID).Parameters.append .CreateParameter("@UserName",200,2,40).Executeend withUserName = MyComm(1)Set MyComm = Nothing
假如我们要取得ID为1到10，10位用户的用户名，是不是要创建10次Command对象呢？不是的。如果需要多次调用同一存储过程，只需改变输入参数，就会得到不同的输出： '**多次调用同一存储过程**DIM MyComm,UserID,UserNameUserName = ""Set MyComm = Server.CreateObject("ADODB.Command")for UserID = 1 to 10with MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串.CommandText = "getUserName" '指定存储过程名.CommandType = 4 '表明这是一个存储过程.Prepared = true '要求将SQL命令先行编译if UserID = 1 then.Parameters.append .CreateParameter("@UserID",3,1,4,UserID).Parameters.append .CreateParameter("@UserName",200,2,40).Executeelse'重新给入参赋值(此时参数值不发生变化的入参以及出参不必重新声明).Parameters("@UserID") = UserID.Executeend ifend withUserName = UserName + MyComm(1) + "," '也许你喜欢用数组存储nextSet MyComm = Nothing 通过以上代码可以看出：重复调用同一存储过程时，只需为值发生改变的输入参数重新赋值即可，这一方法在有多个输入输出参数，且每次调用时只有一个输入参数的值发生变化时，可以大大减少代码量。5. 同时具有返回值、输入参数、输出参数的存储过程前面说过，在调用存储过程时，声明参数的顺序要与存储过程中定义的顺序相同。还有一点要特别注意：如果存储过程同时具有返回值以及输入、输出参数，返回值要最先声明。为了演示这种情况下的调用方法，我们改善一下上面的例子。还是取得ID为1的用户的用户名，但是有可能该用户不存在(该用户已删除，而userid是自增长的字段)。存储过程根据用户存在与否，返回不同的值。此时，存储过程和ASP代码如下： /*SP5*/CREATE PROCEDURE dbo.getUserName--为了加深对"顺序"的印象，将以下两参数的定义顺序颠倒一下@UserName varchar(40) output,@UserID intasset nocount onbeginif @UserID is null returnselect @UserName=username from dbo.[userinfo] where userid=@UserIDif @@rowcount>0return 1elsereturn 0returnendgo '**调用同时具有返回值、输入参数、输出参数的存储过程**DIM MyComm,UserID,UserNameUserID = 1Set MyComm = Server.CreateObject("ADODB.Command")with MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串.CommandText = "getUserName" '指定存储过程名.CommandType = 4 '表明这是一个存储过程.Prepared = true '要求将SQL命令先行编译'返回值要最先被声明.Parameters.Append .CreateParameter("RETURN",2,4)'以下两参数的声明顺序也做相应颠倒.Parameters.append .CreateParameter("@UserName",200,2,40).Parameters.append .CreateParameter("@UserID",3,1,4,UserID).Executeend withif MyComm(0) = 1 thenUserName = MyComm(1)elseUserName = "该用户不存在"end ifSet MyComm = Nothing6. 同时返回参数和记录集的存储过程有时候，我们需要存储过程同时返回参数和记录集，比如在利用存储过程分页时，要同时返回记录集以及数据总量等参数。以下给出一个进行分页处理的存储过程： /*SP6*/CREATE PROCEDURE dbo.getUserList@iPageCount int OUTPUT, --总页数@iPage int, --当前页号@iPageSize int --每页记录数asset nocount onbegin--创建临时表 create table #t (ID int IDENTITY, --自增字段userid int,username varchar(40))--向临时表中写入数据insert into #t select userid,username from dbo.[UserInfo]order by userid
--取得记录总数 declare @iRecordCount intset @iRecordCount = @@rowcount --确定总页数IF @iRecordCount%@iPageSize=0SET @iPageCount=CEILING(@iRecordCount/@iPageSize)ELSESET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
--若请求的页号大于总页数，则显示最后一页IF @iPage > @iPageCountSELECT @iPage = @iPageCount --确定当前页的始末记录DECLARE @iStart int --start recordDECLARE @iEnd int --end recordSELECT @iStart = (@iPage - 1) * @iPageSizeSELECT @iEnd = @iStart + @iPageSize + 1 --取当前页记录 select * from #t where ID>@iStart and ID<@iEnd --删除临时表DROP TABLE #t --返回记录总数return @iRecordCountendgo 在上面的存储过程中，输入当前页号及每页记录数，返回当前页的记录集，总页数及记录总数。为了更具典型性，将记录总数以返回值的形式返回。以下是调用该存储过程的ASP代码(具体的分页操作略去): '**调用分页存储过程**DIM pagenow,pagesize,pagecount,recordcountDIM MyComm,MyRstpagenow = Request("pn")'自定义函数用于验证自然数if CheckNar(pagenow) = false then pagenow = 1pagesize = 20Set MyComm = Server.CreateObject("ADODB.Command")with MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串.CommandText = "getUserList" '指定存储过程名.CommandType = 4 '表明这是一个存储过程.Prepared = true '要求将SQL命令先行编译'返回值(记录总量) .Parameters.Append .CreateParameter("RETURN",2,4)'出参(总页数).Parameters.Append .CreateParameter("@iPageCount",3,2)'入参(当前页号).Parameters.append .CreateParameter("@iPage",3,1,4,pagenow)'入参(每页记录数).Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize)Set MyRst = .Executeend withif MyRst.state = 0 then '未取到数据，MyRst关闭recordcount = -1elseMyRst.close '注意：若要取得参数值，需先关闭记录集对象recordcount = MyComm(0)pagecount = MyComm(1)if cint(pagenow)>=cint(pagecount) then pagenow=pagecountend ifSet MyComm = Nothing '以下显示记录if recordcount = 0 thenResponse.Write "无记录"elseif recordcount > 0 thenMyRst.opendo until MyRst.EOF......loop'以下显示分页信息......else 'recordcount=-1Response.Write "参数错误"end if 对于以上代码，只有一点需要说明：同时返回记录集和参数时，若要取得参数，需先将记录集关闭，使用记录集时再将其打开。7. 返回多个记录集的存储过程本文最先介绍的是返回记录集的存储过程。有时候，需要一个存储过程返回多个记录集，在ASP中，如何同时取得这些记录集呢？为了说明这一问题，在userinfo表中增加两个字段：usertel及usermail，并设定只有登录用户可以查看这两项内容。 /*SP7*/CREATE PROCEDURE dbo.getUserInfo@userid int,@checklogin bitasset nocount onbeginif @userid is null or @checklogin is null returnselect usernamefrom dbo.[usrinfo]where userid=@userid--若为登录用户，取usertel及usermailif @checklogin=1select usertel,usermailfrom dbo.[userinfo]where userid=@useridreturnendgo 以下是ASP代码： '**调用返回多个记录集的存储过程**DIM checklg,UserID,UserName,UserTel,UserMailDIM MyComm,MyRstUserID = 1'checklogin()为自定义函数，判断访问者是否登录checklg = checklogin()Set MyComm = Server.CreateObject("ADODB.Command")with MyComm.ActiveConnection = MyConStr 'MyConStr是数据库连接字串.CommandText = "getUserInfo" '指定存储过程名.CommandType = 4 '表明这是一个存储过程.Prepared = true '要求将SQL命令先行编译.Parameters.append .CreateParameter("@userid",3,1,4,UserID).Parameters.append .CreateParameter("@checklogin",11,1,1,checklg)Set MyRst = .Executeend withSet MyComm = Nothing '从第一个记录集中取值UserName = MyRst(0)'从第二个记录集中取值if not MyRst is Nothing thenSet MyRst = MyRst.NextRecordset()UserTel = MyRst(0)UserMail = MyRst(1)end ifSet MyRst = Nothing 以上代码中，利用Recordset对象的NextRecordset方法，取得了存储过程返回的多个记录集。至此，针对ASP调用存储过程的各种情况，本文已做了较为全面的说明。最后说一下在一个ASP程序中，调用多个存储过程的不同方法。在一个ASP程序中，调用多个存储过程至少有以下三种方法是可行的：1. 创建多个Command对象
DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command")'调用存储过程一......Set MyComm = NothingSet MyComm = Server.CreateObject("ADODB.Command")'调用存储过程二......Set MyComm = Nothing...... 2. 只创建一个Command对象，结束一次调用时，清除其参数 DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command")'调用存储过程一.....'清除参数(假设有三个参数)MyComm.Parameters.delete 2MyComm.Parameters.delete 1MyComm.Parameters.delete 0'调用存储过程二并清除参数......Set MyComm = Nothing 此时要注意：清除参数的顺序与参数声明的顺序相反，原因嘛，我也不知道。 3. 利用Parameters数据集合的Refresh方法重置Parameter对象 DIM MyCommSet MyComm = Server.CreateObject("ADODB.Command")'调用存储过程一.....'重置Parameters数据集合中包含的所有Parameter对象MyComm.Parameters.Refresh'调用存储过程二.....Set MyComm = Nothing
一般认为，重复创建对象是效率较低的一种方法，但是经测试(测试工具为Microsoft Application Center Test)，结果出人意料：方法2 >= 方法1 >> 方法3方法2的运行速度大于等于方法1(最多可高4%左右)，这两种方法的运行速度远大于方法3(最多竟高达130%)，所以建议在参数多时，采用方法1，在参数较少时，采用方法2。
来源：chinazhan
====================================================================================================================结束=====================================================================================================================
这是一个用储存过程实现分页查询的例子，本人没有测试过，先给大家看看。
实现数据分页查询的方案相当多，前台和后台都有很多好方法，这些好方法都有一个共同的特点：在实现分页的同时，考虑了网络资源的占有问题。本文要讨论的是使用SQL Server存储过程的实现方法。
引子

在含有ID主键（且ID连续）的Tab表中，查找第51行到第100行数据，对应的SQL语句为：
SLECET*FROME tab WHERE ID BETWEEN 51 AND 100
如果ID不连续，或者主键为其他，则可以用下SQL语句实现同样的功能：
SELECT TOP 50 * FROM tab WHERE ID NOT IN (SELECT TOP 50 ID FROM tab)
或是SELECT TOP 50 * FROM tab WHERE ID>@lastpage_endidi
如果用变量参数控制输入行，则使用以下语句：
SET ROWCOUNT@pagesize
SELECT * FROM tab WHERE ID>@lastpage_endid
问题

对于没有主键的表，可能存在大量重复的记录，很多SQL Server使用者喜欢用下面的语句：
SELECT IDENTITY(INT,1,1) AS ID,* INTO #T FROM tab
SELECT * FROM #T WHERE ID BETWEEN 51 AND 100
上面的方法非常笨拙，而且相当耗资源。
分析

对于这种没有主键的表，要实现分页查询，笔者认为最好的方法是加一个IDENTITY属性的主键，然后使用文本开头使用的两种方法，效率要高得多。在原表中加入IDENTITY属性的语句如下：
ALTER TABLE tab ADD ID INT IDENTITY PRIMARY KEY
并不是所有用户都有修改表的权限，下面介绍一种通用的方法：使用SQL Server 提供的储存过程sp_cursoropen。具体用法如下：
exec sp_cursoropen @P1 output,@sqlstr
exec sp_cursorfetch @P1,16,@begincol,@pagesize
exec sp_cursorclose @P1
其中第一句的@P1为生成的游标ID，@sqlstr为定义游标的SLELECT字符串；第二句中@begincol为起始行数，@pagesize为输出行数；第三句sp_cursorclose意即关闭游标。
解决

以下是笔者编写的储存过程，通过传入表名，分页取出第N页数据。
Create proc getpage
(@tablename varchar (255), @page count int=1,@pagesize int=99999999)--@tablename为表名
as
begin
set nocount on
declare @P1 int
declare @sqlstr nvarchar(400)
set @pagecount =(@pagecount-1)*@pagecount+1
set @sqlstr=’select * from ’+@tablename
exec sp_cursoropen @P1 output,@sqlstr
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1
end
调用方法

esec getpage’tab’,10,100
--表名tab ,第10页，每页100行。
进阶

以上存储过程比较通用，不过如果适当修改一下，把@sqlstr当作转入参数，就更灵活了，实现方法如下：
Create proc getpage
(@sqlstr nvarchar (4000),@pagecont int=1,@pagesize int=99999999)
as
begin
set nocount on
declare @P1 int
set @pagecount =(@pagecount-1)*@pagecount+1
exec sp_cursoropen @P1 output,@sqlstr
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1
end
调用方法

exec getpage’SELECT * FROM tab WHERE条件 ’,10,100
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1543668

我的大学

星期六, 十二月 15, 2007

电脑打开文件和点击鼠标右键特别慢（拔掉网线恢复正常）

右键菜单里的“新建文件夹”不见了，如何恢复？

星期日, 六月 24, 2007

快毕业啦~！

【转】如何在毕业答辩中取得高分

星期四, 六月 14, 2007

【转】sql存储过程基础语法

标签

博客归档

我的简介