IF: Access to SQL Server control of flow statements 2

[Abracadabra]

Could somebody please advise me how to convert the following Access expression into the necessary SQL code in a stored procedure. I have not found the SQL Server textbooks very helpful in this regard.

 quantity: IIf([sale or return]="R",-[movement qty],[movement qty])

Many thanks in advance.

 

[rajeevnandanmishra]

Hello ,
I hope following query will help you.

SELECT column(s), CASE sale = 'R' or return = 'R'
THEN -[movement qty]
ELSE [movement qty] END

 

[tlbroadbent]

If you want to store the value in a variable, here is a solution.

/* assuming [sale or return] and [movement qty] are columns in a table */

Declare @quantity int
Select @quantity=
Case [sale or return]
When 'R' Then 0-[movement qty]
Else [movement qty]
End
From tbl
Where <criteria>

If you want to select the values from a table and apply the logic in a query do the following.

Select colA, colb,
Case [sale or return]
When 'R' Then 0-[movement qty]
Else [movement qty]
End As quantity
From tbl
Where <criteria> Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost