this question may boil down simpler, still curious how close sql server / tsql can conditional where clauses (and reasoning behind why don't exist interesting).
i have stored procedure that, few parameters, takes in enumeration array (which has been accordingly translated user-defined table type mocks int array). reference data type follows:
create type myintarray table ( val int ); my stored procedure along following lines (altered more simplistic):
create procedure myproc @homeid int, @name varchar(500), @hometype_enum myintarray readonly, @country_enum myintarray readonly begin select * my_table name=@name end go what wanting additionally filter results of query based upon values of enum arrays passed in int tables, iff have values passed in (it possible tables might empty). pseudo code this:
select * my_table name = @name if((select count(val) @hometype_enum) > 0) begin , hometype in (select val hometype_enum) end if((select count(val) @country_enum ) > 0) begin , country in (select val country_enum ) end the 2 enums independent of each other, it's possible search made , filtered on no enum (both tables empty), either-or, or both enums.
my actual query involves multiple columns, tables, , unions (ugly, know), it's not nice being able copy/paste 3-line select each scenario. using pretty ugly temp table logic i'll spare reader's eyes @ moment.
aside figuring out particular problem, main question is: sql server support conditional where clause statements (i convinced not research)? why (architectural, time complexity, space complexity issues)? there more-or-less terse ways go emulating conditional clause, such taking advantage of conditional short-circuiting?
thank insights. day of learning!
as suggested in comments, best way handle kind of conditional clause use dynamic-sql ..... like....
create procedure myproc @homeid int, @name varchar(500), @hometype_enum myintarray readonly, @country_enum myintarray readonly begin set nocount on declare @sql nvarchar(max); set @sql = n' select * my_table ' + n' name = @name ' + case when exists (select * @hometype_enum) n' , hometype in (select val hometype_enum) ' else n' ' end + case when exists (select * @country_enum) n' , country in (select val country_enum ) ' else n' ' end exec sp_executesql @sql ,n'@homeid int , @name varchar(500), @hometype_enum myintarray, @country_enum myintarray' ,@homeid ,@name ,@hometype_enum ,@country_enum end go using sp_executesql allow sql server store parameterised execution plans same stored procedure. different execution plans different sets/combinations of parameters same stored procedure optimal performance.
