Setting a variable in T-SQL from a Query

So I have this query:

select ens_use_n开发者_Python百科ew_models_bit from cfo_transaction
inner join dbo.cfo_trans_entity_rel on te_tr_transaction_id=tr_transaction_id
inner join cfo_tran_quote on tq_tr_transaction_id = tr_transaction_id
left outer join cfo_engine_sponsor on ens_rs_sponsor_id = te_co_re_entity_id
where te_rv_rel_type_id=713 and tq_tran_quote_id = 3

It returns a bit value, which can also be NULL. I hardcoded 3 for testing but in reality another proc passes this value in, but that's not important here.

Now, in a stored proc, I need to set a variable that's declared in the proc:

SET @vRtn = NULL

as the string - either 'VBEngines' or 'WFModels', or keep it NULL if the bit from above returns NULL.

'VBEngines' if the bit is off, 'WFModels' if the bit is on.

Then after that, I need to perform a T-SQL condition on the value, to see if it's NULL or not. How would I do this? I'm so bad with SQL.

Thanks.

Assuming that you don't need the bit value itself stored in a variable as that was just a means to an end then this should do it.

select @vRtn = case ens_use_new_models_bit
                 when 0 then 'VBEngines'
                 when 1 then 'WFModels'
               end /*Implicit Else NULL case*/
from   cfo_transaction ...

In the case 0 rows are returned no assignment is made so @vRtn keeps its initial value,

declare @newmodelsbit bit, @vRtn varchar(10)

select @newmodelsbit = ens_use_new_models_bit from cfo_transaction
inner join dbo.cfo_trans_entity_rel on te_tr_transaction_id=tr_transaction_id
inner join cfo_tran_quote on tq_tr_transaction_id = tr_transaction_id
left outer join cfo_engine_sponsor on ens_rs_sponsor_id = te_co_re_entity_id
where te_rv_rel_type_id=713 and tq_tran_quote_id = 3

if @newmodelsbit is null 
begin
 set @vRtn = null
end 
else
begin
 if @newmodelsbit = 1 --bit is on
 begin
   set @vRtn = 'WFModels'
 end
 else -- bit is off
  begin 
   set @vRtn = ' VBEngines'
  end 
 end