sql server - Make multiple SQL into one Query -

i have table in sql server 2005 shown below

declare @promotions table (promocode varchar(10),                           deptid varchar(10),                           verticalid varchar(10),                           buildingid varchar(10)                           primary key (deptid, verticalid, buildingid)                         )  insert @promotions values ('p1_20%','101','501','1001') insert @promotions values ('p2_10%','101','501','all') insert @promotions values ('p3_5%','101','all','all') insert @promotions values ('p1_50%','111','606','1002') 

we need find out promotion code value based on matching values in deptid, verticalid , buildingid columns. if there not match these 3 columns, match input value deptid, verticalid , default value ("all") buildingid. still if there no result found, match considering input deptid, default value verticalid ("all") , default value buildingid ("all").

the following query works fine – using 3 part approach. can achieve in single sql query?

existing code

declare @promocode varchar(10)  --part 1 select @promocode = promocode @promotions deptid = @inputdeptid   , verticalid = @inputverticalid   , buildingid = @inputbuildingid  --part 2 if @promocode null  begin     select @promocode = promocode     @promotions     deptid = @inputdeptid       , verticalid = @inputverticalid       , buildingid = 'all' end  --part 3 if @promocode null  begin     select @promocode = promocode     @promotions     deptid = @inputdeptid       , verticalid = 'all'       , buildingid = 'all' end  --result select @promocode 

testing

declare @inputdeptid varchar(10) declare @inputverticalid varchar(10) declare @inputbuildingid varchar(10)  set @inputdeptid = '101' set @inputverticalid = '501' set @inputbuildingid = '3003' 

expected result

p2_10% 

you can approach "prioritization". is, possible matches , choose 1 best, using order by , top:

set @promocode = (select top 1 promocode                   @promotions                   deptid = @inputdeptid ,                         (verticalid = @inputverticalid or verticalid = 'all') ,                         (buildingid = @inputbuildingid or buildingid = 'all')                   order (case when building = 'all' 1 else 0 end),                            (case when verticalid = 'all' 1 else 0 end)                  ); 

there other ways express this. if performance issue , have indexes on columns, union all approach order by may work better ors in where clause.