I have a stored procedure which output a column. It runs without problem.

我有一个输出列的存储过程。它运行没有问题。

exec dbo.sp_SelectAlignmentSubset
     @AlnID
    ,@ParentTaxID
    ,@SeqTypeID
    ,@LocationID
    ,@SubsetSize
    ,@SimilarityThreshold
    ,@SimilarityTable
    ,@AnchorSeqID

But when I insert the procedure output into a temp table,

但是当我将过程输出插入临时表时,

create table #resultSeqIDs
(
    SeqID int not null
)

Insert into #resultSeqIDs
exec dbo.sp_SelectAlignmentSubset
     @AlnID
    ,@ParentTaxID
    ,@SeqTypeID
    ,@LocationID
    ,@SubsetSize
    ,@SimilarityThreshold
    ,@SimilarityTable
    ,@AnchorSeqID

error pop out "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

错误弹出“当前事务无法提交,无法支持写入日志文件的操作。回滚事务。”

Thanks in advance.

提前致谢。

The query of the procedure is here:

该过程的查询在这里:

Alter Proc dbo.sp_SelectAlignmentSubset
    @AlnID int,
    @ParentTaxID int,
    @SeqTypeID int,
    @LocationID int,
    @SubsetSize int,
    @SimilarityThreshold float,
    @SimilarityTable nvarchar(255),
    @AnchorSeqID int = null
As
    declare @sql nvarchar(4000), @param nvarchar(2000), @subsetActualSize int, @lastSeqID int, @meanSimilarity float

    --- Cleaning
    begin try 
        drop table #Subset
    end try
    begin catch
    end catch
    begin try 
        drop table #tmpAllSeqs
    end try
    begin catch
    end catch
    begin try 
        drop table #AllSeqs
    end try
    begin catch
    end catch

    --- Create temp tables
    create table #Subset
    (
        SeqID int not null
    )

    create table #AllSeqs
    (
        SeqID int not null
    )

    create table #tmpAllSeqs
    (
        SeqID int not null,
        Similarity float not null
    )

    --- Prepare anchor 
    if @AnchorSeqID is not null
    begin
        Insert into #Subset values (@AnchorSeqID)
        set @lastSeqID = @AnchorSeqID
    end

    --- Get all SeqIDs in the alignment under the parent taxID
    Insert into #AllSeqs
    Select  SeqID
    from    dbo.fn_SelectAlignedSequences(@AlnID, @ParentTaxID, @SeqTypeID, @LocationID)


    --- Put SeqIDs into #Subset
    select @subsetActualSize = count(*) from #Subset

    while  @subsetActualSize < @SubsetSize
    begin
--      select 'Subset size:'+cast(@subsetActualSize as nvarchar(10))
        truncate table #tmpAllSeqs

        set @sql = 
        '   Insert  into #tmpAllSeqs
            select   st.SeqID2 as SeqID
                    ,st.Similarity
            from ' + @SimilarityTable + ' st
            where   st.SeqID1 = @lastSeqID
            and     st.SeqID2 in ( select SeqID from #AllSeqs )
            and     st.Similarity <= @SimilarityThreshold
        '
        set @param = '@lastSeqID int, @SimilarityThreshold float'
        exec sp_executesql @sql, @param, @lastSeqID, @SimilarityThreshold

        --- Mark the SeqID will be selected from #AllSeqs and
        --- added to #Subset in this round
        ---
        --- Here I select the SeqID with minimum difference from 
        --- mean of similarity as the SeqID  
        select  @meanSimilarity = AVG(Similarity)
        from    #tmpAllSeqs


        select  top 1 @lastSeqID = SeqID 
        from    #tmpAllSeqs
        order by    abs(Similarity - @meanSimilarity) asc

        Insert into #Subset values (@lastSeqID)

        --- Update #AllSeqs (all candidate SeqIDs)
        truncate table #AllSeqs

        Insert  into #AllSeqs
        select  SeqID
        from    #tmpAllSeqs

        --- Increment size of #Subset
        select @subsetActualSize = count(*) from #Subset

    end

    select SeqID from #Subset

    drop table #Subset
    drop table #AllSeqs
    drop table #tmpAllSeqs

Go

2 个解决方案

select SeqID from #Subset

drop table #Subset

Insert Into #resultSeqIDs select SeqID from #Subset
drop table #Subset

#1

select SeqID from #Subset

drop table #Subset

Insert Into #resultSeqIDs select SeqID from #Subset
drop table #Subset

#2