Global_User_Info的UserName字段是VARCHARcreate proc CA_Forums_User_Unite @UserName4 varchar(50), @UserNameText varchar(8000)as set nocount onset @UserNameText = @UserNameText +','declare @UserID1 int declare @UserID4 int declare @UserName1 varchar(8000) declare @UserError varchar(8000) set @UserError='' while charindex(',',@UserNameText)>0 BEGIN set @UserName1 = left(@UserNameText , charindex(',',@UserNameText)-1) select @UserID4=UserID from Global_User_Info where UserName=@UserName4 select @UserID1=UserID from Global_User_Info where UserName=@UserName1if exists (select @UserName1 from Global_User_Info where UserName=@UserName1) begin if @UserID1<>@UserID4 --不相等才修改 Begin update a set Score1=cast(a.Score1 as float(10))+cast(b.Score1 as float(10)), Score2=cast(a.Score2 as float(10))+cast(b.Score2 as float(10)), Score3=cast(a.Score3 as float(10))+cast(b.Score3 as float(10)), Score4=cast(a.Score4 as float(10))+cast(b.Score4 as float(10)), Score5=cast(a.Score5 as float(10))+cast(b.Score5 as float(10)), Score6=cast(a.Score6 as float(10))+cast(b.Score6 as float(10)), Score7=cast(a.Score7 as float(10))+cast(b.Score7 as float(10)), Score8=cast(a.Score8 as float(10))+cast(b.Score8 as float(10)), Score9=cast(a.Score9 as float(10))+cast(b.Score9 as float(10)), Score10=cast(a.Score10 as float(10))+cast(b.Score10 as float(10)) from Global_User_Info a,Global_User_Info b where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts from Global_User_Profile a,Global_User_Profile b where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts from CA_Forums_statistics_User a,CA_Forums_statistics_User b where a.UserID=@UserID4 and b.UserID=@UserID1 --把原来用户的帖子数加到目标用户上 delete CA_Forums_statistics_User where UserID=@UserID1 delete Global_User_Profile where UserID=@UserID1 delete Global_User_Info where UserID=@UserID1 update b set b.UserID = a.UserID , b.PostAuthor = a.PostAuthor from CA_Forums_Post a,CA_Forums_Post b where a.UserID=@UserID4 and b.UserID=@UserID1 update b set b.UserID = a.UserID , b.PostAuthor = a.PostAuthor from CA_Forums_Thread a,CA_Forums_Thread b where a.UserID=@UserID4 and b.UserID=@UserID1 update b set b.UserID = a.UserID , b.Creator = a.Creator from CA_Forums_PostAttachment a,CA_Forums_PostAttachment b where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts from Global_User_Profile a,Global_User_Profile b where a.UserID=@UserID4 and b.UserID=@UserID1
--消息 update a set MessageGroupID=c.MessageGroupID from Global_Message a, Global_Message_Group b, Global_Message_Group c where a.MessageGroupID=b.MessageGroupID and b.UserID=@UserID1 and c.UserID=@UserID4 and b.GroupName=c.GroupName delete Global_Message_Group where UserID=@UserID1 --黑名单 update a set FriendGroupID=c.FriendGroupID from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c where a.FriendGroupID=b.FriendGroupID and b.UserID=@UserID1 and c.UserID=@UserID4 and b.GroupName='黑名单' and c.GroupName='黑名单' and not exists (select 1 from Global_User_Friends x,Global_User_FriendGroup y where y.GroupName='黑名单' and y.UserID=c.UserID and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID) --过滤重复的黑名单 --好友 update a set FriendGroupID=c.FriendGroupID from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c where a.FriendGroupID=b.FriendGroupID and b.UserID=@UserID1 and c.UserID=@UserID4 and b.GroupName='我的好友' and c.GroupName='我的好友' and not exists (select 1 from Global_User_Friends x,Global_User_FriendGroup y where y.GroupName='我的好友' and y.UserID=c.UserID and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID) --删除在黑名单中的好友 delete a from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c,Global_User_Friends d where a.FriendGroupID=b.FriendGroupID and c.FriendGroupID=d.FriendGroupID and b.GroupName='我的好友' and c.GroupName='黑名单' and a.FriendUserID=d.FriendUserID and b.UserID=@UserID4 and c.UserID=@UserID4 --删除好友Group delete Global_User_FriendGroup where UserID=@UserID1 --删除在其他用户中的好友记录 update Global_User_Friends set FriendUserID=@UserID4 where FriendUserID=@UserID1 --权限合并 update a set GroupType=b.GroupType,GroupName=b.GroupName from CA_Forums_UserRole_Group a,CA_Forums_UserRole_Group b,CA_Forums_UsersInRole c,CA_Forums_UsersInRole d where a.RoleGroupID=c.RoleGroupID and b.RoleGroupID=d.RoleGroupID and c.UserID=@UserID4 and d.UserID=@UserID1 and a.GroupType<b.GroupType --删除UserRole_Group delete a from CA_Forums_UserRole_Group a,CA_Forums_UsersInRole b where a.RoleGroupID=b.RoleGroupID and b.UserID=@UserID1 --删除UsersInRole delete CA_Forums_UsersInRole where UserID=@UserID1 End
set @UserNameText =right(@UserNameText ,len(@UserNameText )-charindex(',',@UserNameText))endelse begin set @UserError = @UserName1 + @UserError print @UserError return @UserError endEND GO
/* 没理由的呀,这样改改看: */ --if exists (select @UserName1 from Global_User_Info where UserName=@UserName1) if exists (select 1 from Global_User_Info where UserName=@UserName1)
照着你后面写的这个 调用存储过程是个死循环 ----------------------- while charindex(',',@UserNameText)>0 BEGIN set @UserName1 = left(@UserNameText , charindex(',',@UserNameText)-1) --写道这里 set @UserNameText =right(@UserNameText ,len(@UserNameText )-charindex(',',@UserNameText))
@UserName4 varchar(50),
@UserNameText varchar(8000)as
set nocount onset @UserNameText = @UserNameText +','declare @UserID1 int
declare @UserID4 int
declare @UserName1 varchar(8000)
declare @UserError varchar(8000)
set @UserError=''
while charindex(',',@UserNameText)>0
BEGIN
set @UserName1 = left(@UserNameText , charindex(',',@UserNameText)-1)
select @UserID4=UserID from Global_User_Info where UserName=@UserName4
select @UserID1=UserID from Global_User_Info where UserName=@UserName1if exists (select @UserName1 from Global_User_Info where UserName=@UserName1)
begin
if @UserID1<>@UserID4 --不相等才修改
Begin update a set Score1=cast(a.Score1 as float(10))+cast(b.Score1 as float(10)),
Score2=cast(a.Score2 as float(10))+cast(b.Score2 as float(10)),
Score3=cast(a.Score3 as float(10))+cast(b.Score3 as float(10)),
Score4=cast(a.Score4 as float(10))+cast(b.Score4 as float(10)),
Score5=cast(a.Score5 as float(10))+cast(b.Score5 as float(10)),
Score6=cast(a.Score6 as float(10))+cast(b.Score6 as float(10)),
Score7=cast(a.Score7 as float(10))+cast(b.Score7 as float(10)),
Score8=cast(a.Score8 as float(10))+cast(b.Score8 as float(10)),
Score9=cast(a.Score9 as float(10))+cast(b.Score9 as float(10)),
Score10=cast(a.Score10 as float(10))+cast(b.Score10 as float(10))
from Global_User_Info a,Global_User_Info b
where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts
from Global_User_Profile a,Global_User_Profile b
where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts
from CA_Forums_statistics_User a,CA_Forums_statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1 --把原来用户的帖子数加到目标用户上 delete CA_Forums_statistics_User where UserID=@UserID1
delete Global_User_Profile where UserID=@UserID1
delete Global_User_Info where UserID=@UserID1 update b set b.UserID = a.UserID , b.PostAuthor = a.PostAuthor from CA_Forums_Post a,CA_Forums_Post b
where a.UserID=@UserID4 and b.UserID=@UserID1 update b set b.UserID = a.UserID , b.PostAuthor = a.PostAuthor from CA_Forums_Thread a,CA_Forums_Thread b
where a.UserID=@UserID4 and b.UserID=@UserID1 update b set b.UserID = a.UserID , b.Creator = a.Creator from CA_Forums_PostAttachment a,CA_Forums_PostAttachment b
where a.UserID=@UserID4 and b.UserID=@UserID1 update a set TotalPosts=a.TotalPosts+b.TotalPosts
from Global_User_Profile a,Global_User_Profile b
where a.UserID=@UserID4 and b.UserID=@UserID1
--消息
update a set MessageGroupID=c.MessageGroupID
from Global_Message a, Global_Message_Group b, Global_Message_Group c
where a.MessageGroupID=b.MessageGroupID and b.UserID=@UserID1 and c.UserID=@UserID4 and b.GroupName=c.GroupName delete Global_Message_Group where UserID=@UserID1 --黑名单
update a set FriendGroupID=c.FriendGroupID
from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='黑名单'
and c.GroupName='黑名单'
and not exists (select 1 from Global_User_Friends x,Global_User_FriendGroup y where y.GroupName='黑名单'
and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID
and x.FriendUserID=a.FriendUserID) --过滤重复的黑名单
--好友
update a set FriendGroupID=c.FriendGroupID
from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='我的好友'
and c.GroupName='我的好友'
and not exists (select 1 from Global_User_Friends x,Global_User_FriendGroup y where y.GroupName='我的好友'
and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID
and x.FriendUserID=a.FriendUserID)
--删除在黑名单中的好友
delete a from Global_User_Friends a,Global_User_FriendGroup b,Global_User_FriendGroup c,Global_User_Friends d
where a.FriendGroupID=b.FriendGroupID
and c.FriendGroupID=d.FriendGroupID
and b.GroupName='我的好友'
and c.GroupName='黑名单'
and a.FriendUserID=d.FriendUserID
and b.UserID=@UserID4
and c.UserID=@UserID4
--删除好友Group
delete Global_User_FriendGroup where UserID=@UserID1
--删除在其他用户中的好友记录
update Global_User_Friends set FriendUserID=@UserID4 where FriendUserID=@UserID1 --权限合并
update a set GroupType=b.GroupType,GroupName=b.GroupName
from CA_Forums_UserRole_Group a,CA_Forums_UserRole_Group b,CA_Forums_UsersInRole c,CA_Forums_UsersInRole d
where a.RoleGroupID=c.RoleGroupID
and b.RoleGroupID=d.RoleGroupID
and c.UserID=@UserID4
and d.UserID=@UserID1
and a.GroupType<b.GroupType --删除UserRole_Group
delete a
from CA_Forums_UserRole_Group a,CA_Forums_UsersInRole b
where a.RoleGroupID=b.RoleGroupID
and b.UserID=@UserID1 --删除UsersInRole
delete CA_Forums_UsersInRole where UserID=@UserID1
End
set @UserNameText =right(@UserNameText ,len(@UserNameText )-charindex(',',@UserNameText))endelse
begin
set @UserError = @UserName1 + @UserError
print @UserError
return @UserError
endEND
GO
没理由的呀,这样改改看:
*/
--if exists (select @UserName1 from Global_User_Info where UserName=@UserName1)
if exists (select 1 from Global_User_Info where UserName=@UserName1)
我要实现的是,输入一串用英文逗号隔开的用户名,如果这个用户名不存在在表里面就返回这个用户名,继续执行下一个用户名...这样循环,知道所有用户都执行完相关操作
说明一下,1x1x1x1是我拿来故意测试的错误用户名
字段 UserID,UserName
182 Inzaghi
183 milannesta
表 Global_User_Profile
字段 UserID,TotalPosts
182 20
183 30
GO
return @UserErrorreturn 是存储过程的返回值,始终是整型,@UserError是varchar。
@UserName4 varchar(50),
@UserNameText varchar(8000),
@UserError varchar(8000) outputas
set nocount onset @UserNameText = @UserNameText +','declare @UserID1 int
declare @UserID4 int
declare @UserName1 varchar(8000)
--declare @UserError varchar(8000)
...
print @UserError
--return @UserError
end
set @UserNameText =right(@UserNameText ,len(@UserNameText )-charindex(',',@UserNameText))END
GO
exec CA_Forums_User_Unite 'milannesta','1x1x1x1,Inzaghi,2xx2x2x2', @UserError output
必须把@UserError 声明到最前面吗?
都要写成declare @UserError varchar(8000)
exec CA_Forums_User_Unite 'milannesta','1x1x1x1,Inzaghi,2xx2x2x2', @UserError output
不能只写
exec CA_Forums_User_Unite 'milannesta','1x1x1x1,Inzaghi,2xx2x2x2'
都要写成declare @UserError varchar(8000)
exec CA_Forums_User_Unite 'milannesta','1x1x1x1,Inzaghi,2xx2x2x2', @UserError output
不能只写
exec CA_Forums_User_Unite 'milannesta','1x1x1x1,Inzaghi,2xx2x2x2'
-------------------------
/*
可以:
*/
alter proc CA_Forums_User_Unite
...
@UserError=null varchar(8000) output
...
调用存储过程是个死循环
-----------------------
while charindex(',',@UserNameText)>0
BEGIN
set @UserName1 = left(@UserNameText , charindex(',',@UserNameText)-1)
--写道这里
set @UserNameText =right(@UserNameText ,len(@UserNameText )-charindex(',',@UserNameText))
我怎么能够每次都把错误的名字的用户取出来,然后返回继续执行下面的操作?
如果不修改其他的
就让return返回一个字符串怎么办?
如果不修改其他的
就让return返回一个字符串怎么办?
-------------------------------
都说了不行,SQL规定必须return整型。
选:
2、不退出,继续搜索后面的名字-------------------------------
都说了不行,SQL规定必须return整型。
--------------------------------
明白了,学习到了新东西