sqlserver 存储过程 修改

CREATE PROCEDURE [dbo].[UpdateMessage]
@strTable varchar(50),  --要修改的表
@strColumn varchar(500),--要修改的列名（如果有多个，用英文逗号分隔）
@strValue varchar(500), --新的值（用英文逗号分隔，如果是字符串，需加单引号）
@strWhere varchar(500)  --where条件，不加wehere，直接 列名=值，如果值是字符串，需加单引号
as
--变量
declare @sqlString nvarchar(4000);  --完整的update语句
declare @whereString varchar(500);  --where条件
declare @tempString varchar(5000);  --update语句中间的赋值语句
declare @curr_Column int;           --列名字符串的当前位置
declare @curr_Value int;            --值字符串的当前位置
declare @prev int;                  --光标位置
declare @prev2 int;                 --光标位置
declare @num int;                   --字段个数
--变量赋初值
set @sqlString = 'update '+@strTable+' set ';
set @whereString = ' where '+@strWhere;
set @tempString='';
set @curr_Column=1;
set @prev=1;
set @prev2=1;
set @num=(len(@strValue)-len(replace(@strValue,',','')))+1;
--开始循环处理
while ( @num > 0)
begin
                                                     --print @prev2        -- 1   10  15
                                                     --print @prev         -- 1   9   18
    set @curr_Column=charindex(',',@strColumn,@prev);--print @curr_Column  -- 8   17   0
    set @curr_Value= charindex(',',@strValue,@prev2);--print @curr_Value   -- 9   14   0
    if @num>1
        set @tempString = substring(@strColumn,@prev,@curr_Column-@prev)+'='+substring(@strValue,@prev2,@curr_Value-@prev2)+',' +@tempString;
    else--最后一个
        begin
            set @tempString =@tempString + substring(@strColumn,@prev,len(@strColumn)-@prev+1)+'='+substring(@strValue,@prev2,len(@strValue)-@prev2+1);
            break;
        end
    set @num=@num-1;
    set @prev=@curr_Column+1;
    set @prev2=@curr_Value+1;
end
set @sqlString = @sqlString+@tempString+@whereString;
exec(@sqlString);

--execute [dbo].[UpdateMessage] 'base._Member',"AcctNbr","'999999'",'ID=554585'
--execute [dbo].[UpdateMessage] 'base._Member','AcctNbr,AcctName,AcctPswd1',"'999999','zz','852852'",'ID=554585'

 

CREATE PROCEDURE [dbo].[UpdateMessage]
@strTable varchar(50),  --要修改的表
@strColumn varchar(500),--要修改的列名（如果有多个，用英文逗号分隔）
@strValue varchar(500), --新的值（用英文逗号分隔，如果是字符串，需加单引号）
@strWhere varchar(500),  --where条件，不加wehere，直接 列名=值，如果值是字符串，需加单引号
@only_field varchar(20)=null,  --唯一性字段(列名)
@only_value varchar(20)=null,  --唯一性字段值
@msg nvarchar(50)=null,        --错误消息
@not_self varchar(50)=null     --排除自己这行
as
BEGIN
--变量
declare @sqlString nvarchar(4000);  --完整的update语句
declare @whereString varchar(500);  --where条件
declare @tempString varchar(5000);  --update语句中间的赋值语句
declare @curr_Column int;           --列名字符串的当前位置
declare @curr_Value int;            --值字符串的当前位置
declare @prev int;                  --光标位置
declare @prev2 int;                 --光标位置
declare @num int;                   --字段个数
--变量赋初值
set @sqlString = 'update '+@strTable+' set ';
set @whereString = ' where '+@strWhere;
set @tempString='';
set @curr_Column=1;
set @prev=1;
set @prev2=1;
set @num=(len(@strValue)-len(replace(@strValue,',','')))+1;
IF @only_field is not null
    BEGIN
        declare @p int;                    --查询唯一性结果
        declare @sql nvarchar(1000);       --拼接查询sql字串
        set @sql='SELECT @p=count(1) FROM ' +@strTable+' WHERE ' +@only_field+'='+@only_value+' AND '+@not_self;
        exec sp_executesql @sql,N'@p AS int OUTPUT',@p OUTPUT
    
        if @p > 0
            begin
                raiserror(@msg ,16,1)
                return
            end
        else
            begin
                --开始循环处理
                while ( @num > 0)
                begin
                                                                     --print @prev2        -- 1   10  15
                                                                     --print @prev         -- 1   9   18
                    set @curr_Column=charindex(',',@strColumn,@prev);--print @curr_Column  -- 8   17   0
                    set @curr_Value= charindex(',',@strValue,@prev2);--print @curr_Value   -- 9   14   0
                    if @num>1
                        set @tempString = substring(@strColumn,@prev,@curr_Column-@prev)+'='+substring(@strValue,@prev2,@curr_Value-@prev2)+',' +@tempString;
                    else--最后一个
                        begin
                            set @tempString =@tempString + substring(@strColumn,@prev,len(@strColumn)-@prev+1)+'='+substring(@strValue,@prev2,len(@strValue)-@prev2+1);
                            break;
                        end
                    set @num=@num-1;
                    set @prev=@curr_Column+1;
                    set @prev2=@curr_Value+1;
                end
                set @sqlString = @sqlString+@tempString+@whereString;
                exec(@sqlString);
          end
    END
