存储过程拆分循环保存的问题
存储过程1
CREATE PROCEDURE PopFirstWord
@SourceString NVARCHAR(4000) = NULL OUTPUT,
@FirstWord NVARCHAR(4000) = NULL OUTPUT
AS
SET NOCOUNT ON
-- Procedure accepts a comma delimited string as the first parameter
-- Procedure outputs the first word in the second parameter
-- Procedure outputs the remainder of the delimeted string in the first parameter
-- Procedure yields the length of the First Word as the return value
DECLARE @Oldword NVARCHAR(4000)
DECLARE @Length INT
DECLARE @CommaLocation INT
SELECT @Oldword = @SourceString
IF NOT @Oldword IS NULL
BEGIN
SELECT @CommaLocation = CHARINDEX('#',@Oldword)
SELECT @Length = DATALENGTH(@Oldword)
IF @CommaLocation = 0
BEGIN
SELECT @FirstWord = @Oldword
SELECT @SourceString = NULL
RETURN @Length
END
SELECT @FirstWord = SUBSTRING(@Oldword, 1, @CommaLocation -1)
SELECT @SourceString = SUBSTRING(@Oldword, @CommaLocation + 1, @Length - @CommaLocation)
RETURN @Length - @CommaLocation
END
RETURN 0
------------------------------------------------
GO
CREATE PROCEDURE InsertToExpertList
@ExpIDList NVARCHAR(4000),
@EIdList NVARCHAR(4000),
@CMaker varchar(50)
AS
SET NOCOUNT ON
Begin TRAN
DECLARE @Length INT
DECLARE @FirstExpID NVARCHAR(4000)
DECLARE @ExpID INT
SELECT @Length = DATALENGTH(@ExpIDList)
WHILE @Length > 0
BEGIN
EXECUTE @Length = PopFirstWord @ExpIDList OUTPUT, @FirstExpID OUTPUT
IF @Length > 0
BEGIN
SELECT @ExpID= CONVERT(int, @FirstExpID)
if @ExpID>0
Begin
DECLARE @ELen INT
DECLARE @FirstEId NVARCHAR(4000)
DECLARE @EId INT
SELECT @ELen = DATALENGTH(@EIdList)
WHILE @ELen > 0
begin
EXECUTE @ELen = PopFirstWord @EIdList OUTPUT, @FirstEId OUTPUT
if @ELen > 0
begin
SELECT @EID= CONVERT(int, @FirstEID)
DECLARE @ncount INT
select @ncount= ( select count(*) from ExampleEstimate where EXPId=@EXPId and EId=@EId)
if @ncount=0
begin
insert into ExampleEstimate(EXPId,EId,CMaker) values ( @EXPId,@EId,@CMaker)
end
end
end
End
if @@ERROR <>0
Begin
ROLLBACK TRAN
RETURN 0
End
END
END
COMMIT TRAN
GO
1 4
1 5
2 3
2 4
2 5
可是却只插入前面3条，也就是说执行了里面的循环后，外面的循环就不执行了。PopFirstWord是来源微软的petshop的，是没问题的。主要是InsertToExpertList该怎么改，高手帮帮忙。
[解决办法]
因为外层1次循环之后，@EIdList已经被内层循环搞成NULL了。
[解决办法]
定义那么多变量，你不觉得眼花的么，用集合的方式改写以下：
ALTER PROCEDURE InsertToExpertList
@ExpIDList NVARCHAR(4000),
@EIdList NVARCHAR(4000),
@CMaker varchar(50)
AS
SET NOCOUNT ON
select @ExpIDList=nullif(@ExpIDList,''), @EIdList=nullif(@EIdList,'')
if @ExpIDList is null or @EIdList is null return
SET XACT_ABORT ON
Begin TRAN
declare @s nvarchar(10)
declare @ta table (expid int)
while @ExpIDList is not null
begin
exec PopFirstWord @ExpIDList OUTPUT, @s OUTPUT
insert @ta select @s
end
declare @tb table (eid int)
while @EIdList is not null
begin
exec PopFirstWord @EIdList OUTPUT, @s OUTPUT
insert @tb select @s
end
insert into ExampleEstimate(EXPId,EId,CMaker) select *, @CMaker from @ta a, @tb b where not exists (select 1 from ExampleEstimate where EXPId=a.expid and EId=b.eid)
COMMIT TRAN
GO
create PROCEDURE InsertToExpertListNew
@ExpIDList NVARCHAR(max),
@EIdList NVARCHAR(max),
@CMaker varchar(50)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
Begin TRAN
declare @sql nvarchar(max) = 'insert into ExampleEstimate(EXPId,EId,CMaker) select *, '''+@CMaker+''' from '
set @sql += '(values('+replace(@ExpIDList,'#','),(')+')) a(expid), (values('+replace(@EIdList,'#','),(')+')) b(eid)'
set @sql += ' where not exists (select 1 from ExampleEstimate where EXPId=a.expid and EId=b.eid)'
exec (@sql)
COMMIT TRAN
GO
