存储过程拆分循环保存的问题

[chenhaoying]

存储过程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

存储过程2

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

其中存储过程PopFirstWord是按#拆分字符串，现在我希望是在InsertToExpertList中执行@ExpIDList=1#2；@EIdList=3#4#5时能在ExampleEstimate表中插入数据：
expid eid
1 3
1 4
1 5
2 3
2 4
2 5
可是却只插入前面3条，也就是说执行了里面的循环后，外面的循环就不执行了。PopFirstWord是来源微软的petshop的，是没问题的。主要是InsertToExpertList该怎么改，高手帮帮忙。

[Vidor]

因为外层1次循环之后，@EIdList已经被内层循环搞成NULL了。

[Vidor]

定义那么多变量，你不觉得眼花的么，用集合的方式改写以下：

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

[Vidor]

另一种思路：

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