Single SQL query with input tvp parameter when tvp doesn't contain any row

Question

Anyone knows a kind of this trick

CREATE PROCEDURE [pr_GetFinDoc]
    @id UNIQUEIDENTIFIER NULL
AS
BEGIN
    SELECT f.*
    FROM [dbo].[FinDocument] f     --id column is primary key
    WHERE @id is null or f.id = @id
END

So, the procedure above returns either single FinDoc or all FinDocs. It depends on whether the id was sent or not.

Just a single query.

So i need something like that but for tvp parameter.

It's my tvp parameter - just array of uniqueidentifier values

CREATE TYPE [dbo].[GuidList] AS TABLE(
    [Id] [uniqueidentifier] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

And my stored procedure:

CREATE PROCEDURE [pr_GetFinDoc]
    @id_list [dbo].[GuidList] READONLY
AS
BEGIN

    IF EXISTS (SELECT 1 FROM @id_list)
        BEGIN
            SELECT f.*
            FROM
            @id_list filter 
                INNER JOIN [dbo].[FinDocument] f 
                    ON f.id = filter.Id
        END
    ELSE
        BEGIN
            SELECT f.*
            FROM [dbo].[FinDocument] f
        END
END

Is there a way to change the code of SP with tvp input parameter to single query ?

Answer 1

You can use IN instead. This gives you the flexibility to use more complex logic:

select f.*
from FinDocument f
where f.id in (select id from @id_list) or
      not exists (select 1 from @id_list);

Note that these methods (and yours) are not necessarily efficient. The if logic will probably compile using an index on id. Often if the queries are complex, it is better to either force a re-compile or use dynamic SQL (and force a re-compile) because of SQL Server compiles queries the first time the stored procedure is invoked. And the query plan may not be optimal for all parameter choices.