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

Popular posts from this blog

c++ - Convert big endian to little endian when reading from a binary file -

C#: Application without a window or taskbar item (background app) that can still use Console.WriteLine() -

unicode - Are email addresses allowed to contain non-alphanumeric characters? -