存储过程知识总结【二】

9/1/2015来源:SQL技巧人气:1490

存储过程知识总结【二】

/*==========================================================*描述:存储过程知识点总结,以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.事物处理的存储过程===================

createprocedure 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******************************************==============================================================*/