ELSE
    BEGIN
        --开始循环处理
        while ( @num > 0)
        begin
                                                             --print @prev2        -- 1   10  15
                                                             --print @prev         -- 1   9   18
            set @curr_Column=charindex(',',@strColumn,@prev);--print @curr_Column  -- 8   17   0
            set @curr_Value= charindex(',',@strValue,@prev2);--print @curr_Value   -- 9   14   0
            if @num>1
                set @tempString = substring(@strColumn,@prev,@curr_Column-@prev)+'='+substring(@strValue,@prev2,@curr_Value-@prev2)+',' +@tempString;
            else--最后一个
                begin
                    set @tempString =@tempString + substring(@strColumn,@prev,len(@strColumn)-@prev+1)+'='+substring(@strValue,@prev2,len(@strValue)-@prev2+1);
                    break;
                end
            set @num=@num-1;
            set @prev=@curr_Column+1;
            set @prev2=@curr_Value+1;
        end
        set @sqlString = @sqlString+@tempString+@whereString;
        exec(@sqlString);
    END
END


--execute [dbo].[UpdateMessage] 'base._Member','AcctNbr,AcctName,AcctPswd1',"'2999999','zz','852852'",'ID=554710',NULL,NULL,NULL,NULL
--execute [dbo].[UpdateMessage] 'base._Member','AcctNbr,AcctName,AcctPswd1',"'12999999','zz','852852'",'ID=554711','AcctNbr','12999999',N'会员卡号已存在,不能重复添加!','ID !=554711'

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 邹柯
ALTER PROCEDURE CollegeUpdate
(
     @ceid varchar(20)=null
    ,@username nvarchar(10)=null
    ,@cardid varchar(18)=null
    ,@age int=null
    ,@sex NChar(2)
    ,@image_path VarChar(50)=null
    ,@job_base_id int=null
    ,@level_base_id int=null
    ,@certificateid VarChar(15)=null
    ,@certificate_path VarChar(50)=null
    ,@BeginTime DateTime=null
    ,@EndTime DateTime=null
)
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT     ON;
    SET XACT_ABORT  ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRY
        BEGIN    TRAN;
        --先查询修改的用户id
        declare @userid int=null;
        select @userid=TutorId from Certificate where Id=@ceid;

        -- Update Certificate
        update Certificate set
               TutorId=@userid, 
               JobId=@job_base_id, 
               LevelId=@level_base_id, 
               BeginTime=@BeginTime,    
               EndTime=@EndTime,       
               Id=@certificateid, 
               ImageUrl=@certificate_path
        where Id=@ceid
        
        -- Update Tutor    
        update Tutor set
               Name=@username,  
               IdentityCard=@cardid,  
               Age=@age,  
               Gender=@sex, 
               Photo=@image_path
        where Id=@userid        

        COMMIT    TRAN;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN; throw;
    END CATCH
END

/*
USE [GmkCollege]
GO

DECLARE    @return_value int

EXEC    @return_value = CollegeUpdate
        @ceid = 1,
        @username = '测试123456',
        @cardid = '测试123456',
        @age = 98,
        @sex = '男',
        @image_path = '测试123456',
        @job_base_id = 2,
        @level_base_id = 2,
        @certificateid = '测试123456',
        @certificate_path = '测试123456',
        @BeginTime='2016-06-06',
        @EndTime='2017-01-01',

SELECT    'Return Value' = @return_value

GO
*/