Transact-SQL编程

在下面部分的许多语句都不是SQL标准语句，而是一个叫做Transact-SQL(T-SQL)的SQL扩展的一部分。T-SQL通过在别的事物上添加一些诸如变量、条件和循环之类的传统编程元素来扩充SQL。 

你可能注意到这个问题的唯一原因是你计划使用一种和Microsoft SQL Server不同的数据库服务器。在下面部分介绍的语句和技术并不是所有都是可替换的。然而，如果你想全面的利用Microsoft SQL Server的性能，你必须使用T-SQL。 

在批处理中执行多个语句 

为了一次能够执行多个SQL语句，你可以把它们一起放入一个批处理中。批处理，如同它的名字所隐含的意识一样，是简单的一个或多个SQL语句组成的语句组。比如，假如你在ISQL/w中输入下面的两个语句，它们就会做为一个批处理而一起执行： 

SELECT pub_name FROM publishers 

SELECT pub_name FROM publishers 

当你执行上面那个简单的批处理时，在表中同样的数据被读取了两次---一个SELECT 语句读取一次。现在假设你输错了第二个SELECT语句中表的名称，并尝试执行下面的语句： 

SELECT pub_name FROM publishers 

SELECT ub_name FROM publishers 

你可能期望第一个语句能够成功执行，而在执行第二个语句时会发生错误。而实际上并不是这样，在大多数情况下，如果在批处理中有任何一个语句不能成功执行。在其中没有一个语句会执行（特殊情况请看下面的注意）。SQL Server 把在批处理中的语句当成组来看待。 

注意 

在一些情况下，一个语句在发生错误时，其他语句仍然能够执行。假如服务器在传递和编译一个批处理时检测到错误，则在批处理中的所有语句都不能执行。假如批处理通过了编译但仍然存在错误，则在其中的某些语句就可能会执行。 

你可以把一组语句分成隔离的批处理。在ISQL/w中，你可以使用命令GO来实现该功能。考察下面的语句： 

SELECT pub_name FROM publishers 

GO 

SELECT ub_name FROM publishers 

假如你在ISQL/w中执行上面的语句。即使在第二个SELECT语句中存在错误。第一个语句仍然能够成功地返回答案集。GO命令强迫SQL Server 将此二个语句看成两个批处理。

给变量赋值 

SQL Server 具有两种类型的变量：全局变量和局部变量。全局变量是只读的，你不能改变它们的值。然而你可以在多个批处理中接收全局变量的值。局部变量则相反，仅局限于专门的某一个批处理。局部变量的优势在于你可以修改和读取它们的值，在本部分中，你将学习如何使用这两种类型的变量。 

全局变量 

全局变量的个数是有限的，你不能自己创建全局变量。两个非常重要的全局变量是@@IDENTITY和@@ROWCOUNT。@@IDENTITY变量保留最近一次插入到IDENTITY列的值，如下面的例子： 

INSERT Authors(author_name) VALUES(‘James Joyce’) 

INSERT Books(book_id,book_title) VALUES(@@IDENTITY,’Portrait of Artist as a Yong Man’) 

假定表Authors具有两列，第一列是author_id,它是个IDENTITY列。它用于给每个Authors表中的作者一个唯一的标志，第二列author_name用于保留作者的姓名。假定表Books同样也具有两列。第一列book_id是一个INT列，第二列book_title是一个具有VARCHAR属性的列。 

在该批处理执行时，Authors表中标志列的值就会插入到Books表的整数列中。这就允许你进行两个表的联合查询。比如，假定你想从两个表中得到作者的姓名和他们的书时，你就可以执行下面的SELECT语句： 

SELECT author_name,book_title FROM Authors,Books 

WHERE author_id=book_id 

对于@@IDENTITY变量来说，一个很重要的特征是如果有一个记录插入了一个没有标志列的表之后，该变量的值就会变成NULL。例如下面的批处理： 

INSERT Authors(author_name) VALUES(‘James Joyce’) 

SELECT @@IDENTITY 

SELECT @@IDENTITY 

INSERT Books(book_id,book_title) 

VALUES(@@IDENTITY,’Portrait of the Artist as a Young Man’) 

SELECT @@IDENTITY 

SELECT @@IDENTITY 

