sql添加修改删除通用存储过程

USE [CoastalDB]
GO
/****** 对象:  StoredProcedure [dbo].[Pro_EditUserInfo]    脚本日期: 08/07/2008 15:12:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



/*-----------------------------------------------------
--   名称：Pro_EditUser
--   功能说明：新增/修改用户信息
--   输入参数:
            @Action              "Insert" 为新增；"Update" 为更新；"Delete" 为删除
            @UserName            用户帐号，删除时为以逗号分隔的多个'a','b'
            @ActualName            姓名, 
            @Sex                 性别, 
            @Age                年龄, 
            @Marriage           婚姻状况，
            @Company            公司,  
            @Department            部门,
            @Position           职位,
            @CareerTime            本公司服务时间,
            @Password            密码,
            @UserRight            用户权限,
            @Active                是否激活,
            @Note                 备注,
            @FullName            中文,  

--   输出参数: 
--            @errMsg        出错信息
--   注意事项:
--   原设计者: 薛云枫
--   设计日期:2007/06/04
--   ------------------------ 变更纪录明细-------------
--   变更日期    变更者        变 更 原 因

-------------------------------------------------------*/
ALTER procedure [dbo].[Pro_EditUserInfo]
(    @Action                  nvarchar(20),
    @UserName                nvarchar(50),
    @ActualName                nvarchar(100),  
    @Sex                      nvarchar(50) ,
    @Age                    nvarchar(100),  
    @Marriage                nvarchar(50),
    @Education                nvarchar(100),  
    @Company                nvarchar(500),  
    @CompanyProperty        nvarchar(500), 
    @CompanyProfession      nvarchar(500), 
    @EmployeeNum            nvarchar(500), 
    @Department                nvarchar(500),
    @Position               nvarchar(200),
    @ManageNum              nvarchar(500), 
    @WorkTime               nvarchar(500), 
    @CareerTime                nvarchar(200),
    @Password                nvarchar(50),
    @UserRight                nvarchar(50),
    @Active                    nvarchar(50),
    @Note                     nvarchar(200),
    @errMsg                 varchar(100)  output
)
as
begin
   begin tran
    if(@Action='Insert')
      begin
        insert into dbo.UserInfo(
            UserName, ActualName, Sex,Age,  Marriage,Education,Company,CompanyProperty, CompanyProfession,EmployeeNum, Department,
            Position,ManageNum, WorkTime, CareerTime,Password,UserRight,Active,RegistTime,Note )
        values (
            @UserName,@ActualName, @Sex,@Age, @Marriage,@Education,  @Company,@CompanyProperty ,@CompanyProfession , @EmployeeNum,  @Department,
            @Position,@ManageNum, @WorkTime,@CareerTime,@Password,@UserRight,@Active,getdate(),@Note );

      if(@@error<>0)
        begin
        set @errMsg = '数据库执行出错!';  
        --set @errMsg = 'Database Execution Error Occured !';  
        goto  Error_Handle  ; 
        end
     end
    else if(@Action='Update')
      begin
        update     dbo.UserInfo
          set
                ActualName=@ActualName, 
                Sex=@Sex, 
                Age=@Age, 
                Marriage=@Marriage ,
                Education=@Education ,
                Company=@Company ,
                CompanyProperty = @CompanyProperty,
                CompanyProfession = @CompanyProfession ,
                EmployeeNum = @EmployeeNum ,
                Department=@Department ,
                Position=@Position ,
                ManageNum = @ManageNum,
                CareerTime =@CareerTime ,
                Password =@Password ,
                WorkTime = @WorkTime,
                UserRight=@UserRight ,
                Active=@Active ,
                Note=@Note 
           where  lower(UserName)=lower(@UserName);
      if(@@error<>0)
        begin
        set @errMsg = '数据库执行出错!';  
        --set @errMsg = 'Database Execution Error Occured !';  
        goto  Error_Handle  ; 
        end
     end
    else if(@Action='Delete')
      begin
        if(right(@UserName,1)=',')
          begin
            set @UserName = @UserName+''''''; --随意补齐一个负数即可
          end
        declare @sql varchar(8000);
        set @sql='delete from dbo.UserInfo
                        where lower(UserName) in('+lower(@UserName)+')';
        execute (@sql);
      if(@@error<>0)
        begin
        set @errMsg = '数据库执行出错!';  
        --set @errMsg = 'Database Execution Error Occured !';  
        goto  Error_Handle  ; 
        end
      end

   commit tran ;
   return;      
Error_Handle:
   rollback tran; 
   return;
end