/*==========================================================*描述：存储过程知识点总结，以Northwind数据库的Employees表为例
===========================================================*/
--=========================1.out输出/输出的存储过程==================
create procedure usp_OutParameterSelect @employeeID int, @name nvarchar(10) out,--**即作为输入，又作为输出** @lastName nvarchar(20) out --**out与output在这里通用**asbegin 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 = 6set @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 catchend
GO
--===========================执行测试==========================
execute usp_ExceptionHandling
--执行结果如下：/*Msg 50000, Level 16, State 1, Procedure usp_ExceptionHandling, Line 17Divide by zero error encountered.*/GO--=========================3.事物处理的存储过程===================
alter procedure usp_Transaction
asbegin 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******************************************==============================================================*/