在该批处理执行时，第一个和第二个SELECT语句返回Authors表中标志列的值。而第三个和第四个SELECT语句则返回空值，因为在他们之前执行了另一个INSERT 语句。 

注意 

当你要在Active Server Pages中接收全局变量的值时，你必须为该变量提供一个列名。比如，为了在ASP脚本中接收全局变量@@IDENTITY的值，使用如下的语句： 

SELECT @@IDENTITY ‘myidentity’ 

第二个重要的全局变量是@@ROWCOUNT,@@ROWCOUNT变量记录最近一个语句执行时所影响到的列的数目。为了能够更清晰地了解该变量的工作原理，请看下面的批处理： 

UPDATE Authors SET author_name=’Samel Clemens’ 

Where author_name=’Mark Twain’ 

SELECT @@ROWCOUNT 

SELECT * FROM Authors Where 1=2 

SELECT @@ROWCOUNT 

在UPDATE语句执行之后，变量@@ROWCOUNT的值等于在数据库中名字为’Samel Clement’的作者的数目。当最后一个SELECT语句执行后，它返回0个记录，因此变量@@ROWCOUNT的值被置为0（短句where 1=2保证没有记录会返回，因为没有一个记录会满足该条件） 

你不能创建自己的全局变量同时你也不能给已存在的全局变量赋值。这意味着你不能使用变量在多个批处理之间传递信息。 

假如你需要存储一些能被多个批处理使用的信息，你必须把信息保存在表中。你并不需要构建所用的数据库非常之大，你只需要创建一些只具有很少几列的小表。在这些表中的每一列都和在传统编程语言中的全局变量的作用非常相似。 

注意 

你也可以使用临时表格来模拟传统的全局变量。临时表格是一个特殊的表格，它不永久地存在于数据库中，关于更多的信息，请参看SQL Server自带的文档。 

　 

局部变量 

在SQL中的局部变量和在传统编程语言中的变量非常相似。你可以定义自己的局部变量，并且给他们赋值。对于局部变量而言，最大的局限是他只能存活在定义它的批处理中。下面是使用局部变量的一个批处理例子： 

DECLARE @myvariable INT 

SELECT @myvariable=2+2 

SELECT @myvariable 

所用的局部变量都以单个@字符开头。在上面的例子中，局部变量@myvariable首先定义成INT类型。然后，使用SELECT语句为该变量赋值为2＋2。最后一个SELECT语句返回该变量的值。 

在批处理中使用变量之前，你必须首先定义它。你在定义变量时为该变量提供了该变量的名称和他所具有的数据类型。你可以在一个定义语句中定义多个变量。如下所示： 

DECLARE @firstname VARCHAR（20），@secondname VARCHAR(20) 

SELECT @firstname=”MARK TWAIN” 

SELECT @secondname=”samnel clement” 

SELECT @firstname=@secondname 

在该批处理中，创建了两个变量：@firstname和@secondname。两个变量都定义成VARCHAR数据类型。下一步，使用SELECT语句为变量赋予不同的值。最后一个SELECT语句把第一个变量的值赋予另外一个。 

你也可以把查询结果赋予局部变量，这是一种非常有用的做法。考察下面的批处理： 

DECLARE @queryresults VARCHAR(20) 

SELECT @queryresults=author_name FROM Authors WHERE author_id=1 

SELECT @queryresults 

在该例子中，变量@queryresults赋予一个从Authors表中查询所得到的结果。比如：如果author_id等于1的作者名为James Joyce。则变量@queryresults的值就是James Joyce。 

在Authors表中使用的标志列用以确保最多只有一个值赋予变量。这就产生了另外两个问题。当你赋予变量一个没有返回值的查询结果时，会发生何种情况？当你赋予变量一个返回多个值的查询结果时，又会发生什么情况？ 

假如一个查询没有返回值，该变量保留它原先的值。当变量第一次定义时，该变量的值为NULL。因此，在前面的例子中，如果没有一个作者的author_id为1，则该变量将会保留值NULL。考虑下面的例子： 

DECLARE @queryresults INT 

SELECT @queryresults=12 

SELECT @queryresults=author_id FROM Authors WHERE 1=2 

SELECT @queryresults 

在该例子中，查询确保没有返回结果因为1不等于2。因为没有返回结果，所以变量@queryresults保留它原先的值12。 

