sql server - SQL IF to Set Parameter Value Within SELECT Statement -

edit: problem has been solved using better method. rather using variable concatenating results desired output.

i trying values several fields couple of different tables , works fine. have "@description" concatenation of hard coded text based on existing values each row.

declare @description varchar(1000) = ''  select t1.id       t1.mytype       t1.myname       ,case when        (           t1.avalue1 = 1            , t1.avalue2 = 1           , t2.avalue1 = 1           , t2.avalue2 = 1       )          'eligible'          else 'not eligible'        end 'iseligible'        ,@description        -- i'm trying set description        /*       if               (             t1.avalue1 = 2           )       set @description = @description + ' t1.avalue1 2'       if           (             t1.avalue2 = 2           )       set @description = @description + ' t1.avalue2 2'           if           (             t2.avalue1 = 2           )       set @description = @description + ' t2.avalue1 2'           if           (             t2.avalue2 = 2           )       set @description = @description + ' t2.avalue2 2'               */       end 'description'    [dbo].[table1] t1   join [dbo].[table2] t2 on t1.id = t2.id 

so example, if row had t1.avalue1 = 2 , t2.avalue1 = 2 output might this.

id | mytype | myname | iseligible | description ----------------------------------------------- ...  24 | red | john | not eligible | t1.avalue1 2 t2.avalue1 2 25 | blue | eric | eligible |  etc...  

i using ssms 2008 r2. how accomplish this?

you have variable, expected results looks want row? can row using case, this:

 end iseligible,  case when t1.avalue1 = 2 't1.avalue1 2 ' else '' end +  case when t1.avalue2 = 2 't1.avalue2 2 ' else '' end +  case when t2.avalue1 = 2 't2.avalue1 2 ' else '' end +  case when t2.avalue2 = 2 't2.avalue2 2 ' else '' end description