if exists (select * from t where id=1000 and a1 is null)
begin
update t set a1=@data where id=1000 and a1 is null)
return 1
endif exists (select * from t where id=1000 and a2 is null)
begin
update t set a1=@data where id=1000 and a2 is null)
return 2
endif exists (select * from t where id=1000 and a3 is null)
begin
update t set a1=@data where id=1000 and a3 is null)
return 3
endif exists (select * from t where id=1000 and a4 is null)
begin
update t set a1=@data where id=1000 and a4 is null)
return 4
endreturn 0 ---全非空,没有更新
begin
update t set a1=@data where id=1000 and a1 is null)
return 1
endif exists (select * from t where id=1000 and a2 is null)
begin
update t set a1=@data where id=1000 and a2 is null)
return 2
endif exists (select * from t where id=1000 and a3 is null)
begin
update t set a1=@data where id=1000 and a3 is null)
return 3
endif exists (select * from t where id=1000 and a4 is null)
begin
update t set a1=@data where id=1000 and a4 is null)
return 4
endreturn 0 ---全非空,没有更新
但如何用一个SQL语句实现呢,
这个SQL语句我要用在ASP.NET中
请再帮帮忙
create proc AutoInsert
@InsertValue char(2)
asIF exists(select id from t where id = 1000)
BEGINif (select(a1) from T where id = 1000) is null
begin
update T set a1 = @insertvalue
return
endif (select(a2) from T where id = 1000) is null
begin
update T set a2 = @insertvalue
return
endif (select(a3) from T where id = 1000) is null
begin
update T set a3 = @insertvalue
return
endif (select(a4) from T where id = 1000) is null
begin
update T set a4 = @insertvalue
return
endEND如果a1,a2这些字段名不确认,可以:
select name from syscolumns where id = (
select id from sysobjects where name = 't' and xtype = 'U')
order by name
然后再循环
when (a1+a2+a3) is null then a4
else isnull(a4,'值') end,
a3 = case
when (a1+a2) is null then a3
else isnull(a3,'值') end,
a2 = case
when a1 is null then a2
else isnull(a2,'值') end,
a1 = isnull(a1,'值')
where id = 1000
SET A1=CASE WHEN A1 IS NULL THEN 'data' ELSE A1,
A2=CASE WHEN A2 IS NULL THEN 'data' ELSE A2,
A3=CASE WHEN A3 IS NULL THEN 'data' ELSE A3,
A4=CASE WHEN A4 IS NULL THEN 'data' ELSE A4
WHERE ID=1000
如果 a1 null
a2 'bb'
a3 null
a4 'bb'
那么你的语句会把a3改为'data'
SET A1=CASE WHEN A1 IS NULL THEN 'data' ELSE A1 END,
A2=CASE WHEN A2 IS NULL AND A1 IS NOT NULL THEN 'data' ELSE A2 END,
A3=CASE WHEN A3 IS NULL AND (A2+A1) IS NOT NULL THEN 'data' ELSE A3 END,
A4=CASE WHEN A4 IS NULL AND (A3+A2+A1) IS NOT NULL THEN 'data' ELSE A4 END
WHERE ID=1000
又忘了不更新的情況了。搞了半天還是Chiff(~o~) 的更准確。
================================================================CSDN 论坛助手 Ver 1.0 B0402提供下载。 改进了很多,功能完备!★ 浏览帖子速度极快![建议系统使用ie5.5以上]。 ★ 多种帖子实现界面。
★ 保存帖子到本地[html格式]★ 监视您关注帖子的回复更新。
★ 可以直接发贴、回复帖子★ 采用XML接口,可以一次性显示4页帖子,同时支持自定义每次显示帖子数量。可以浏览历史记录!
★ 支持在线检测程序升级情况,可及时获得程序更新的信息。★★ 签名 ●
可以在您的每个帖子的后面自动加上一个自己设计的签名哟。Http://www.ChinaOK.net/csdn/csdn.zip
Http://www.ChinaOK.net/csdn/csdn.rar
Http://www.ChinaOK.net/csdn/csdn.exe [自解压]
insert into csdn values (1,'10' ,null,null, null)
insert into csdn values (1,null ,'2323',null, null)
insert into csdn values (1,null ,'2323','2323', null)
insert into csdn values (1,'23' ,'2323','2323', null)
insert into csdn values (1,'34' ,'2323',null, null)
insert into csdn values (1,'45' ,'2323','2323', null)
insert into csdn values (1,'56' ,'2323','2323', '66')
update csdn
set
a1=case when COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)=1 then 999 else a1 end ,a2=case when COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)=2 then 999 else a2 end ,a3=case when COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)=3 then 999 else a3 end ,a4=case when COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)=4 then 999 else a4 end
select * from csdnID A1 A2 A3 A4
----------- ---------- ---------- ---------- ----------
1 10 999 NULL NULL
1 999 2323 NULL NULL
1 999 2323 2323 NULL
1 23 2323 2323 999
1 34 2323 999 NULL
1 45 2323 2323 999
1 56 2323 2323 66
COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)
做成自定义函数,这样语句就简化多了。
alter table add flag
as COALESCE(case when a1 is null then 1 else null end ,
case when a2 is null then 2 else null end ,
case when a3 is null then 3 else null end ,
case when a4 is null then 4 else null end)语句会简单好多:
update csdn
set a1=(case when flag=1 then 'newvalue' else a1 end ),
a2=(case when flag=2 then 'newvalue' else a2 end ),
a3=(case when flag=3 then 'newvalue' else a3 end ),
a4=(case when flag=4 then 'newvalue' else a4 end )
也比较好理解,可能对其它操作也会有用,你可以试试。
就是不能把表名作为参数,
哎,命苦啊
CREATE PROCEDURE mailbox_book_x @table varchar(20),@data varchar(6),@id varchar(10)
as
if exists (select * from @table where id=@id and mailbox4 is null)
begin
update @table set mailbox4=@data where id=@id and mailbox4 is null)
return 1
endif exists (select * from @table where id=@id and mailbox5 is null)
begin
update @table set mailbox5=@data where id=@id and mailbox5 is null)
return 2
endif exists (select * from @table where id=@id and mailbox6 is null)
begin
update @table set mailbox6=@data where id=@id and mailbox6 is null)
return 3
endif exists (select * from @table where id=@id and mailbox7 is null)
begin
update @table set mailbox7=@data where id=@id and mailbox4 is null)
return 4
endif exists (select * from @table where id=@id and mailbox8 is null)
begin
update @table set mailbox8=@data where id=@id and mailbox8 is null)
return 5
endif exists (select * from @table where id=@id and mailbox9 is null)
begin
update @table set mailbox9=@data where id=@id and mailbox9 is null)
return 3
endreturn 0
go
服务器: 消息 137,级别 15,状态 2,过程 mailbox_book_x,行 5
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 7
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 11
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 13
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 17
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 19
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 23
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 25
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 29
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 31
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 35
必须声明变量 '@table'。
服务器: 消息 137,级别 15,状态 1,过程 mailbox_book_x,行 37
必须声明变量 '@table'。
这样的表有26个,而且选那一个由用户决定,
所以不能写死,必须把它设为变量请各位再帮帮忙
declare @str varchar(200)
select @str='update '+@table+' set mailbox4=@data where id=@id and mailbox4 is null)'
exec (@str)下面类似。
declare @sql nchar(1000)
set @sql = 'select * from ' + @table + ' where ......'
exec sp_executesql @sql
if @@rowcount = 0
print '没记录'
select @str='update '+@table+' set mailbox4='+@data +' where id='+@id+' and mailbox4 is null)'
declare @sql nchar(1000)
set @sql = 'select * from ' + @table + ' where ......'
exec sp_executesql @sql
if @@rowcount = 0
print '没记录'
在asp.net中调用,该怎样写
能帮我把这个存储过程写出来吗
^-^ thx
CREATE PROCEDURE mailbox_book_x @table varchar(20),@data varchar(6),@id varchar(10)
asdeclare @sql nchar(300)set @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox4 is null'
exec sp_executesql @sql
if @@rowcount() > 1 begin
update @table set mailbox4=@data where id=@id and mailbox4 is null)
return 1
endset @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox5 is null'
exec sp_executesql @sql
if @@rowcount() > 1
begin
update @table set mailbox5=@data where id=@id and mailbox5 is null)
return 2
endset @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox6 is null'
exec sp_executesql @sql
if @@rowcount() > 1
begin
update @table set mailbox6=@data where id=@id and mailbox6 is null)
return 3
endset @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox7 is null'
exec sp_executesql @sql
if @@rowcount() > 1
begin
update @table set mailbox7=@data where id=@id and mailbox4 is null)
return 4
endset @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox8 is null'
exec sp_executesql @sql
if @@rowcount() > 1
begin
update @table set mailbox8=@data where id=@id and mailbox8 is null)
return 5
endset @sql = 'select * from '+rtrim(@table)+' where id='+ rtrim(@id) +' and mailbox9 is null'
exec sp_executesql @sql
if @@rowcount() > 1
begin
update @table set mailbox9=@data where id=@id and mailbox9 is null)
return 3
endreturn 0
go
如果访问量大的话,sever可能会down我这里有一个新的解决方案:
在asp.net中,把id为1000的记录搜索出来
放入一个table中,然后在tale 中判断A1,A2,A3,A4的值
再根据它们的值做insert但是我不知道,那一种效率高