假如你把一个返回多于一个值的查询结果赋予变量，变量将被赋予最后一个返回的值。比如：假定表Authors包含5个作者信息。如果在标志列中没有间断，在下面的批处理的最后，变量@queryresults的值将是5： 

DECLARE @queryresults INT 

SELECT @queryresults=author_id FROM Authors 

SELECT @queryresults 

当你的批处理文件编的越来越大时，你就会发现你会经常的使用局部变量。局部变量在当你需要对多个表格中的数据进行比较时非常的有用。你可以把存储在一个表中的数据存到一个局部变量中，然后和在另外一个表中的数据进行比较。下面就是一个简单的例子： 

DECLARE @queryresults INT 

SELECT @queryresults=author_id FROM Authors 

WHERE author_name=”James Joyce” 

SELECT book_title FROM Books WHERE author_id=@queryresults 

在该例子中，第一个SELECT语句用于接收James Joyce的author_id。这样James Joyce的唯一标志列就存储在局部变量@queryresults中。然后该变量就在第二个SELECT语句中的WHERE短句中使用。该SELECT语句通过使用变量来查询在表Books中由James Joyce写的书。

在屏幕上显示数据 

一般的，当你在执行批处理时并不需要在屏幕上打印任何结果。然而在你调试一组SQL语句时，PRINT语句就能派上用场。下面就是PRINT语句的一个简单例子： 

DECLARE @myvariable VARCHAR(30) 

SELECT @myvariable=”Hello There!” 

PRINT @myvariable 

你不能使用PRINT语句在屏幕上打印除字符类型以外的其它数据类型。假如你需要打印其它的数据类型，你首先必须使用CONVERT（）函数来转化他们。比如： 

DECLARE @myvariable VARCHAR(10),@mynumber INT 

SELECT @mynumber=12 

SELECT @myvariable=CONVERT(VARCHAR(10),@mynumber) 

PRINT @myvariable 

在上面的例子中，变量@mynumber首先赋值12。但是你不能直接打印该变量，因为该变量的值并不是字符类型。CONVERT（）函数用于把该值转化成VARCHAR数据类型，这样它就能够打印了。 

技巧 

假如你仅仅是因为调试的原因而观看变量的值，你可以使用SELECT语句而不是PRINT语句。使用SELECT语句，你并不需要把一个变量转换成字符类型。

 注释你的SQL语句 

当你的SQL语句集合变得越来越大而非常复杂时，你需要对你的语句进行注释。你可以使用两种方法在你的语句中添加注释。假如你需要添加单行的注释时，你可以在一行后使用两个连字符，如下所示： 

DECLARE @myvariable DATETIME 

SELECT @myvariable=GETDATE() –Gets The Current Date 

SELECT @DATENAME(dw,@myvariable)—Gets The Day of the Week 

当你使用双连字符时，在双联字符后面到本行的结尾之间部分全是注释。为了创建一个多行的注释，你需要把注释包含在字符/*和*/之间，如下所示： 

DECLARE @myvariable DATETIME 

/* The following statements retrieve the current date and time and extract the day of the week from the results. 

*/ 

SELECT @myvariable=GETDATE() 

SELECT DATENAME(dw,@myvariable) 

注释对文档的代码而言没有任何用处；他们只在调试程序时有用。假如你想临时让一部分的SQL语句失去效用，你可以简单的使用注释符号包含它们。当你准备再次包含这些语句时，你只需要删除注释符号。 

用条件控制语句的执行 

当你想根据某些条件来执行一个或多个SQL语句时，你需要使用conditional，请看下面的例子： 

IF (SELECT COUNT(*)FROM Authors) >10 PRINT “More than 10 Authors!” 

假如在表Authors中有多于10个的作者，该语句就会打印More than 10 Authors!结果。 

注意 

仔细观看该条件语句的结构。注意到缺了什么？这里没有THEN语句。假如你在条件语句中包含了THEN，就会产生错误。 

你可以创建一个可以执行一块语句的条件判定。只需要把你要执行的这块语句包含在关键字BEGIN和END中： 

IF DATENAME(mm,GETDATE())=”July” 

BEGIN 

PRINT “It is July” 

PRINT “Happy July” 

END 

假如你在执行该批处理时刚好是7月份。则包含在BEGIN和END中的语句将会执行。 

