SQL查询帮助：从存储过程插入临时表时出错

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 个解决方案

#1

You are doing the following statements at the end of your stored procedure:

您在存储过程结束时执行以下语句:

select SeqID from #Subset

drop table #Subset

Try changing that to

尝试将其更改为

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

Then remove the INSERT command from where you execute the statement.

然后从执行语句的位置删除INSERT命令。

Since your #resultSeqIDs Temp table will still be in scope, it will hold all the values you need.

由于#resultSeqIDs Temp表仍在范围内,因此它将包含您需要的所有值。

If you are on SQL Server 2008 and above, you can return a table variables - a much better alternative to #temp tables.

如果您使用的是SQL Server 2008及更高版本,则可以返回表变量 - 这是#temp表的一个更好的替代方法。

#2

If the procedure runs okay, but inserting a record into a temporary table does not, then I'd check the roles of the user running the record insertion. The error message states read-only access.

如果过程运行正常,但是没有将记录插入临时表,那么我将检查运行记录插入的用户的角色。错误消息指出只读访问权限。

#1