DECLARE @tempKnowledgeID NVARCHAR(50)
DECLARE @tempID int
DECLARE @Table TABLE(
ID INT,
Val NVARCHAR(120)
)
--增加临时表数据
INSERT INTO @Table(ID,Val) SELECT ID,[Value] FROM dbo.Split(@KnowledgeIDS,',')
---增加订阅功能
WHILE EXISTS(select 1 from @Table)
begin
SET ROWCOUNT 1
select @tempID=ID,@tempKnowledgeID=Val from @Table
SET ROWCOUNT 0
delete from @Table where [ID] = @tempID
IF EXISTS(SELECT 1 FROM tbl_KbaSubscribeKnowledge WHERE knowledgeID=@tempKnowledgeID AND userID=@UserID)
BEGIN
UPDATE tbl_KbaSubscribeKnowledge SET IsActive=@IsActive,DeSubscribeTime=GETDATE() WHERE knowledgeID=@tempKnowledgeID AND userID=@UserID
END
ELSE
BEGIN
INSERT INTO dbo.tbl_KbaSubscribeKnowledge (
ID,
KnowledgeID,
UserID,
SubscribeTime,
DeSubscribeTime,
IsActive
) VALUES (
/* ID - uniqueidentifier */ NEWID(),
/* KnowledgeID - nvarchar(50) */ @tempKnowledgeID,
/* UserID - nvarchar(50) */@UserID,
/* SubscribeTime - datetime */ GETDATE(),
/* DeSubscribeTime - datetime */ null,
/* IsActive - bit */ @IsActive )
END
end
DECLARE @tempID int
DECLARE @Table TABLE(
ID INT,
Val NVARCHAR(120)
)
--增加临时表数据
INSERT INTO @Table(ID,Val) SELECT ID,[Value] FROM dbo.Split(@KnowledgeIDS,',')
---增加订阅功能
WHILE EXISTS(select 1 from @Table)
begin
SET ROWCOUNT 1
select @tempID=ID,@tempKnowledgeID=Val from @Table
SET ROWCOUNT 0
delete from @Table where [ID] = @tempID
IF EXISTS(SELECT 1 FROM tbl_KbaSubscribeKnowledge WHERE knowledgeID=@tempKnowledgeID AND userID=@UserID)
BEGIN
UPDATE tbl_KbaSubscribeKnowledge SET IsActive=@IsActive,DeSubscribeTime=GETDATE() WHERE knowledgeID=@tempKnowledgeID AND userID=@UserID
END
ELSE
BEGIN
INSERT INTO dbo.tbl_KbaSubscribeKnowledge (
ID,
KnowledgeID,
UserID,
SubscribeTime,
DeSubscribeTime,
IsActive
) VALUES (
/* ID - uniqueidentifier */ NEWID(),
/* KnowledgeID - nvarchar(50) */ @tempKnowledgeID,
/* UserID - nvarchar(50) */@UserID,
/* SubscribeTime - datetime */ GETDATE(),
/* DeSubscribeTime - datetime */ null,
/* IsActive - bit */ @IsActive )
END
end