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
Post a Comment