sql存储过程详解

要使用存储过程，首先要创建一个存储过程。 　存储过程可用CREATE PROCEDURE创建。 　执行存储过程使用EXECUTE语句。 　OUTPUT指定存储过程必须返回一个参数。 　查看存储过程可用sp_helptext。如EXEC sp_helptext BOOK_INFOR。 　修改存储过程用ALTER PROCEDURE。 　删除存储过程用DROP，如DROP PROCEDURE BOOK_INFOR。

　　　　　　　　不带输入参数的存储过程 我们为stores表创建一个不带参数的名为STORE_ENQUIRY的存储过程:

CREATE PROC STORE_ENQUIRY AS SELECT * FROM stores

直接在查询分析器执行。 请在查询分析器输入以下语句： EXEC STORE_ENQUIRY

                     　 带输入参数的存储过程                         第一种是使传递的参数和定义时的参数顺序一致: 下面创建了一个用于向stores表插入记录的存储过程STORE_INFOR。                CREATE PROC STORE_INFOR                  (                         @store_id char(20),                         @store_name char(20), @store_address char(20),                         @city char(10),                         @state char(10),    @zip char(5)                    ) AS INSERT INTO stores VALUES(@store_id,@store_name,@store_address,@city,@state, @zip) GO 这样我们创建了一个存储过程。第一行代码说明将要创建一个名为STORE_INFOR的过程。并在括号内对输入参数做了定义。AS用于把存储过程的签名与存储过程体分隔开来。在AS之后编写了一条插入语句。 接下来就可以在查询分析器执行。 请在查询分析器输入以下语句： EXECUTE STORE_INFOR ''1003'',''文兴书店'',''上沙路20号'',''深圳'',''奥'',''1003'' 这样，我们就在stores表中添加了一条记录！ 另一种传参的方式就是采用"@zip=''100"的形式，此时参数的顺序可以任意排列。例如： EXEC STORE_INFOR @zip=''23223'',@state=''奥'',@store_id=''1007'',@store_name=''文兴书店'',@store_address=''上沙路20号'',@city=''深圳''

参数也可以采用默认值，可以将上面的例子作如下定义： CREATE PROC STORE_INFOR ( @store_id char(20) , @store_name char(20), @store_address char(20)=''无'', @city char(10)=''无'', @state char(10), @zip char(5) ) AS INSERT INTO stores VALUES(@store_id,@store_name,@store_address,@city,@state,@zip) GO 此时，可以省略对默认值传递参数: EXEC STORE_INFOR @zip=''23223'',@state=''奥'',@store_id=''1007'',@store_name=''文兴书店'',

                             带输入和输出参数的存储过程 假设要找出客户要求的出货日期和实际出货日期两项数据。我们可以为此编写一个存储过程，该过程采用OrderId作为输入参数，在SELECT语句的两个输出参数中返回(RETURN)日期。但这里将在两个OUTPUT参数中检查日期。步骤如下： 1)创建名为sp_ShipDate的存储过程。 CREATE PROCEDURE sp_ShipDate (     @OrderId int,     @RequiredDate datetime OUTPUT,     @ShippedDate datetime OUTPUT ) AS SELECT @RequiredDate=Min(RequiredDate) FROM Orders SELECT @ShippedDate=Max(ShippedDate) FROM Orders WHERE OrderID=@OrderID RETURN

2)执行存储过程 DECLARE @OrderId int DECLARE @RequiredDate datetime DECLARE @ShippedDate datetime EXEC sp_ShipDate 2,@RequiredDate OUTPUT,@ShippedDate OUTPUT SELECT ''要求出货日期''=@RequiredDate,''实际出货日期''=@ShippedDate GO 这个例子返回两个输出值。 创建存储过程时，把@OrderId指定为int类型的输入参数。接着，把@RequiredDate datetime指定为datatime参数，用OUTPUT关键其加以限定，表示这是输出参数。 在过程体中，我们在OrderId列上使用Min和Max查询函数给输出参数赋值。完成操作时，使用RETRUN语句把0这个值返回给调用程序。

具有RETURN值的存储过程

这个例子我们将创建一个存储过程，如果定单数大于100，该过程就返回1，如果定单数小于100，就返回2。 于是，调用这个存储过程的应用程序将采用RETURN值，给用户提供适当的消息。步骤如下： 1）创建存储过程 CREATE PROC sp_OrderMoreThan100 AS DECLARE @Orders int SELECT @Orders=COUNT(*) FROM Orders

IF @Orders>100     RETURN 1 ELSE     RETURN 2

2）执行存储过程 DECLARE @Ret int EXEC @Ret=sp_OrderMoreThan100 SELECT @Ret 我们先声明了一个叫做@Orders的变量，用于保存定单数。接着使用COUNT(*)函数给@Orders变量赋值： SELECT @Orders = COUNT(*)。 最后，编写了一个简单的IF语句做判断。由于Orders表中有900个定单，所以返回值是1。 这个返回值非常有用，可以在应用程序中捕获这个值，通知用户数据庫中存在100多个定单。