v写在章节前面
存储过程是一段可执行的服务端程序。不同于用户定义函数(UDF),存储过程允许有副作用
,也就是说可以修改表中的数据,甚至可以修改对象的架构。
v存储过程种类
SQL Server2005支持不同类型的存储过程:用户定义存储过程、系统存储过程和扩展存储
过程。你可以使用T-SQL或CLR开发用户定义存储过程。
用户定义存储过程
用户定义存储过程在用户数据库中创建,通常与数据库对象进行交互。要调用一个用户定义存
储过程,指定EXEC命令和限定架构的存储过程名称以及参数:
EXEC dbo.usp_Proc1
v存储过程接口
下面我们介绍存储过程接口(即输入和输出参数)。
输入参数
你可已在存储过程头定义输入参数。当调用存储过程时,必须为输入参数提供值,除非使用默认值。下面的代码创建存
储过程usp_GetCustOrders,他接受一个消费者ID和一个时间范围作为输入,并返回消费者在指定时间范围内的订单。
USE Northwind
GO
IF OBJECT_ID(‘dbo. usp_GetCustOrders’) IS NOT NULL
DROP PROC dbo.usp_GetCustOrders;
GO
CREAET PROC dbo.usp_GetCustOrders
@custid AS NCHAR(5)
@fromdate AS DATETIME = ‘19000101’
@todate AS DATATIME = ‘99991231’
AS
SET NOCOUNT ON;
SELECTOrderID,CustomerID,EmployeeID,OrderDate
From dbo.Orders
Where CustomerID = @custid
And OrderDate >= @fromdate
And OrderDate
GO
【注】SET NOCOUNT ON通知SQL Server不要生成表示受影响行数的消息。一些客户端数据库接口,如OLEDB,会把该消息作为一个行集。结果,你得到的第一个结果集将是受影响行数的消息,而你只想获取查询的结果集。通过SET NOCOUNT ON就可以避免在这些接口中出现这种问题。
当调用存储过程的时候,你必须为那些在定义中未提供默认值的输入参数指定输入值。调用存储过
程时有两种为参数赋值的格式:非命名格式和命名格式。在非命名格式中,我们不需要指定参数名,只
需要指定参数值,但必须按参数声明的顺序指定输入。我们可以忽略那些包含默认值且位于参数列表末
尾的参数,但是却不能忽略已经为其提供值的两个参数之间的参数。如果想让参数使用默认值,需要为
该参数指定DEFAULT关键字。
下面我们只传第一个参数,后两个用默认值
EXEC dbo.usp_GetCustOrders N’ALFKI’;
如果你想为第三个参数指定值,但让第二个参数使用默认,应为第二个参数指定DEFAULT
EXEC dbo.usp_GetCustOrders N’ALFKI’ , DEFAULT , ‘20060212’
使用非命名赋值格式会导致很多维护方面的问题。如,必须按顺序指定参数,不能忽略可选参数等
。因此使用命名赋值格式是一个好的习惯:
EXEC dbo.usp_GetCustOrders
@custid = N’ALFKI’
@fromdate = ‘19970201’
@todate = ‘19980101’
输出参数
输出参数允许你从存储过程返回输出值。
下面我们修改dbo. usp_GetCustOrders存储过程,为其添加输出参数@numrows
ALTER PROC dbo.usp_GetCustOrders
@custid AS NCHAR(5)
@fromdate AS DATETIME = ‘19000101’
@todate AS DATATIME = ‘99991231’
@numrows AS INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @err AS INT
SELECTOrderID,CustomerID,EmployeeID,OrderDate
From dbo.Orders
Where CustomerID = @custid
And OrderDate >= @fromdate
And OrderDate
SELECT @numrows = @@rowcount, @err = @@error;
return @err;
GO