Basic evaluations in SELECT statement

Is there a way to determine several fields under one CASE or IF in a SELECT statement? For example, I don't think I can do this (though I'd like to):

@.Type INT

AS

SELECT

CASE @.Type

WHEN 1 THEN

Field1 = <some calculation>

Field2 = <Some calculation>

WHEN 2 THEN

Field1 = <some calculation>

Field2 = <some calculation>

END

The alternative, of course, is to evaluate @.Type twice, once for Field1 and again for Field2. But this seems like such a waste. Is there a better way?

Thanks,

One alternative might be to use an inline table -- maybe something like this:

declare @.type integer
set @.type = 2

select type,
field1,
field2
from ( select 1 as type, 1+3+5 as field1, 1*3*5 as field2 union all
select 2, 2+4+6, 2*4*6
) a
where type = @.type

/*
type field1 field2
-- -- --
2 12 48
*/

|||

Jararaca,

You are correct that a CASE can only return a single value.

So in YOUR case, you must use two CASE statements: one for Field1, and then another for Field2.

I think the speed of evaluation of the CASE usually exceeds the speed of retrieving the rows of data and/or formatting the result set.

Dan

|||

If only one condition value, How about this.

Code Snippet

If @.Type = 1

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

if @.Type = 2

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

|||

Another method of doing this if you are wanting to apply it to an entire table is something like this:

declare @.tString char(20)
set @.tString = convert(char(10), 2*4*6)
+ convert(char(10), 1*3*5)

declare @.demo table
( rid integer,
type tinyint
)
insert into @.demo
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2

select rid,
rtrim(substring (@.tString, 10*type-9, 10))
computedField
from @.demo

/*
rid computedField
-- -
1 48
2 15
3 48
4 48
5 15
*/