/*==========================================================
*描述： 存储过程知识点总结，以Northwind数据库的Employees表为例
===========================================================*/
--=========================1.out输出/输出的存储过程==================
create procedure usp_OutParameterSelect
@employeeID int,
@name
nvarchar(10) out, --**即作为输入，又作为输出**
@lastName nvarchar(20) out
--**out与output在这里通用**
as
begin
select
@name=FirstName --**重新赋值，作为输出**
,@lastName=LastName
from
dbo.Employees
where EmployeeID = @employeeID
and City =
@name --**输入参数查询**
end
GO
--===========================执行测试=======================
declare @employeeID int
,@name nvarchar(10)
,@lastName
nvarchar(20)
set @employeeID = 6
set @name = ‘London‘
execute usp_OutParameterSelect @employeeID,@name output,@lastName output
select @name as FirstName,@lastName as LastName
GO
--=========================2.异常处理的存储过程=================
create procedure usp_ExceptionHandling
as
begin
begin try
select 1/0 --**除数为零**
end try
begin catch
if @@ERROR <> 0
declare @ErrorMessage
nvarchar(4000)
,@ErrorSeverity int
,@ErrorState
int
select @ErrorMessage =
ERROR_MESSAGE() --**错误的信息**
,@ErrorSeverity =
ERROR_SEVERITY() --***错误的严重级别*
,@ErrorState =
ERROR_STATE() --**错误的状态**
/*抛出一个异常*/
raiserror
(@ErrorMessage,@ErrorSeverity,@ErrorState)
end catch
end
GO
--===========================执行测试==========================
execute usp_ExceptionHandling
--执行结果如下：
/*
Msg 50000, Level 16, State 1, Procedure
usp_ExceptionHandling, Line 17
Divide by zero error
encountered.
*/
GO
--=========================3.事物处理的存储过程===================
alter procedure usp_Transaction
as
begin
begin try
SET XACT_ABORT ON
/*
*当SET
XACT_ABORT为ON 时,如果Transact-SQL语句产生运行时错误,事务终止并回滚.
*为OFF
时,只回滚产生错误的语句.而事务继续处理.
*/
begin
transaction
--**这条跟新语句执行时会出现异常，FirstName被定义为Not Null**
update
dbo.Employees set FirstName = NULL
where EmployeeID = 1
update
dbo.Employees set FirstName = FirstName + ‘XXX‘
where City =
‘London‘
commit transaction
end try
begin
catch
if @@TRANCOUNT > 0
rollback
transaction --**事物回滚**
declare @ErrorMessage
nvarchar(4000)
,@ErrorSeverity int
,@ErrorState
int
select @ErrorMessage =
ERROR_MESSAGE() --**错误的信息**
,@ErrorSeverity =
ERROR_SEVERITY() --***错误的严重级别*
,@ErrorState =
ERROR_STATE() --**错误的状态**
/*抛出一个异常*/
raiserror
(@ErrorMessage,@ErrorSeverity,@ErrorState)
end catch
end
--===========================执行测试==============================
execute usp_Transaction
/*==============================================================
*********************************End*****************************************
*==============================================================*/