SQL Server存储过程与自定义函数

例程（routine）是为了计算结果或执行任务而对代码进行封装的一种编程对象。SQL Server支持三种例程：用户定义函数，存储过程，触发器。

 

用户定义函数

用户定义函数（UDF，user-defined  function）的目的是要封装计算的逻辑处理，有可能需要基于输入的参数，并返回结果。

SQL Server支持两种用户定义函数：标量UDF和表值UDF。标量UDF只返回单个数据值。而表值UDF则返回一个表。使用UDF的优点之一是可以在查询中集成UDF。对于查询中返回单个值的表达式，在其出现的位置上，也能够使用标量UDF。表值UDF只能在查询的FROM子句中出现。

UDF不允许有任何副作用，副作用的含义是指不能对数据库中任何架构或者数据进行修改。

 

存储过程

存储过程是封装了T-SQL代码的服务端例程。存储过程可以有输入和输出参数，也可范湖多个查询的结果集。通过存储过程不但可以对数据进行修改，也可以对数据库架构进行修改。

和普通代码相比，存储过程有以下好处：

1. 存储过程可以封装逻辑处理。只需要修改数据库里的存储过程，所有用户都能使用修改后的版本。
2. 存错过程可以提高执行效率。存储过程默认情况下是重用执行计划的，而SQL Server对其他特殊执行计划有更多限制。另一个好处是减少网络的传输流量。客户端只需要向数据库发送存储过程的名称和参数。服务器就换处理所有代码，向调用者只返回输出结果。
3. 通过存储过程可以有更好的安全性
4. 在存储过程中可以整合所有的错误处理

 

错误处理

运行错误处理的主要工具是一种称之为TRY…CATCH的结构。通常我们是将T-SQL语句放入TRY块中（放在BEGIN TRY和END TRY关键字中间），而把错误处理代码放在CATCH块中（在BEGIN CATCH和END CATCH关键字中间）。

SQL Server中有一组函数来反馈有关错误的信息。ERROR_NUMBER函数返回一个整数表示错误号，ERROR_MESSAGE函数返回错误文本。我们可以对照sys.messages视图来查看所有的对照表。ERROR_SEVERITY和ERROR_STATE返回错误严重级别和状态号。ERROR_LINE返回错误行号。最后ERROR_PROCEDURE返回发生错误的存储过程或触发器的名称。

以下是封装好的一个收集错误的存储过程，这样所有的错误处理代码只需要在这里维护一次即可，只要在CATCH块中调用它，就行了。

CREATE PROCEDURE P_ErrorMessage
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	print 'ERROR_NUMBER:' + CAST(error_number() AS VARCHAR(10));
	PRINT 'ERROR_MESSAGE:' + error_message();
	print 'ERROR_SEVERITY:' + CAST(error_severity() AS VARCHAR(10));
	print 'ERROR_STATE:' + CAST(error_state() AS VARCHAR(10));
	print 'ERROR_LINE:' + CAST(error_line() AS VARCHAR(10));
	PRINT 'ERROR_PROC:' + COALESCE(error_procedure(),'not within proc')
END
GO