sql server 通用修改表数据存储过程

ALTER PROC [dbo].[UpdateTableData]
@TableName VARCHAR(255),
@SearchField VARCHAR(255),
@SearchValue VARCHAR(255),
@WhereField VARCHAR(255),
@WhereValue VARCHAR(255)
AS 
BEGIN
DECLARE @sql VARCHAR(255)
DECLARE @xtype INT=0
SET @sql ='UPDATE '+@TableName;
  --获取SqlServer中表结构 
SELECT @xtype=syscolumns.xtype
FROM syscolumns, systypes 
WHERE syscolumns.xusertype = systypes.xusertype 
AND syscolumns.id = object_id(@TableName)and  syscolumns.name=@SearchField
IF(@xtype=NULL)
RAISERROR ('查询字段不存在', 16, 1)
SET @sql=@sql+' SET '+@SearchField+'=';
IF(@xtype=48 OR @xtype=52 OR @xtype=56 OR @xtype=62OR @xtype=127)
BEGIN
SET @sql=@sql+@SearchValue;
END
ELSE IF(@xtype=35OR @xtype=99 OR @xtype=167 OR @xtype=175 OR @xtype=231 OR @xtype=239)
BEGIN
SET @sql=@sql+''''+@SearchValue+'''';
END
ELSE
RAISERROR ('数据类型错误', 16, 1)
SET @xtype=0
SELECT @xtype=syscolumns.xtype
FROM syscolumns, systypes 
WHERE syscolumns.xusertype = systypes.xusertype 
AND syscolumns.id = object_id(@TableName)and  syscolumns.name=@WhereField
IF(@xtype=0)
BEGIN
RAISERROR ('修改字段不存在', 16, 1)
END
SET @sql=@sql+' WHERE '+@WhereField+'='
IF(@xtype=48 OR @xtype=52 OR @xtype=56 OR @xtype=62OR @xtype=127)
BEGIN
SET @sql=@sql+@WhereValue;
END
ELSE IF(@xtype=35OR @xtype=99 OR @xtype=167 OR @xtype=175 OR @xtype=231 OR @xtype=239)
BEGIN
SET @sql=@sql+''''+@WhereValue+'''';
END
ELSE
RAISERROR ('数据类型错误', 16, 1)
EXEC (@sql)
END 

 exec UpdateTableData '表名','修改字段','修改值','条件字段','条件值' 

转载于:https://www.cnblogs.com/RambleLife/p/9830814.html