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.
@UserDefinedTableTypeParam confusing SQL execution plans and the query optimizer?