同样的，你还可以嵌套IF语句，如下面的例子所示： 

IF DATENAME(mm,GETDATE())=”July” 

BEGIN 

PRINT “It is July” 

PRINT “Happy July” 

IF DATEPART(dd,GETDATE())=4 

BEGIN 

PRINT “And it is the 4th” 

PRINT “Happy 4th of July” 

END 

END 

假如在你执行该语句时恰好是7月4号，则在内层的语句块也将会执行。下面就是你执行的结果（如果你是在7月4号执行该批处理的话） 

It is July 

Happy July 

And it is the 4th 

Happy 4th of July 

使用关键字ELSE和IF语句一起使用，可以创建逻辑上与判断语句相反的代码。下面的例子在星期五执行时打印It is Friday!，而在其他时间执行时打印Some other day。 

If DATENAME(dw,GETDATE())=”Friday” 

PRINT “It is Friday” 

ELSE 

PRINT “Some other day” 

最后你可以在条件语句中使用关键字EXISTS来判断查询是否返回结果。下面的例子就使用EXISTS语句来判断作者James Joyce是否在表Authors中： 

IF EXISTS(SELECT author_name FROM authors WHERE author_name=”James Joyce”) 

PRINT “James Joyce is an author” 

ELSE 

PRINT “James Joyce is not an author”

使用CASE控制语句执行 

假设你想在网页上显示你喜欢站点的名称，URL，和关于它的简单描述。更进一步，你想在列出这些站点时在站点的名称旁边指明每一个网站的类型。比如，在政府机关网站的旁边，你会显示单词Government；在商业网站的旁边，显示单词Commercial。为了做到这一点，你可能会创建一个具有4列的表。前三列和原先的一样，分别是名称列，URL列，简单描述列。另外你可能会添加一个新列，该列为每一个网站的类型。 

但在实际上，你不需要这样做…有一种能获得同样的结果但是更好的方法。 

按照上面的做法，你的数据表中就会包含多余的信息。因为在URL列中已经包含了每一个网站类型的信息。通过查看每一个URL最后的三个字母，你就能确定某个网站是商业的，政府的和教育的等等。所以你不需要在数据表中添加新列重复同样的信息。但是当你在读取数据表中的记录时你是如何在URL列中获取该信息的呢？你可以使用CASE表达式来做到这一点。如下面的例子所示： 

SELECT 

（ 

CASE 

WHEN site_url LIKE “%edu” THEN “Educational” 

WHEN site_url LIKE “%gov” THEN “Government” 

WHEN site_url LIKE “%com” THEN “Commercial” 

ELSE “Other” 

END 

) “TYPE”, 

site_name “Name”, 

site_URL “URL”, 

site_desc “Description” 

FROM site_directory 

　 

注意 

字母.edu，.gov，和.com都是顶级域名的例子，目前还有许多的顶级域名在使用，比如，假如某个网站的域名以字母.cn结尾，这就指明该网站建在中国大陆。 

当上面的SELECT语句执行时，你就会接收到正确的结果。为了返回正确的值，我们使用CASE表达式对site_url列进行了检测。比如，当site_url列的值以三个字母edu结尾时，就会返回值Educational。下面就是从该查询返回结果的一个例子： 

Type 
Name 
URL 
Description 

…………….. 
……………… 
厖厖厖 
厖厖厖 

Commercial 
Microsoft 
www.microsoft.com 
The Creators of Active Server Pages 

Commercial 
Yahoo 
www.yahoo.com 
One of the best Internet Directories 

Commercial 
Collegescape 
www.collegescape.com 
Apply to over 50 colleges online 

Educational 
MIT 
www.mit.edu 
The Massachusetts Institute of Technology 

Government 
The whitehouse 
www.whitehouse.gov 
The residence of the Prisedent 


使用CASE语句，你可以测试不同的条件，并且基于不同的结果返回不同的值。对于每一个测试，都有对应的WHEN短句。在该例子中，当在WHEN短句后面的语句为真时，返回在相应的THEN短句后面的值。如果没有一个WHEN短句后面的语句为真，则返回在ELSE短句后面的值。 

所有的多个WHEN短句和可以选择的单个ELSE短句都必须包含在表达式CASE和END之间。关键字CASE指明CASE语句的开始。而关键字END指明CASE语句的结束。 

