alter proc GSP_316_CreateList
@UserID int,
@List varchar(6)
as
SET NOCOUNT ON;
DECLARE @MemberID int; --成员ID
DECLARE @FaceID int; --头像
DECLARE @Accounts nvarchar(100); --账号
DECLARE @Content varchar(2000); --备注
DECLARE @commaPos int; --保存分割字符的位置
DECLARE @subString varchar(2000); --保存每个临时子串
DECLARE @commaPos_ int; --保存子信息分割字符的位置
DECLARE @Str varchar(8000); --保存子信息每个临时子串
if @List='friend' --好友
begin
select @str=friend from AccountsInfo where UserID=@UserID
end
else
begin --黑名单
select @str=hate from AccountsInfo where UserID=@UserID
end
/*无记录返回*/
IF @Str is null
BEGIN
PRINT '无记录';
RETURN;
END;
if object_id('tempdb..#temp') is not null--临时表存在则删除
begin
drop table #temp
end
create table #temp--建立临时表
(
UserID int,
FaceID int,
Accounts nvarchar(100),
Content nvarchar(1000)
)
SET @commaPos = CHARINDEX(',', @Str, 0);
IF @commaPos = 0
BEGIN
PRINT '没有包含分割字符';
RETURN;
END;
BEGIN TRAN; --因为有可能是批量操作,所以采用事务,防止出现脏数据,对本例子没有实际意义
WHILE @commaPos > 0
BEGIN
SET @subString = SUBSTRING(@Str, 1, @commaPos - 1); --分割出一个子字符串
SET @commaPos_ = CHARINDEX('#', @subString, 0);
SET @MemberID = SUBSTRING(@subString, 1, @commaPos_ - 1); --分割出一个子字符串
SET @subString = SUBSTRING(@subString, @commaPos_ + 1, Len(@Str)); --
SET @Content = @subString
select @FaceID=FaceID,@Accounts=Accounts from AccountsInfo where UserID=@MemberID
insert into #temp (UserID,FaceID,Accounts,Content) values (@UserID,@FaceID,@Accounts,@Content)
SET @Str = SUBSTRING(@Str, @commaPos + 1, Len(@Str));
SET @commaPos = CHARINDEX(',', @Str, 0);
END
SET @MemberID = SUBSTRING(@Str, 1, @commaPos_ - 1);
SET @Str = SUBSTRING(@Str, @commaPos_ + 1, Len(@Str));
SET @Content = @Str
select @FaceID=FaceID,@Accounts=Accounts from AccountsInfo where UserID=@MemberID
insert into #temp (UserID,FaceID,Accounts,Content) values (@UserID,@FaceID,@Accounts,@Content) --经过上面的分解,此处还有最后一个子串, 不要忘记
IF @@error = 0
COMMIT TRAN; --因为启用了事务,所以这里要做处理
ELSE
ROLLBACK TRAN;
select * from #temp