sql - More efficient double coalesce join alternative -
i have procedure (slightly more complex) version of below:
create proc sp_find_id ( @match1 varchar(10), @match2 varchar(10) ) declare @id int select @id = id table1 match1 = @match1 , coalesce(match2,@match2,'') = coalesce(@match2,match2,'') select @id id
essentially match1 mandatory match, match2 both optional on input procedure, , on table being searched. 2nd match succeeds input and/or table match2 values null, or they're both same (not null) value.
my question is: there more efficient (or more readable) way of doing this?
i've used method few times, , feel sullied each time (subjective dirtiness admittedly).
is there more efficient (or more readable) way of doing this?
the example provided, using coalesce/etc non-sargable. need separate things needs present in query run:
declare @id int if @match2 not null begin select @id = t.id table1 t t.match1 = @match1 , (t.match2 = @match2 or t.match2 null) end else begin select @id = t.id table1 t t.match1 = @match1 end select @id id
if want occur in single sql statement, dynamic sql real alternative. highly recommend reading the curse , blessing of dynamic sql before reading further:
declare @sql nvarchar(max) set @sql = n' select @id = t.id table1 t t.match1 = @match1 ' set @sql = @sql + case when @match2 not null ' , (t.match2 = @match2 or t.match2 null) ' else ' ' end begin exec sp_executesql @sql, n'@id int output, @match1 varchar(10), @match2 varchar(10)', @id, @match1, @match2 end
Comments
Post a Comment