CASE语句具有另外一种稍微不同的格式。考察在下面的例子内是如何使用CASE语句的： 

SELECT 

(CASE 

site_name 

WHEN “Yahoo” THEN “Internet Directory” 

WHEN “Microsoft” THEN “Software Giant” 

ELSE “Other” 

END 

)”Type”, 

site_name “Name”, 

site_URL “URL”, 

site_desc “Description” 

FROM site_directory 

该例子同样显示网站列表的类型，名称，URL和简要描述。所不同的是，在该例子中，每个网站的类型是由网站的名称决定的，而不是URL。下面就是该批处理的结果： 

Type 
Name 
URL 
Description 

Software Giant 
Microsoft 
www.microsift.com 
The Creators of Active Server Pages 

Internet Directory 
Yahoo 
www.yahoo.com 
One of the best Internet Directories 

Other 
Collegescape 
www.collegescape.com 
Apply to over 50 college online 

Other 
MIT 
www.mit.edu 
The Massachusetts Institute of Technology 

Other 
The whitehouse 
www.whitehouse.gov 
The residence of the President 


(5 row(s) affected) 

请注意在该例子中，CASE例子是如何书写的。在该CASE语句中，在WHEN短语后面并没有跟一个语句，而是要与site_name列的值相比较的值。 

当你具有多个条件且每一个条件都是可真或可假时，你必须使用第一种CASE语句格式。假如你需要把一个值和多个值比较时，则使用第二种格式。

使用RETURN语句从批处理中返回 

为了从批处理中退出，你可以使用RETURN语句。考察下面的例子： 

IF DATENAME（dw,GETDATE()）=”Saturday” 

BEGIN 

PRINT “It is Saturday” 

RETURN 

END 

PRINT “It is some other day” 

当该批处理在星期天执行时，在条件语句后面的语句块就会执行。当碰到RETURN语句时，该批处理立即就退出了。这也意味着下面的语句仅仅在不是星期天的某一天显示： 

It is some other day

把语句组入到事务中 

假如你想在你的网站上出售商业卡。首先，为了做到这一点，你必须创建两个表格，一个表格包含了要付帐的信用卡帐号的列表。另外一个表则包含了定购产品的定单的列表。无论何时，当一个客户在访问你的站点时定购了商业卡，你需要把信息同时添入到两个表格中。 

现在假设有一个访问你站点的客户定购了商业卡。首先该客户的信用卡号添加到信用卡表中。然而就在这一时刻。你Web服务器的硬盘驱动器突然坏了。该客户的定单并没有保留到产品定单表中。这就会使事情变得很糟糕。来访者的信用卡为商业卡付了账，但是确没有收到定购的产品。此时定购的客户肯定会对你的行为感到生气。你该怎样阻止该事情的发生呢？ 

在本节中，将会用到下面的三个语句：BEGIN TRANSACTION，COMMIT TRANSACTION和ROLLBACK TRANSACTION。一个事务（transaction）是一组语句，这组语句要么全部执行，要么全部不执行。下面就是如何使用这些语句的一个简单的例子： 

BEGIN TRANSACTION 

INSERT credit_cards (username,ccnumber) 

VALUES(‘Andrew Jones’,’5555-55-555-55-5555’) 

INSERT orders(username) VALUES (‘Andrew Jones’) 

COMMIT TRANSACTION 

在该例子中,我们使用BEGIN TRANSACTION 和COMMIT TRANSACTION来定义事务块.在该块中显示的语句只有在执行COMMIT TRANSACTION后才发生效用。假如在到达该语句之前的任一点发生了错误，所有在该事务中的语句都不会发生效用。比如在上面的例子中，如果你的硬盘在执行完第一个INSERT语句而在第二个语句之前发生了硬盘错误。没有数据会插入到任一表中。第一个语句会倒转回去，并不会有如何效果。 

你可以使用ROLLBACK TRANSACTION语句来显式地倒转一个事务，如下所示： 

BEGIN TRANSACTION 

INSERT credit_cards(username,ccnumber) 

VALUES(‘Andrew Jones’,’5555-55-555-55-5555’) 

INSERT orders(username) VALUES(‘Andrew Jones) 

IF DATENAME(dw,GETDATE())=”Tuesday” 

ROLLBACK TRANSACTION 

ELSE 

