1、设计存储过程
几乎任何可写成批处理的 Transact-SQL 代码都可用于创建存储过程。
2、存储过程的设计规则
存储过程的设计规则包括：
- CREATE PROCEDURE 定义本身可包括除下列 CREATE 语句以外的任何数量和类型的 SQL 语句，存储过程中的任意地方都不能使用下列语句：
CREATE DEFAULT CREATE TRIGGER CREATE PROCEDURE CREATE VIEW CREATE RULE
- 可在存储过程中创建其它数据库对象。可以引用在同一存储过程中创建的对象，前提是在创建对象后再引用对象。
- 可以在存储过程内引用临时表。
- 如果在存储过程内创建本地临时表，则该临时表仅为该存储过程而存在；退出该存储过程后，临时表即会消失。
- 如果执行调用其它存储过程的存储过程，那么被调用存储过程可以访问由第一个存储过程创建的、包括临时表在内的所有对象。
- 如果执行在远程 Microsoft® SQL Server™ 2000 实例上进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。
- 存储过程中参数的最大数目为 2100。
- 存储过程中局部变量的最大数目仅受可用内存的限制。
- 根据可用内存的不同，存储过程的最大大小可达 128 MB。
3、限定存储过程内的名称
在存储过程内部，如果用于诸如 SELECT 或 INSERT 这样的语句的对象名没有限定用户，那么用户将默认为该存储过程的所有者。在存储过程内部，如果创建存储过程的用户没有限定 SELECT、INSERT、UPDATE 或 DELETE 语句中引用的表名，那么通过该存储过程对这些表进行的访问将默认地受到该过程的创建者权限的限制。
4、加密过程定义
如果有其他用户要使用存储过程，则用于语句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的对象名必须用该对象所有者的名称限定。例如，Mary 拥有表 marytab，如果她希望其他用户能够执行使用该表的存储过程，必须在该表用于上述某一条语句时对其表名进行限定。
此规则是必需的，因为运行存储过程时将解析对象的名称。如果未限定 marytab，而 John 试图执行该过程，SQL Server 将查找 John 所拥有的名为 marytab 的表。
如果要创建存储过程，并且希望确保其他用户无法查看该过程的定义，那么可以使用 WITH ENCRYPTION 子句。这样，过程定义将以不可读的形式存储。
存储过程一旦加密其定义即无法解密，任何人（包括存储过程的所有者或系统管理员）都将无法查看存储过程定义。
5、SET 语句选项
当 ODBC 应用程序与 SQL Server 连接时，服务器将自动设置会话的下列选项：
- SET QUOTED_IDENTIFIER ON
- SET TEXTSIZE 2147483647
- SET ANSI_DEFAULTS ON
- SET CURSOR_CLOSE_ON_COMMIT OFF
- SET IMPLICIT_TRANSACTIONS OFF
这些设置将提高 ODBC 应用程序的可移植性。由于基于 DB-Library 的应用程序通常不设置这些选项，所以应在上述所列 SET 选项打开和关闭的情况下都对存储过程进行测试。这样可确保存储过程始终能正确工作，而不管特定的连接在唤醒调用该存储过程时可能设置的选项。需要特别设置其中一个选项的存储过程，应在开始该存储过程时发出一条 SET 语句。此 SET 语句将只对该存储过程的执行保持有效，当该存储过程结束时，将恢复原设置。
示例
A. 创建使用参数的存储过程
下例创建一个在 pubs 数据库中很有用的存储过程。给出一个作者的姓和名，该存储过程将显示该作者的每本书的标题和出版商。
CREATE
PROC
au_info
@lastname
varchar
(
40
),
@firstname
varchar
(
20
)
AS
SELECT
au_lname, au_fname, title, pub_name
FROM
authors
INNER
JOIN
titleauthor
ON
authors.au_id
=
titleauthor.au_id
JOIN
titles
ON
titleauthor.title_id
=
titles.title_id
JOIN
publishers
ON
titles.pub_id
=
publishers.pub_id
WHERE
au_fname
=
@firstname
AND
au_lname
=
@lastname
GO
将出现一条说明该命令未返回任何数据也未返回任何行的消息，这表示已创建该存储过程。
现在执行 au_info 存储过程：
EXECUTE au_info Ringer, Anne
GO
下面是结果集：
| au_lname | au_fname | title | pub_name |
| --------- | --------- | --------------------- | ---------------- |
| Ringer | Anne | The Gourmet Microwave | Binnet & Hardley |
| Ringer | Anne | Is Anger the Enemy? | New Moon Books |
(2 row(s) affected)
B. 创建使用参数默认值的存储过程
下例创建一个存储过程 pub_info2，该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名。如果未提供出版商的名称，该存储过程将显示由 Algodata Infosystems 出版的书籍的作者。
CREATE
PROC
pub_info2
@pubname
varchar
(
40
)
=
'
Algodata Infosystems
'
AS
SELECT
au_lname, au_fname, pub_name
FROM
authors a
INNER
JOIN
titleauthor ta
ON
a.au_id
=
ta.au_id
JOIN
titles t
ON
ta.title_id
=
t.title_id
JOIN
publishers p
ON
t.pub_id
=
p.pub_id
WHERE
@pubname
=
p.pub_name
执行未指定参数的 pub_info2：
EXECUTE pub_info2
GO
下面是结果集：
| au_lname | au_fname | pub_name |
| ---------------- | ---------------- | -------------------- |
| Green | Marjorie | Algodata Infosystems |
| Bennet | Abraham | Algodata Infosystems |
| O'Leary | Michael | Algodata Infosystems |
| MacFeather | Stearns | Algodata Infosystems |
| Straight | Dean | Algodata Infosystems |
| Carson | Cheryl | Algodata Infosystems |
| Dull | Ann | Algodata Infosystems |
| Hunter | Sheryl | Algodata Infosystems |
| Locksley | Charlene | Algodata Infosystems |
(9 row(s) affected)
C. 执行用显式值替代参数默认值的存储过程
在下例中，存储过程 showind2 的 @table 参数默认值是 titles。
CREATE
PROC
showind2
@table
varchar
(
30
)
=
'
titles
'
AS
SELECT
TABLE_NAME
=
sysobjects.name,
INDEX_NAME
=
sysindexes.name, INDEX_ID
=
indid
FROM
sysindexes
INNER
JOIN
sysobjects
ON
sysobjects.id
=
sysindexes.id
WHERE
sysobjects.name
=
@table
列标题（例如，TABLE_NAME）可使结果更具可读性。下面是该存储过程显示的 authors 表的情况：
EXECUTE showind2 authors
GO
| TABLE_NAME | INDEX_NAME | INDEX_ID |
| ---------- | ---------- | ---------- |
| authors | UPKCL_auidind | 1 |
| authors | aunmind | 2 |
(2 row(s) affected)
如果用户未提供值，则 SQL Server 将使用默认表 titles：
EXECUTE showind2
GO
下面是结果集：
| TABLE_NAME | INDEX_NAME | INDEX_ID |
| ---------- | ---------- | ---------- |
| titles | UPKCL_titleidind | 1 |
| titles | titleind | 2 |
(2 row(s) affected)
D. 使用参数默认值 NULL 创建存储过程
参数默认值可以是 NULL 值。在这种情况下，如果未提供参数，则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。
过程定义还可指定当不给出参数时要采取的其它某种措施。例如：
CREATE
PROC
showind3
@table
varchar
(
30
)
=
NULL
AS
IF
@table
IS
NULL
PRINT
'
Give a table name
'
ELSE
SELECT
TABLE_NAME
=
sysobjects.name,
INDEX_NAME
=
sysindexes.name, INDEX_ID
=
indid
FROM
sysindexes
INNER
JOIN
sysobjects
ON
sysobjects.id
=
sysindexes.id
WHERE
sysobjects.name
=
@table
E. 使用包含通配符的参数默认值创建存储过程
如果存储过程将参数用于 LIKE 关键字，那么默认值可包括通配符（%、_、[] 和 [^]）。例如，可将 showind 修改为当不提供参数时显示有关系统表的信息：
CREATE
PROC
showind4
@table
varchar
(
30
)
=
'
sys%
'
AS
SELECT
TABLE_NAME
=
sysobjects.name,
INDEX_NAME
=
sysindexes.name, INDEX_ID
=
indid
FROM
sysindexes
INNER
JOIN
sysobjects
ON
sysobjects.id
=
sysindexes.id
WHERE
sysobjects.name
LIKE
@table
在存储过程 au_info 的下列变化形式中，两个参数都有带通配符的默认值：
如果执行 au_info2 时不指定参数，将显示姓以字母 D 开头的所有作者：
EXECUTE au_info2
GO
下面是结果集：
| au_lname | au_fname | title | pub_name |
| -------- | -------- | --------------------- | ------------------- |
| Dull | Ann | Secrets of Silicon Val | Algodata Infosystems |
| del Castillo | Innes | Silicon Val Gastrono | Binnet & Hardley |
| DeFrance | Michel | The Gourmet Microwave | Binnet & Hardley |
(3 row(s) affected)
下例在两个参数的默认值已定义的情况下，省略了第二个参数，因此可找到姓为 Ringer 的所有作者的书和出版商：
EXECUTE au_info2 Ringer
GO
| au_lname | au_fname | title | pub_name |
| --------- | --------- | ---------------------- | ---------------- |
| Ringer | Anne | The Gourmet Microwave | Binnet & Hardley |
| Ringer | Anne | Is Anger the Enemy? | New Moon Books |
| Ringer | Albert | Is Anger the Enemy? | New Moon Books |
| Ringer | Albert | Life Without Fear | New Moon Books |
(4 row(s) affected)
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
