在这个SQL存储过程中真的需要临时表吗？

In taking a look at some existing sprocs in a project I was brought into, I am seeing things like this:

在看一下我参与的项目中的一些现有的sprocs时,我看到的是这样的事情:

declare @tmpTable Table (
    ID bigint,
    patientID   uniqueidentifier,
    resultDate  datetime,
    resultParameter nvarchar(50),
    resultValue decimal(18, 6),
    resultUnit  nvarchar(50),
    pos smallint,
    minpos smallint,
    maxpos smallint
)

if (@patientID is not null)
    begin
        declare @minpos smallint = 0;
        declare @maxpos smallint = 0;

        select 
            @minpos = min(a.pos),
            @maxpos = max(a.pos)
        from tbl_patientBiometricResults a (nolock)
        where a.patientID = @patientID

        insert into @tmpTable
        (
            ID,
            patientID,
            resultDate,
            resultParameter,
            resultValue,
            resultUnit,
            pos,
            minpos,
            maxpos
        )
        select 
            a.ID, 
            a.patientID,
            a.resultDate,
            a.resultParameter,
            a.resultValue,
            a.resultUnit,
            a.pos,
            @minpos,
            @maxpos
        from tbl_patientBiometricResults a (nolock)
        where a.patientID = @patientID

    end

select * from @tmpTable order by pos;

The thing that stands out for me here is that they are using temp tables, and I really don't see the benefit in this for this type of sproc. There are no new fields added in the temp table (no combination of tables), only a subset of the fields available in the tbl_patientBiometricResults table.

我这里最突出的是他们正在使用临时表,而我真的没有看到这种类型的sproc的好处。临时表中没有添加新字段(没有表的组合),只有tbl_patientBiometricResults表中可用字段的子集。

Is there a benefit to using a temp table in this case?

在这种情况下使用临时表是否有好处?

---

2 个解决方案

#1

As Sean mentioned in a comment, there are no benefits to using your table variable. You could easily rewrite the query in a simpler format like so:

正如Sean在评论中提到的那样,使用表变量没有任何好处。您可以轻松地以更简单的格式重写查询,如下所示:

IF (@patientID is not null)
BEGIN
    SELECT  ID, 
            patientID,
            resultDate,
            resultParameter,
            resultValue,
            resultUnit,
            pos,
            minPos, --MIN(pos) OVER (PARTITION BY NULL) /*Alternative for SQL 2012+. No need for CROSS APPLY*/
            maxPos  --MAX(pos) OVER (PARTITION BY NULL)
    FROM tbl_patientBiometricResults
    CROSS APPLY(
                    SELECT  MIN(pos) minPos,
                            MAX(pos) maxPos
                    FROM    tbl_patientBiometricResults
                    WHERE   patientID = @patientID
                )
    WHERE patientID = @patientID
    ORDER BY pos;
END

#2

Well it does seem overkill out of context, but I have found that under certain scenarios for example involving SSIS you may need to construct a table variable like this in order for the SSIS package to recognize the metadata the stored procedure returns. Not sure if that matters in your case though!

好吧它看起来有点矫枉过正,但我​​发现在某些情况下例如涉及SSIS你可能需要构造一个像这样的表变量,以便SSIS包识别存储过程返回的元数据。不确定这在你的情况下是否重要!