COMMIT TRANSACTION 

在上面的例子中，事务在除了星期二外的每天都递发。假如一个星期中的某一天刚好是星期二，则事务就会倒转回去，不会产生任何效果。换句话说，在在星期二不会收到任何新的定单。 

注意 

在和触发器一起使用时，ROLLBACK TRANSACTION命令特别有用。参看下面的“结合触发器和事务过程”部分。

使用存储过程 

该部分将向你介绍如何创建和使用你自己的存储过程。在SQL Server中存储过程是和传统的计算机应用程序最相近的事物，并具有如下的优点： 

假如你有一套复杂的SQL语句需要在多个Active Server Pages中执行。你可以把他们放入一个存储过程，然后执行该存储过程。这能够减少你Active Server Pages的大小。同时还能确保在每一页上执行的SQL语句都相同。 

当你执行一个SQL的批处理时。服务器首先必须编译在批处理中的所有语句。这不但需要时间，还要花费服务器资源。相比较而言，在存储过程第一次执行后，它就不需要重新编译了。通过使用存储过程，你可以跨过编译这一步，更快地执行SQL语句集合。从一个Active Server页中执行一个存储过程比执行一个SQL语句的集合更有效。 

你可以对存储过程输入输出值。这意味着存储过程非常的灵活，相同的存储过程可以根据不同的输入值返回不同的信息。 

当你向数据库服务器传递一个SQL语句集合时，必须传递其中的每一个独立语句，而当你执行存储过程时，相反的，仅仅传递一个简单的语句。通过使用存储过程，你可以减少在网络上的阻塞。 

你可以配置表的权限，比如用户只能通过使用存储过程来修改表。这就能增加在你数据库中表的安全性。 

你可以在其他的存储过程内部执行你的存储过程。这种策略就允许你在非常小的存储过程上建立非常复杂的存储过程。这也意味着你可以为许多不同的编程任务使用相同的存储过程。 

当你在Active Server页中添加SQL语句时，你必须仔细考虑能否把这些语句放置到存储过程中。上面提到的优点都是实质性的。如下一部分所示，存储过程是非常容易创建的。 

使用CREATE PROCEDURE创建存储过程 

你可以使用CREATE PROCEDURE来创建一个存储过程。下面就是一个非常简单的存储过程的一个例子： 

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors 

当你创建存储过程时，你必须给它指定一个名称。在本例子中，存储过程的名称为retrieve_authors。你可以给存储过程赋予任何你想要的名称，但最好你能够使该名称在一定程度上描述存储过程的功能。 

每一个存储过程都包括一个或多个SQL语句。为了指明是存储过程一部分的SQL语句，你只需简单地在关键词AS后面包含它们。在前面例子中的存储过程只包含一个SQL语句。当该存储过程执行时，它返回在Authors表中所有的记录。 

你可以使用EXECUTE语句来执行一个存储过程。比如，为了执行retrieve_authors存储过程，你可以使用如下的语句： 

EXECUTE retrieve_authors 

当你执行该存储过程时，所有包括在其中的SQL语句都会执行，在上面的例子中，会返回所有在Authors表中的记录。 

当在批处理中的第一个语句是调用存储过程时，你并不需要使用EXECUTE语句。你可以简单地提供存储过程的名称来执行存储过程。比如在ISQL/W中，可以象下面所示来执行存储过程： 

retrieve_authors 

这起同样的作用。存储过程会被执行，并会返回结果。然而如果在该存储过程之前还有其他的任何语句，你就会收到错误信息（一般地，语法错误）。 

当你创建和执行一个存储过程时，这仅仅是在某一个数据库的范围内完成。假设你在数据库MyDatabase内创建了存储过程retrieve_authors。如果没有指明过程调用，你就不能在另一个数据库比如MyDatabase2中调用存储过程retrieve_authors。假如你需要在Mydatabase2中执行存储过程retrieve_authors，你必须使用如下的语句（注意下面的两个点号）： 

EXECUTE Mydatabase..retrieve_authors 

一旦你已经创建了一个存储过程，你就能使用系统存储过程sp_helptext来观看在该存储过程的的SQL语句。比如，如果你输入命令sp_helptext retrieve_authors，就会显示下面的结果： 

text 

…………………………………………… 

