0

My stored procedures accepts table value parameters for querying data.

Usually those are custom user-defined table types for where clause. Business logic of stored procedures push me to these @tables be optional.

Sometimes there is data inside @table, sometimes there is not.

Here is way how I handle these

select 
     ... etc
from tebel ...
    ...etc
where 
     (table1.id in (select id from @UserDefinedTableTypeParam ) or (select count(*) from @UserDefinedTableTypeParam ) = 0)
     and (table2.id = @OtherIntParam or @OtherIntParam = 0)

I see that my code is ugly but I do not know is there better way to avoid using count(*) inside subquery of where clause ?

My focus is to keep performance of my T-SQL code at respectable level.
Does this @UserDefinedTableTypeParam confusing SQL execution plans and the query optimizer?

  • 1
    I use an outer join on optional table-valued parameters like this when the desired result is to filter by the table values or not filter at all. For the int parameter, you could use something like table2.id = isnull(@OtherIntParam, table2.id) (assuming a default of null for the param). – dartonw Jan 13 '15 at 21:20
  • As dartonw states, an outer join will be much cleaner and most likely perform better. Once you declare the table variable, you are not dealing with a null value anymore, but rather an empty temporary table, for all intents and purposes. – Steve Mangiameli Jan 23 '15 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.