关键词：sql server存储过程，sql server表值函数
事务控制，自定义错误
1.存储过程
精华总结：
通过对比@@ERROR一般和if判断结合使用，@@TRANCOUNT和try catch块结合使用，xact_abort为on可以单独使用
Xact_abort为off时，如果存在事务嵌套可以和保存点结合使用
1.1 SET XACT_ABORT ON是设置事务回滚的！
当为ON时，如果你存储中的某个地方出了问题，整个事务中的语句都会回滚；且不会再执行该事务内的所有语句了；
为OFF时，只回滚错误的地方。事务内剩余的语句都会执行；
举例：一个过程里有10行dml，执行到第5行出错。
【1】如果为on，则直接回滚整个事务，并且报错
【2】如果为off，则只会回滚出错的第5行，其余1-4,6-10可以正常执行并且提交保存；
在存储过程中使用事务时，如果没有try…catch语句，那么当set xact_abort 为on时，如果有错误发生，在批处理语句结束后，系统会自动回滚所有的sql操作。
当set xact_abort 为off时，如果有错误发生，在批处理语句结束后，系统会执行所有没有发生错误的语句，发生错误的语句将不会被执行。
在存储过程中使用事务时，如果存在try…catch语句块，那么当捕获到错误时，需要在catch语句块中手动进行Rollback操作，否则系统会给客户端传递一条错误信息。
如果在存储过程开始处将set xact_abort on，那么当有错误发生时，系统会将当前事务置为不可提交状态，即会将xact_state()置为-1。
此时只可以对事务进行Rollback操作，不可进行提交(commit)操作，那么我们在catch语句块中就可以根据xact_state()的值来判断是否有事务处于不可提交状态，如果有则可以进行rollback操作了。
如果在存储过程开始处将set xact_abort off，那么当有错误发生时，系统不会讲xact_state()置为-1，那么我们在catch块中就不可以根据该函数值来判断是否需要进行 rollback了。
但是我们可以根据@@Trancount全局变量来判断，如果在catch块中判断出@@Trancount数值大于0，代表还有未提交的事务。
既然进入catch语句块了，那么还存在未提交的事务，该事务应该是需要rollback的，但是这种方法在某些情况下可能判断的不准确。
推荐的方法还是将set xact_abort on，然后在catch中判断xact_state()的值来判断是否需要Rollback操作。
（1）基本形式
1. create proc | procedure pro_name
2. output], --加上output就代表是地址传递（即会从调用接收，也会输出给调用）
3. output],
4. ....
5. ]
6. as
7. SQL_statements（2）实例演示
--判断过程是否存在
IF OBJECT_ID('getArea') is not null
BEGIN
drop procedure getArea
END
GO
--创建存储过程
create procedure getArea
@shopId nvarchar(64) ,
@area nvarchar(32) output
as
begin
print @area
if (@shopId is not null and @shopId <> '')
begin
select @area= 2
end
else
begin
set @area = '空'
end
end
GO
--声明变量
declare @a varchar(20);
set @a='1'
--执行存储过程
execute getArea '2010315', @a output;
--规范形式，形参实参一一对应execute getArea @shopId='2010315',
@area=@a output;
--输出验证结果
print @a ;
GO在存储过程中，使用自定义类型（表类型，2008及以上才支持）,作为存储过程的参数的应用
/* 创建表类型.*/
create type Type_test as table(keyId int,info varchar(30));
go
/* 创建一个存储过程以表值参数作为输入 */
create Procedure pr_testTable(@tmp Type_test readonly)
as
select *,getdate() from @tmp;
go
/* 声明表值参数变量.*/
declare @tmp as Type_test;
/*对自定义类型进行数据插入*/
insert into @tmp(keyId,info)
values(10,'test'),(20,'maomao365.com'),(30,'猫猫小屋')
/* 存储过程运行表参数*/
exec pr_testTable @tmp;
go
drop proc pr_testTable
go
drop type Type_test
go
2.自定义函数
【2.1】常规自定义函数
--创建
--（1）直接返回表类型
IF OBJECT_ID('testFunTable') is not null
drop function testFunTable
go
create function testFunTable(@count int)
returns @temptale table (intcount int ,intcountAdd int)
as
begin
insert into @temptale values(@count,@count+1)
return
end
GO
--调用
SELECT * FROM dbo.testFunTable(20)
GO
--（2）直接返回数字
IF OBJECT_ID('testFunTable') is not null
drop function testFunTable
go
create function testFunTable(@count int)
returns int
as
begin
return @count+1
end
GO
--调用
SELECT dbo.testFunTable(20)【2.2】表值函数
有些情况可能用下表值函数，表值函数主要用于数据计算出来返回结果集，可以带参数（和视图的一个大的区别），如果函数中没有过多的逻辑处理，如变量的定义，判断等，
表值函数返回结果集可以简单向下面这么写：
CREATE FUNCTION Fun_GetReportNews(@type varchar(10))
RETURNS TABLE
AS
RETURN
(
SELECT TPR_ID,TPR_Title,TPR_Date FROM TP_ReportNews WHERE TPR_Type = @type
)
调用的时候就 SELECT XX FROM Fun_GetReprotNews('xx')
如果函数中要定义变量，进行判断计算处理什么的，写法有点不一样了，要定义表变量才行，表值函数里是不允许创建临时表的，只能是表变量。
举个简单的写法样式，如下：
CREATE FUNCTION FUN_GetInfoList(@type varchar(10))
RETURNS @Table TABLE(TPR_ID int,TPR_Title nvarchar(100),TPR_PubDate datetime)
AS
BEGIN
DECLARE @a varchar(10)
SELECT @a = xx FROM XX WHERE xx = @type
INSERT @Table SELECT XX,XX,XX FROM TableName WHERE XX = @a --表变量里定义的列数和取值列数要一致
RETURN
END
3.存储过程的特性
（1）SP中创建临时表，SP结束后临时表被销毁
（2）SP不能返回表变量，但限制少，可以执行对数据库表的操作，可以返回数据集
（3）限制少，可以执行对数据库表的操作，可以返回数据集
（4）可以return一个标量值，也可以省略return
存储过程一般用在实现复杂的功能，数据操纵方面4.函数特性
（1）函数中不能用某些DML（select,update，delete）语句
（2）函数中不能用print与output
（3）函数中不能使用临时表
（4）可以返回表变量
（5）内部操作不影响外部
自定义函数一般用在复用度高，功能简单单一，争对性强的地方。
5、设置存储过程在实例启动时启动
--将该存储过程设置为SQL Server服务启动时自动启动
EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO
