CREATE PROCEDURE PRO_INSERT @tblName Nvarchar(100), -- 表名 @strUpdate Nvarchar(4000), -- 更新内容 @strWhere varchar(1000) = '' -- 查询条件 (不要加 where) AS DECLARE @EXEC nvarchar(4000) --最终执行语句 DECLARE @parm nvarchar(4000) --参数变量 DECLARE @col nvarchar(4000) --列字段变量 DECLARE @value nvarchar(4000) --值字段变量 SET @parm='' SET @col='' SET @value='' ----设置参数变量 SELECT @parm=@parm+N'@'+CAST(name as Nvarchar(20))+' '+cast(TYPE_NAME(xtype) as Nvarchar(20))+ CASE WHEN xtype in(35,36,48,52,56,58,59,98,99,122,189) THEN ',' WHEN xtype in(106,108) THEN '('+cast(length as varchar)+','+cast(xscale as varchar)+'),' ELSE '('+cast(length as varchar)+'),' END FROM syscolumns WHERE object_name(id)=@tblName ----设置字段变量 SELECT @col=@col+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName ----设置值变量 SELECT @value =@value+N'@'+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblNameSET @parm=LEFT(@parm,LEN(@parm)-1) SET @col=LEFT(@col,LEN(@col)-1) SET @value=LEFT(@value,LEN(@value)-1) --设置最终执行语句 SET @EXEC='INSERT INTO '+@tblName+'('+@col+') VALUES ('+@value+')' EXEC('DECLARE '+@parm+' EXECUTE sp_executesql N'''+@EXEC+''',N'''+@parm+''''+','+@strUpdate) GO CREATE TABLE TESTTB ( ID INT, COL1 VARCHAR(50) ) DECLARE @return_value intEXEC @return_value = [dbo].[PRO_INSERT] @tblName = N'TESTTB', @strUpdate = N'1,''HELLO'''SELECT 'Return Value' = @return_value SELECT * FROM TESTTB DROP TABLE TESTTB --结果 ID COL1 ----------- -------------------------------------------------- 1 HELLO(1 行受影响) --此过程对长语句不支持,输入值用,分隔,对于varchar类型之类的值加'输入
经过验证: 1、长度表面是没有问题了,但这样解决长度的办法,使得sql语句出现错误了,本来最终的执行@strSQL类似这样:update OneTable set [字段1]=值1,[字段2]=值2,[字段n]=值n where ... 但是现在变成了update OneTable set '[字段1]=值1,[字段2]=值2,[字段n]=值n' where ... 也就是说现在把@strUpdate 整体作为一个字符串了,出现语法错误。 2、数据类型的问题,标准格式数据没有问题,但我问题中描述的最后一个情况时,就出问题。
晕啊,刚才试了一下,发现datetime类型的给加长度了。现在改成 ALTER PROCEDURE [dbo].[PRO_INSERT] @tblName Nvarchar(100), -- 表名 @strUpdate Ntext -- 更新内容 AS DECLARE @EXEC nvarchar(4000) --最终执行语句 DECLARE @parm nvarchar(4000) --参数变量 DECLARE @col nvarchar(4000) --列字段变量 DECLARE @value nvarchar(4000) --值字段变量 SET @parm='' SET @col='' SET @value='' ----设置参数变量 SELECT @parm=@parm+N'@'+CAST(name as Nvarchar(20))+' '+cast(TYPE_NAME(xtype) as Nvarchar(20))+ CASE WHEN xtype in(35,36,48,52,56,58,59,61,98,99,122,189) THEN ',' WHEN xtype in(106,108) THEN '('+cast(length as varchar)+','+cast(xscale as varchar)+'),' ELSE '('+cast(length as varchar)+'),' END FROM syscolumns WHERE object_name(id)=@tblName ----设置字段变量 SELECT @col=@col+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName ----设置值变量 SELECT @value =@value+N'@'+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblNameSET @parm=LEFT(@parm,LEN(@parm)-1) SET @col=LEFT(@col,LEN(@col)-1) SET @value=LEFT(@value,LEN(@value)-1) print @parm --设置最终执行语句 SET @EXEC='INSERT INTO '+@tblName+'('+@col+') VALUES ('+@value+')' EXEC('DECLARE '+@parm+' EXECUTE sp_executesql N'''+@EXEC+''',N'''+@parm+''''+','+@strUpdate) 执行 -- DECLARE @return_value intEXEC @return_value = [dbo].[PRO_INSERT] @tblName = N'dq_infor2', @strUpdate = N'2,''字符串二'',''字符串三'',''2007-03-31 00:00:00.000'''SELECT 'Return Value' = @return_value SELECT * FROM dq_infor2 where rtrim(id)='2' --结果 id unit address regdate --------------- ---------- ---------- ----------------------- 2 字符串二 字符串三 2007-03-31 00:00:00.000(1 行受影响) --另外,这个存储过程还不支持带自增列的。 再试一下吧,有时间再完善一下。
@tblName varchar(255), -- 表名
@strUpdate varchar(1000), -- 更新内容
@strWhere varchar(1000) = '' -- 查询条件 (不要加 where)
AS
BEGIN
declare @strSQL nvarchar(4000) -- 主语句
if @strWhere != ''
set @strSQL = 'update [' + @tblName + '] set @strUpdate where (' + @strWhere + ') '
else
set @strSQL = 'update [' + @tblName + '] set @strUpdate'
execute sp_executesql @strSQL, N'@strUpdate varchar(1000)', @strUpdate
END
如果单独处理text列, 则是可以做到的-- 类似下面这样可以解决长度问题CREATE PROCEDURE sp_update
@tblName varchar(255), -- 表名
@strUpdate text, -- 更新内容
@strWhere varchar(1000) = '' -- 查询条件 (不要加 where)
AS
BEGIN
declare @strSQL nvarchar(4000) -- 主语句
if @strWhere != ''
set @strSQL = 'update [' + @tblName + '] set @strUpdate where (' + @strWhere + ') '
else
set @strSQL = 'update [' + @tblName + '] set @strUpdate'
execute sp_executesql @strSQL, N'@strUpdate text', @strUpdate
END
这个思路我比较容易接受。
----------------------
zsl5305256(lying) :要是sqlserver2005就可以用varchar(max)。
我现在用的是sqlserver2000,另外sqlserver2005用varchar(max),最长可以多大呢?
----------------------
zjcxc(邹建):解决了长度问题,但会出现数据类型的问题,建议放在程序中处理。
长度和数据类型问题我等会验证一下看看;“通用的操作应该考虑放在程序中, 处理会容易很多",我这里主要是想把所有的数据操作都通过存储过程来实现而不是sql语句,这样存储过程必然很多,所以想把存储过程尽量写得通用一点,并不想让程序来过多处理构建sql语句的工作,那样的话,一旦数据库结构变动,程序里所有构建sql语句的地方都要进行维护。
----------------------
谢谢各位的方案,还有别的方案吗?
@tblName Nvarchar(100), -- 表名
@strUpdate Nvarchar(4000), -- 更新内容
@strWhere varchar(1000) = '' -- 查询条件 (不要加 where)
AS DECLARE @EXEC nvarchar(4000) --最终执行语句
DECLARE @parm nvarchar(4000) --参数变量
DECLARE @col nvarchar(4000) --列字段变量
DECLARE @value nvarchar(4000) --值字段变量
SET @parm=''
SET @col=''
SET @value=''
----设置参数变量
SELECT @parm=@parm+N'@'+CAST(name as Nvarchar(20))+' '+cast(TYPE_NAME(xtype) as Nvarchar(20))+
CASE
WHEN
xtype in(35,36,48,52,56,58,59,98,99,122,189)
THEN
','
WHEN
xtype in(106,108)
THEN
'('+cast(length as varchar)+','+cast(xscale as varchar)+'),'
ELSE
'('+cast(length as varchar)+'),'
END
FROM
syscolumns WHERE object_name(id)=@tblName
----设置字段变量
SELECT @col=@col+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName
----设置值变量
SELECT @value =@value+N'@'+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblNameSET @parm=LEFT(@parm,LEN(@parm)-1)
SET @col=LEFT(@col,LEN(@col)-1)
SET @value=LEFT(@value,LEN(@value)-1)
--设置最终执行语句
SET @EXEC='INSERT INTO '+@tblName+'('+@col+') VALUES ('+@value+')'
EXEC('DECLARE '+@parm+' EXECUTE sp_executesql N'''+@EXEC+''',N'''+@parm+''''+','+@strUpdate)
GO
CREATE TABLE TESTTB
(
ID INT,
COL1 VARCHAR(50)
)
DECLARE @return_value intEXEC @return_value = [dbo].[PRO_INSERT]
@tblName = N'TESTTB',
@strUpdate = N'1,''HELLO'''SELECT 'Return Value' = @return_value
SELECT * FROM TESTTB
DROP TABLE TESTTB
--结果
ID COL1
----------- --------------------------------------------------
1 HELLO(1 行受影响)
--此过程对长语句不支持,输入值用,分隔,对于varchar类型之类的值加'输入
1、长度表面是没有问题了,但这样解决长度的办法,使得sql语句出现错误了,本来最终的执行@strSQL类似这样:update OneTable set [字段1]=值1,[字段2]=值2,[字段n]=值n where ...
但是现在变成了update OneTable set '[字段1]=值1,[字段2]=值2,[字段n]=值n' where ...
也就是说现在把@strUpdate 整体作为一个字符串了,出现语法错误。
2、数据类型的问题,标准格式数据没有问题,但我问题中描述的最后一个情况时,就出问题。
hrb2008() :不能解决长度问题。
怎么做到通用呢?单独针对text列再写一个,暂时不考虑。
@strUpdate 只要够容纳下 'col1=@col1,[email protected]=@coln'就没有问题。
楼主的@strUpdate内容也是varchar(1000)
另外,如一表中有一百字段。每字段长度为二十的话,楼主的也会出问题,参数
@strUpdate该为'col1=@col1,[email protected]=@coln'形式20×2×100>=4000
ALTER PROCEDURE [dbo].[PRO_INSERT]
@tblName Nvarchar(100), -- 表名
@strUpdate Ntext -- 更新内容
AS DECLARE @EXEC nvarchar(4000) --最终执行语句
DECLARE @parm nvarchar(4000) --参数变量
DECLARE @col nvarchar(4000) --列字段变量
DECLARE @value nvarchar(4000) --值字段变量
SET @parm=''
SET @col=''
SET @value=''
----设置参数变量
SELECT @parm=@parm+N'@'+CAST(name as Nvarchar(20))+' '+cast(TYPE_NAME(xtype) as Nvarchar(20))+
CASE
WHEN
xtype in(35,36,48,52,56,58,59,61,98,99,122,189)
THEN
','
WHEN
xtype in(106,108)
THEN
'('+cast(length as varchar)+','+cast(xscale as varchar)+'),'
ELSE
'('+cast(length as varchar)+'),'
END
FROM
syscolumns WHERE object_name(id)=@tblName
----设置字段变量
SELECT @col=@col+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblName
----设置值变量
SELECT @value =@value+N'@'+CAST(name as Nvarchar(20))+N',' FROM syscolumns WHERE object_name(id)=@tblNameSET @parm=LEFT(@parm,LEN(@parm)-1)
SET @col=LEFT(@col,LEN(@col)-1)
SET @value=LEFT(@value,LEN(@value)-1)
print @parm
--设置最终执行语句
SET @EXEC='INSERT INTO '+@tblName+'('+@col+') VALUES ('+@value+')'
EXEC('DECLARE '+@parm+' EXECUTE sp_executesql N'''+@EXEC+''',N'''+@parm+''''+','+@strUpdate)
执行
--
DECLARE @return_value intEXEC @return_value = [dbo].[PRO_INSERT]
@tblName = N'dq_infor2',
@strUpdate = N'2,''字符串二'',''字符串三'',''2007-03-31 00:00:00.000'''SELECT 'Return Value' = @return_value
SELECT * FROM dq_infor2 where rtrim(id)='2'
--结果
id unit address regdate
--------------- ---------- ---------- -----------------------
2 字符串二 字符串三 2007-03-31 00:00:00.000(1 行受影响)
--另外,这个存储过程还不支持带自增列的。
再试一下吧,有时间再完善一下。
暂时还是根据每个具体的表专门写一个更新添加了!
http://blog.csdn.net/zlp321002/archive/2006/08/12/1055089.aspx