Can I use the keyword "if" whil using cte

Sql server 2005

The code written (snippet id = 8085622) has syntax error.

Can I use if keyword while using cte.

I can hadle the situation by using temp table but I am only interested if is it possible to use if with cte directly.

My pois is , to directly use a CTE immediately followed by an IF-ELSE construct. ...
declare @trans_no varchar(100)
;with vchr_balance as ( 
 select  debit = sum(debit) , credit = sum(credit)
     from  gl_vchr_det 
     where trans_no  = @trans_no 
)
if (select sum(debit) from vchr_balance) <> (select sum(debit) from vchr_balance)
    begin
      SET @msg = 'Voucher is not balanced? ' 
             + 'Debit amt = '  + convert(varchar,convert(money,(select sum(debit) from vchr_balance)),103)
             + 'Credit amt = ' + convert(varchar,convert(money,(select sum(credit) from vchr_balance)),103)
        RAISERROR (@msg,16,1)
end

Open in new window

MehramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I presume that you want to have an ELSE to do something else: you cannot do that.
declare @trans_no varchar(100)
declare @balance decimal(20,4)

select @balance = sum(debit) - sum(credit)
     from  gl_vchr_det 
     where trans_no  = @trans_no 

if @balance <> 0
    begin
      SET @msg = 'Voucher is not balanced? ' 
             + 'Debit amt = '  + convert(varchar,convert(money,(select sum(debit) from vchr_balance)),103)
             + 'Credit amt = ' + convert(varchar,convert(money,(select sum(credit) from vchr_balance)),103)
        RAISERROR (@msg,16,1)
    end
else
  begin
    SET @msg = 'ok'
  end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MehramAuthor Commented:
Ok I got it
This will do my job easily


In raiserror I want to show that the voucher is not balanced and what is the debit amount and credit amount

select @balance = sum(debit) - sum(credit)
, @debit = sum(debit)
, @credit = sum(credit)
     from  gl_vchr_det 
     where trans_no  = @trans_no 

if @balance <> 0
    begin
      SET @msg = 'Voucher is not balanced? ' 
             + 'Debit amt = '  + convert(varchar,convert(money,@deibt),103)
             + 'Credit amt = ' + convert(varchar,convert(money,@credit),103)
        RAISERROR (@msg,16,1)
    end

Open in new window

0
MehramAuthor Commented:
<<you cannot do that.>>

Now I am sure.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.