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

anyone knows kind of trick

create procedure [pr_getfindoc]     @id uniqueidentifier null begin     select f.*     [dbo].[findocument] f     --id column primary key     @id null or f.id = @id end 

so, procedure above returns either single findoc or findocs. depends on whether id sent or not.

just single query.

so need tvp parameter.

it's tvp parameter - array of uniqueidentifier values

create type [dbo].[guidlist] table(     [id] [uniqueidentifier] not null,     primary key clustered  (     [id] asc )with (ignore_dup_key = off) ) 

and stored procedure:

create procedure [pr_getfindoc]     @id_list [dbo].[guidlist] readonly begin      if exists (select 1 @id_list)         begin             select f.*                         @id_list filter                  inner join [dbo].[findocument] f                      on f.id = filter.id         end     else         begin             select f.*             [dbo].[findocument] f         end end 

is there way change code of sp tvp input parameter single query ?

you can use in instead. gives flexibility use more complex logic:

select f.* findocument f f.id in (select id @id_list) or       not exists (select 1 @id_list); 

note these methods (and yours) not efficient. if logic probably compile using index on id. if queries complex, better either force re-compile or use dynamic sql (and force re-compile) because of sql server compiles queries first time stored procedure invoked. , query plan may not optimal parameter choices.