if exists (select * from dbo.sysobjects where id = object_id(N'[m_buchong]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [m_buchong]
GO

CREATE TABLE [m_buchong] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [tablename] [varchar] (50)  NOT NULL ,--其他表的表名
    [colname] [varchar] (50)  NOT NULL ,--其他表的字段名
    [userid] [int] NULL DEFAULT (0),--完善信息的用户ID
    [recordid] [int] NOT NULL ,--其他表的ID
    [describe] [ntext] NULL ,--用户完善的信息，这个信息在其他表中可以是任意数据类型
    [ptime] [datetime] NULL DEFAULT (getdate()),--时间
    CONSTRAINT [PK_m_buchong] PRIMARY KEY  CLUSTERED 
    (
        [id]
    )  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into m_buchong(tablename,colname,userid,recordid,describe,ptime) 
select 'a','cname',1,1,'0001',getdate() union all--varchar型
select 'a','ename',1,1,'0002',getdate() union all
select 'a','sex',1,1,'1',getdate() union all    --bit型
select 'b','age',1,2,'13',getdate()        --int型

select * from m_buchong

--代码有点问题, 重新写一下
declare @s varchar(200)
set @s = '1, 2, 3'

if @s = '' return
declare @id int, @cur int, @Next int, @t int, @sql varchar(2000)
set @cur = 1
set @next = charindex(',', @s)

while @cur <> 0
begin 

    if @cur <>1 set @cur = @cur + 1
    if @next = 0 
    begin
        set @id = substring(@s, @cur, Len(@s) - @cur + 1)
    end
    else
    begin
        set @id = substring(@s, @cur, @next - @cur)
    end
    select @sql = 'update ' + tablename + ' set ' + colname + ' = ''' + cast(describe as nvarchar) + '''' + ' where id = ' + cast(recordid as varchar)
    from m_buchong 
    where id = @id

    print @cur
    print @next 
    --exec(@sql)
    print @sql
    
    set @t = @next 
    set @next = charindex(',', @s, @next + 1)
    set @cur = @t 
end

------解决方案--------------------

SQL code


declare @sql varchar(max)
with cte as
(
    --set 语句拼凑，where 语句拼凑
    select 
        tablename,
        colname+'='''+convert(varchar(1000),describe)+'''' as update_statement,
        ' where id='+convert(varchar(38),recordid) as condition 
    from m_buchong
),
cte2 as
(
    --把set语句多行合并一行
    select tablename,
        update_statement=stuff
        (
            (
                select ','+update_statement  from cte a where a.tablename=cte.tablename for xml path('')
            ),
        1,1,''),
        condition
    from cte
    group by tablename,condition
)
select @sql=isnull(@sql,'')+char(10)+'update '+tablename+' set '+update_statement+' '+condition from cte2
print @sql


--update a set cname='0001',ename='0002',sex='1'  where id=1
--update b set age='13'  where id=2