如何将内连接的存储过程代码改进为可能为空的 tvp [英] How to improve the code of stored procedure with inner join to possible empty tvp

问题描述

我想改进以下存储过程的代码.我想将它加入单个选择语句.你能找到更好的方法吗?

I want to improve the code of the following stored procedure. I want to join it into single select statement. Can you purpose a better way?

CREATE PROCEDURE [dbo].[pr_FinDocument_Filter]
  @finDocIdForFilter [dbo].[GuidList] READONLY,
  @filteredSid nvarchar(64),
  @filteringOffsetInDay int
AS
BEGIN

    IF (@filteredSid is null or @filteringOffsetInDay is null)
        BEGIN
            RAISERROR(N'arguments must have a value', 15, 1);
        END

    IF EXISTS (SELECT 1 FROM @finDocIdForFilter)
        BEGIN
            SELECT fin_doc_extra.docId
            FROM
                [CpsOther].[dbo].[FinDocumentExtra] AS fin_doc_extra
                INNER JOIN @finDocIdForFilter       AS fin_doc_for_filter
                    ON fin_doc_extra.docId = fin_doc_for_filter.Id
                    AND fin_doc_extra.sid = @filteredSid
            WHERE
                DATEDIFF(DAY, CONVERT(DATE, fin_doc_extra.value, 105), CONVERT(DATE, GETDATE(), 126)) = @filteringOffsetInDay
        END
    ELSE
        BEGIN
            SELECT fin_doc_extra.docId
            FROM
                [CpsOther].[dbo].[FinDocumentExtra] AS fin_doc_extra
            WHERE
                fin_doc_extra.sid = @filteredSid
                AND DATEDIFF(DAY, CONVERT(DATE, fin_doc_extra.value, 105), CONVERT(DATE, GETDATE(), 126)) = @filteringOffsetInDay
        END

END

推荐答案

我认为将 2 个选择合并为一个没有必要改进您的代码.为了进行比较，这里有一个示例，您可以如何做到这一点.我们有 1 个查询，但它更复杂，可读性较差.

I think that merging 2 selects into one will not necessary improve your code. For comparison, here is an example how you can do that. We have 1 query but it is more complex and can be perceived as less readable.

以下查询基于您的第二个查询.我通过向 WHERE 语句添加附加条件对其进行了修改.如果 @finDocIdForFilter 为空或 @finDocIdForFilter 包含匹配的行，则将满足此额外条件.

The query below is based on your your second query. I modified it by adding an additional condition to WHERE statement. This extra condition will be satisfied if @finDocIdForFilter is empty or if @finDocIdForFilter contains matching rows.

SELECT fin_doc_extra.docId
FROM
    [CpsOther].[dbo].[FinDocumentExtra] AS fin_doc_extra
WHERE
    fin_doc_extra.sid = @filteredSid AND
    DATEDIFF(DAY, CONVERT(DATE, fin_doc_extra.value, 105), CONVERT(DATE, GETDATE(), 126)) = @filteringOffsetInDay 
    AND
    (
        NOT EXISTS(SELECT 1 FROM @finDocIdForFilter) 
        OR 
        EXISTS(
            SELECT 1 
            FROM @finDocIdForFilter AS fin_doc_for_filter
                ON fin_doc_extra.docId = fin_doc_for_filter.Ida.AssetCode)
    )

这篇关于如何将内连接的存储过程代码改进为可能为空的 tvp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！