CREATE PROCEDURE retrieve_authors AS SELECT * FROM Authors 

注意 

你可能感到奇怪的是，sp_helptext系统过程本身就是一种存储过程类型。它是一种系统的存储过程。（系统存储过程存储在Master数据库中，能够被所有的数据库访问。）为了满足你的好奇心，你可以使用命令sp_helptext sp_helptext来观看组成sp_helptext本身的SQL语句。 

你在创建完存储过程后，不能对其进行修改。假如你需要修改一个存储过程。你必须首先破坏它，然后重新构建之。为了破坏一个存储过程。你可以使用DROP PROCEDURE语句，例如下面的语句删除retrieve_authors存储过程： 

DROP PROCEDURE retrieve_authors 

注意 

你可以使用系统存储过程sp_help来观看在当前数据库中所有存储过程的列表。假如你不加任何修改地执行了sp_help。该过程会显示在当前数据库中所有的存储过程、触发器和表。假如在sp_help后面跟上指定的存储过程，sp_help会仅仅显示那个存储过程的信息。

给存储过程传值 

当你调用一个存储过程时，你可以使用参数来传值给它，从而使你的存储过程变得非常的灵活。比如，你想修改过程check_philosophers，使之能够检测是否存在某一个哲学家。你可以使用如下的语句进行修改： 

CREATE PROCEDURE check_philosophers 

(@philosopher VARCHAR(30)) 

AS 

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher) 

PRINT “A philosopher” 

ELSE 

PRINT “Not a philisopher” 

当该过程执行时，它检查传递给变量@philosopher的姓名是否存在于表Philosophers中。假如@philosopher的值存在于表中，打印文本“A philosopher”，否则打印文本“Not a philisopher” 

当你在存储过程中包含参数时，你把它们列在CREATE PROCEDURE语句的后面的括号内，但必须在关键词AS之前。对于每一个参数，你都必须指明数据类型。在一个单独的过程中，你最多能设置255个参数。比如下面的过程检查是否在传递的姓名当中，至少有一个是哲学家的姓名： 

CREATE PROCEDURE check_philosophers 

(@firstname VARCHAR(30)，@secondname VARCHAR(30)) 

AS 

IF EXISTS(SELECT name FROM Philosophers 

WHERE name=@firstname OR name=@secondname) 

PRINT “At least one of them is a philosopher” 

ELSE 

PRINT “Neither one of them is a philisopher” 

为了执行一个具有一个或多个参数的存储过程，你只需简单地在存储过程名称的后面列出参数的值。比如下面的语句检查Plato和Aristotle中是否至少有一个是哲学家： 

EXECUTE check_philisophers “Plato”,”Aristotle” 

假如一个存储过程具有多个参数。你必须以正确的顺序来传值。有时候这并不是很方便。你可以使用另一种方法，通过名称来传递参数，如下所示： 

EXECUTE check_philisophers @firstname=“Plato”, @secondname=”Aristotle” 

该语句完成于前一语句完全相同的事情。然而通过使用参数名，你可以使用任何你想要的顺序来传递参数。

从存储过程中获得值 

你可以从存储过程中接受值。这些值可以直接在你的Active Server Pages中使用（参看第24章“使用Commands”）。同样，你可以在其他的存储过程中获得这些值。假如第一个过程调用了第二个存储过程，则第一个过程能接受有第二个过程设置的参数值。 

例如，下面的存储过程输出变量@conclusion的值： 

CREATE PROCEDURE check_philosophers 

(@philosopher VARCHAR(30)，@conclusion VARCHAR(30) OUTPUT) 

AS 

IF EXISTS(SELECT name FROM Philosophers WHERE name=@philosopher) 

SELECT @conclusion= “A philosopher” 

ELSE 

SELECT @conclusion= “Not a philisopher” 

注意在本例子中关键词OUTPUT的使用。该关键词紧跟在参数@conclusion的定义后面。这指明该参数将会用于从该过程中输出信息。在这个简单的例子中，参数的值将会是“A philosopher”或“Not a philisopher”，根据变量@philosophe的值的不同而变化。 

为了这些一个具有输出参数的存储过程，你需要在EXECUTE语句中使用关键词OUTPUT 。假如你在一个批处理或者另外一个存储过程中执行该过程时，你必须首先定义一个变量用于存储从过程中传递出的值，如下面的例子所示： 

