PETSHOP研究笔记 数据存储

 下面是存储过程的一个简单示例，它返回一个主题列表。

CREATE PROCEDURE TopicsGetList
AS
SET NOCOUNT ON -- 不返回受影响行的值
SELECT
ID,
Title,
Description
FROM
Topics
ORDER BY
Title
RETURN @@ERROR

　　在本示例中，有几点需要指出。首先，请注意 SET NOCOUNT ON 行。它告诉 SQL Server 停止为该查询计算受影响的行数，并停止向调用函数返回该值。这是一项不必要的额外工作。其次，结尾处的 RETURN @@ERROR 一行很重要。此行代码返回 SQL Server 中发生的错误的整数值。您可以在调用例程中使用此代码完成其他诊断和错误处理操作。您现在并不需要执行任何操作，但它们是创建存储过程时应该遵循的两个好习惯。

　　下面是一个更复杂的存储过程。此过程用于从数据库中检索单条主题记录。您会发现一些附加项，包括输入参数、返回特定值的输出参数，以及检查输入参数并在需要时返回错误的某些程序代码。

CREATE PROCEDURE TopicsGetItem
(
@AdminCode char(3),
@ID int,
@Title varchar(30) OUTPUT,
@Description varchar(500) OUTPUT
)
AS
SET NOCOUNT ON -- 不返回受影响行的值
-- 确保是一个 Admin 用户
IF @AdminCode<>'adm'
BEGIN
RETURN 100 -- 无效 admin 错误
END
-- 检查记录是否存在
IF (SELECT Count(ID) FROM Topics WHERE ID=@ID)=0
BEGIN
RETURN 101 --- 无效 ID 代码
END
-- 继续执行并返回该记录
SELECT
@Title=Title,
@Description=Description
FROM
Topics
WHERE
ID=@ID
-- 返回错误，如果成功则返回 0
RETURN @@ERROR
　　在本示例中，还有几点需要指出。首先，您会在存储过程顶端看到一个参数列表。除前两个参数外，其他参数均被标记为 OUTPUT 参数。这些参数用于返回选定记录的值。使用一条记录的返回值要比返回带有所有字段的记录集合更为高效。

　　其次，您会发现用于检查 @AdminCode 参数值的 T-SQL 数据块，以确保传递正确的代码。如果传递的代码不正确，则传递返回代码 100 并停止执行该过程。再其次，您会发现检查 @ID 参数，以确保其代表一条现有记录。如果不是现有记录，则传送返回代码 101 并终止执行。最后，如果输入变量都有效，存储过程将尝试选择记录并返回相应的值。如果此时发生任何错误，将由该过程的最后一行代码进行处理。

　　注意：通常情况下，最好将自定义错误代码及其含义保存在数据库中的一个单独的表格中，或保存在解决方案可以访问的文本文件中。这样就可以轻松更新这些错误代码，并与解决方案中的其他子系统共享。因为这只是一个短小的示例，其中只使用了两个错误代码，所以我决定创建一个包含大量代码和消息的文档，以供其他子系统参考。

　　该解决方案中包含的存储过程超过 25 个。本文仅举一例进行说明，其他代码可以通过本文开始处的链接进行下载。最后这个示例使用一个自定义的内置标量函数。

　　使用自定义标量函数

　　有时，单独一个存储过程不足以解决问题。例如，我们的用户方案中就有一个方案要求列出某个问题的解答数目。解决此问题的方法之一是生成一个对问题的解答进行计数的子查询。另外一种方法是生成一个自定义函数，返回标量值并将其包含在问题查询中。这种方法还有一个好处，那就是我们可以在其他存储过程中再次使用该标量函数。

　　添加自定义函数的操作类似于添加存储过程。在 Server Explorer（服务器资源管理器）树中，在选定数据库的 Functions（函数）节点上单击鼠标右键，然后从上下文相关菜单中选择 New Scalar-Valued Function（新建标量值函数）。然后在编辑器中编辑该文档，并像保存存储过程那样保存该文档。

　　以下是自定义函数的代码：

CREATE FUNCTION dbo.fn_QuestionsGetResponseCount
(
@ID int
)
RETURNS int
AS
BEGIN
DECLARE @ResponseCount int
Set @ResponseCount =
(
SELECT
COUNT(Responses.ID)
FROM
Responses
WHERE
Responses.QuestionID=@ID
)
RETURN @ResponseCount
END
　　以下是使用自定义函数的存储过程：

CREATE PROCEDURE QuestionsGetCountWithNoResponses
(
@Total int OUTPUT
)
AS
SET NOCOUNT ON -- 不返回受影响行的值

SELECT
@Total=Count(ID)
FROM
Questions
WHERE
dbo.fn_QuestionsGetResponseCount(Questions.ID)=0

RETURN @@ERROR