用這種方式處理:create proc p_update @condition varchar(1000) as declare @sql varchar(1000) set @sql=' update aaa .....' +@condition --你的條件 exec(@sql) ... go
CREATE TABLE [ai] ( [id] [int] NULL , [i] [int] NULL ) GOdeclare @sql nvarchar(4000) select @sql='update ai set id=11 where i=1' exec (@sql)select @sql='update ai set id=11 where i=1 and id=22' exec(@sql) 给你一个例题条件是不确定,可以利用动态语句运行 exec
1、加参数说明选择,比如 @Op int =0if @Op=0 update StudentBaseInfo set sfzh = isnull(@sfzh,sfzh), xh = isnull(@xh,xh).............. where sfzh = @sfzh and xh=@xhif @Op=1 update StudentBaseInfo set sfzh = isnull(@sfzh,sfzh), xh = isnull(@xh,xh).............. where xl=@xl and xh=@xh or mz=@mz ... 2、先把xls倒入到新表,再 insert 表(...) select ... from 新表
问题二必须写字段列表,来对应字段关系 问题一,用动态SQL语句
/* 生成组合条件的存储过程 */ --创建得到组合条件的存储过程 create procedure test @fd1 varchar(100),@value1 varchar(100) ,@fd2 varchar(100),@value2 varchar(100) ,@fd3 varchar(100),@value3 varchar(100) ,@fd4 varchar(100),@value4 varchar(100) ,@tj varchar(1000) output as select @tj='',@tj=@tj +case isnull(@value1,'') when '' then '' else ' and ['+@fd1+']='''+@value1+'''' end +case isnull(@value2,'') when '' then '' else ' and ['+@fd2+']='''+@value2+'''' end +case isnull(@value3,'') when '' then '' else ' and ['+@fd3+']='''+@value3+'''' end +case isnull(@value4,'') when '' then '' else ' and ['+@fd4+']='''+@value4+'''' end if @tj<>'' set @tj=' where '+right(@tj,len(@tj)-5) go--测试 declare @tj varchar(1000) exec test '姓名','李XX' ,'职务','' ,'性别','男' ,'学历','本科' ,@tj output print @tjgo drop procedure test
@condition varchar(1000)
as
declare @sql varchar(1000)
set @sql=' update aaa .....' +@condition --你的條件
exec(@sql)
...
go
CREATE TABLE [ai] (
[id] [int] NULL ,
[i] [int] NULL
)
GOdeclare @sql nvarchar(4000)
select @sql='update ai set id=11 where i=1'
exec (@sql)select @sql='update ai set id=11 where i=1 and id=22'
exec(@sql)
给你一个例题条件是不确定,可以利用动态语句运行
exec
@Op int =0if @Op=0
update StudentBaseInfo
set sfzh = isnull(@sfzh,sfzh),
xh = isnull(@xh,xh)..............
where sfzh = @sfzh and xh=@xhif @Op=1
update StudentBaseInfo
set sfzh = isnull(@sfzh,sfzh),
xh = isnull(@xh,xh)..............
where xl=@xl and xh=@xh or mz=@mz
... 2、先把xls倒入到新表,再
insert 表(...) select ... from 新表
问题一,用动态SQL语句
生成组合条件的存储过程
*/
--创建得到组合条件的存储过程
create procedure test
@fd1 varchar(100),@value1 varchar(100)
,@fd2 varchar(100),@value2 varchar(100)
,@fd3 varchar(100),@value3 varchar(100)
,@fd4 varchar(100),@value4 varchar(100)
,@tj varchar(1000) output
as
select @tj='',@tj=@tj
+case isnull(@value1,'') when '' then ''
else ' and ['+@fd1+']='''+@value1+'''' end
+case isnull(@value2,'') when '' then ''
else ' and ['+@fd2+']='''+@value2+'''' end
+case isnull(@value3,'') when '' then ''
else ' and ['+@fd3+']='''+@value3+'''' end
+case isnull(@value4,'') when '' then ''
else ' and ['+@fd4+']='''+@value4+'''' end
if @tj<>''
set @tj=' where '+right(@tj,len(@tj)-5)
go--测试
declare @tj varchar(1000)
exec test '姓名','李XX'
,'职务',''
,'性别','男'
,'学历','本科'
,@tj output
print @tjgo
drop procedure test