Setting a variable in T-SQL from a Query

Question

So I have this query:

select 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

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.

Is your query guaranteed to always return at most one row?

Martin Smith
– Martin Smith

2011-08-31 23:08:20 +00:00
Commented Aug 31, 2011 at 23:08 — Martin Smith
– Martin Smith, Commented Aug 31, 2011 at 23:08

Martin Smith · Accepted Answer · 2011-08-31 23:21:14Z

2

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,

answered Aug 31, 2011 at 23:21

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

deutschZuid · Accepted Answer · 2011-08-31 23:23:57Z

0

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

answered Aug 31, 2011 at 23:23

deutschZuid

1,0585 gold badges16 silver badges34 bronze badges

Collectives™ on Stack Overflow

Setting a variable in T-SQL from a Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related