DELCARE @proc_results VARCHAR(30) 

EXECUTE check_philosophers “Plato”,@proc_results OUTPUT 

PRINT @proc_results 

在该例子中的第一个语句定义了将用于存储从过程check_philosophersZ中传出的参数值的变量。该变量将和输出参数的数据类型一模一样。第二个语句执行存储过程。注意变量@proc_results后面必须紧跟关键词OUTPUT。最后变量@proc_results的值被打印到屏幕上。 

你同样可以使用名称来接收输出参数的值，下面就是一个简单的例子： 

DECLARE @proc_results VARCHAR(30) 

EXECUTE check_philosophers @philosopher=”Plato”,@conclusion=@proc_results OUTPUT 

PRINT @proc_results 

注意在该EXECUTE语句中，参数的名称总是列在前面。你要使用@conclusion=@proc_results来接收参数@conclusion的值，而不是你可能期望的@proc_results=@conclusion。

在存储过程中使用RETURN语句“In the Philosophers Table”“In the Authors Table”“Not in any tables!””。RETURN语句会在一旦存在匹配时，立刻退出该过程。”来执行该过程。首先使用表Philosophers来检测是否存在“Plato”。由于该名字存在于该表中，所以过程打印文本”In the Philosophers table”，然后过程在执行到RETURN语句时，退出。你可以使用RETURN语句结束任何过程。这实际上并不完成任何事情。因为在任何情况下，过程总是要退出的。

在上面的文章中我们已经介绍了RETURN语句的使用。在存储过程中该语句的用法和你在批处理中的用法是一模一样的。RETURN语句会导致过程立即退出。考察下面的例子：

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

PRINT

RETURN

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

PRINT

RETURN

END

PRINT

RETURN

该过程检查了两个表以判断一个人是否是哲学家或作者。假如提供的名称即不是哲学家也不是作者，打印文本“Not in any tables!

比如，假定你使用参数“Plato

注意

 

当你在存储过程而不是在批处理中使用RETURN语句时，你能够返回一整数值。该整数值代表一个状态码。下面就是上面的例子经过改写后，返回特定的整数值。

CREATE PROCEDURE check_tables

(@who VARCHAR(30))

AS

IF EXISTS(SELECT name FROM Philosophers WHERE name=@who)

BEGIN

RETURN(1)

END

IF EXISTS(SELECT author_name FROM Authors WHERE author_name=@who)

BEGIN

RETURN(2)

END

RETURN(3)

该过程完成和前面的那个过程相同的任务。然而当在表中发现该姓名时，并不打印信息，代替地，该过程使用RETURN语句来指明从那个表中找到该名称。例如，假如你使用参数“James Joyce”来执行该过程时。存储过程会返回值2，因为James Joyce在表Authors中，而不是在表Philosophers中。你可以在Active Server Page或其他的存储过程中使用状态值来确定该姓名所在的表。

当使用状态值时，你必须使用对于1或小于-99的值。SQL Server使用值0来报告一个存储过程的成功执行。它同时使用小于0对于-100的值来报告错误(参看表12。1以获得SQL Server使用的状态值的完全列表)。

　

值

意思

0

过程成功执行。

-1

对象丢失。

-2

发生数据类型错误。

-3

处理过程被死锁。

-4

发生权限错误。

-5

发生语法错误。

-6

发生恶意用户错误。

-7

发生资源错误，比如空间不够等。

-8

遭遇非致命的内部问题。

-9

遭遇系统限制。

-10

发生致命的内部不稳定性。

-11

发生致命的内部不稳定性。

-12

表或索引被破坏。

-13

数据库被破坏。

-14

发生硬盘错误。

包含在-15到-99之间的值有SQL Server保留以在将来使用。

　当你使用RETURN语句返回状态值时，切记不要返回NULL值。这会导致错误的结果。“James Joyce”“James Joyce”

表12.1 过程状态值

注意

 

为了获得在前面例子中过程返回的状态值，你可以使用如下的语句：

DECLARE @conclusion INT

EXECUTE @conclusion=check_tables

SELECT @conclusion

变量@conclusion用于保存状态值。它必须定义成INT整数数据类型。当你执行存储过程check_tables时，使用下面的语句将状态值赋予该变量：

EXECUTE @conclusion=check_tables