如果寫通用的增加,更新存儲過程?

Question

可以将表名和字段名作为参数传入，然后利用EXEC执行就可以了。这样你写一个存储过程就可以了。

Answer 1

create  procedure prCategoriesUpdate
(
      @varcategoryid                          varchar(4),
     @varcategoryname                    varchar(16),
     @varcategorydescription           varchar(40),
     @vareditman                             varchar(10),
     @varedittime                              varchar(23),
@表名 varchar(1000),
@列名1 varchar(100),@列名2 varchar(100),@列名3 varchar(100),
@列名4 varchar(100),@列名5 varchar(100)
)
as 
    declare @interrorcode int,
                @introwcount  int,
                 @errormessage varchar(255)
   select @interrorcode=@@error
   begin transaction
     if  @interrorcode=0 
          begin
              exec('update '+@表名+' set '+@列名1+'='''+@varcategoryname+''','+@列名2+'='''+@varcategorydescription+''', '+ '='''+@vareditman+','+@列名3+'=getdate()  where '+@列名4+'='''+@varcategoryid+'''  and  convert(varchar(23),'+@列名5+',121)='''+@varedittime+''''
      select @interrorcode=@@error,@introwcount=@@rowcount
          end
  if @interrorcode !=0
       begin
             raiserror 50000  '在Categories表中數據修改失敗'
              rollback transaction
             return -100
       end
 if  @introwcount<>1
         begin
               if @introwcount=0
                   begin 
                         if exists(select * from categories where categoryid=@varcategoryid)
                                begin 
                                        select @errormessage='當前記錄已被其他用戶作了修改'
                                 end
                          else
                                 begin
                                        select @errormessage='當前記錄不存在,可能被其他用戶刪除'
                                  end
                      end
                else
                      begin
                          select @errormessage='更新了太多行,更新不成功'
                      end
                     raiserror  50000 @errormessage
                     rollback  transaction
                     return -100
            end
      commit transaction
   return