JOIN on a Table Valued Parameter - Sql Server T SQL -


i have stored procedure accepts table valued parameter. join table if has values.

create proc dbo.testproc     (@name uniqueidentifier = null,      @hascityguids bit,       @cityguids dbo.guidtabletype readonly) begin    select x1 , x2    address    (@hascityguids = 0            or a.cityguid in (select value @cityguids))  end 

the other option outer join

select      x1 , x2      address  left outer join     @cityguids cg on a.cityguid = cg.value 

is there faster option these 2 ? have complex query , had use filter on multiple places, slowing down query.

if it's issue i've had in past, it's slow because of or here , optimizer wasn't behaving , using indexes

where (@hascityguids = 0 or a.cityguid in (           select value  @cityguids ))  

instead, ended actual if statement, like

create proc dbo.testproc( @name uniqueidentifier = null,                           @hascityguids bit,                            @cityguids dbo.guidtabletype readonly) begin     if @hascityguids = 0    begin    select x1 , x2    address    end    else    select x1 , x2    address inner join @cityguids cg on a.cityguid = cg.value  end 

Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -