我设计了一个表（m_buchong），表中保存用户完善其他表时的内容，这些内容在后台审核后更新对应表，其他表至少ID有内容：
表A结构如下：
- SQL code
if exists (select * from dbo.sysobjects where id = object_id(N'[m_buchong]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [m_buchong]GOCREATE 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]GOinsert 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 allselect 'a','sex',1,1,'1',getdate() union all --bit型select 'b','age',1,2,'13',getdate() --int型select * from m_buchong
现在的问题是，如何写这个更新的存储过程，参数是m_buchong的ID，比如 p_name '1,2,3,4' 这样。
------解决方案--------------------
- SQL code
--代码有点问题, 重新写一下declare @s varchar(200)set @s = '1, 2, 3'if @s = '' returndeclare @id int, @cur int, @Next int, @t int, @sql varchar(2000)set @cur = 1set @next = charindex(',', @s)while @cur <> 0begin 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 cte2print @sql--update a set cname='0001',ename='0002',sex='1' where id=1--update b set age='13' where id=2