关于存储过程事务，返回值，变量声明以及执行方法

      Sql存储过程是一个非常有用的东西，我们常需要在执行完一个储存过程后知道执行的结果，所以我们可以在存储过程中定义变量来告诉我们返回状态和执行信息

      set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,>
-- Create date: <Create Date,,2010-5-22>
-- Description: <Description,,插入用户>
-- =============================================
ALTER PROCEDURE [dbo].[Insert_User]
 -- Add the parameters for the stored procedure here
 @Account  VARCHAR(100)='',--名称
 @NickName VARCHAR(100)='',--昵称
 @UID BIGINT =0,--UID
 @PassWord VARCHAR(32)='',--密码
 @EMAIL VARCHAR(50)='',--邮箱
 @Status TINYINT =0,--类别
 @UseStatus TINYINT =0,--状态
 @RoleID SMALLINT =0--角色
AS
BEGIN TRANSACTION roll_back
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 SAVE TRANSACTION roll_back
 declare @return_value int
 declare @return_message varchar(50)
 IF NOT EXISTS (SELECT Account FROM dbo.UserBase WHERE Account=@Account OR NickName=@NickName)
 BEGIN  
  INSERT INTO dbo.UserBase(Account,PassWord,NickName,UID)
    VALUES(@Account,@PassWord,@NickName,@UID)
  INSERT INTO dbo.UserLimite(UID,Status,RoleID,UseStatus)
    VALUES(@UID,0,@RoleID,0)
  INSERT INTO dbo.UserExpand(UID,NAME,EMAIL)
    VALUES(@UID,@NickName,@EMAIL)
  set @return_value=1
  set @return_message='操作成功'
  IF(@@ERROR<>0)
  BEGIN
   set @return_value=0
   set @return_message='操作失败'
   ROLLBACK TRANSACTION roll_back
  END
  IF(@@ERROR>0)
  BEGIN
   set @return_value=0
   set @return_message='操作失败'
   ROLLBACK TRANSACTION roll_back
  END
 END
 ELSE
 BEGIN
  set @return_value=0
  set @return_message='已有此用户名称或昵称'
  RAISERROR ('已有此用户名称或昵称!', -- Message text.
      16, -- Severity.
      1 -- State.
      );
 END
 SELECT @return_value IsSuccess,@return_message Message
 commit TRANSACTION

 

如果执行储存过程，就是下面这行代码

EXEC dbo.Insert_User 'AA1','AA1','125','0000','174510355@qq.com','0','0','0'

结果如下图