Local variables in a view

Hello everyone. I need to set the value of a column from the value of another column in a view, like so:

create view vBoatMetrics
as
select PK_BID,
CASE when(var1 > 1) then A else B end as Column1Val
CASE when(var2 > 1) then C else D end as Column2Val

This part works fine, but when I try to:

declare @Variable float
set @Variable = (Column1Val*Column2Val),
Column3Val = @Variable

I get errors. Can someone please show me how to create a local variable in a view so I can carry the value throught the view? I have several calculations in a view that need to be based on the outcomes of other calculations and so forth. I would GREATLY appreciate some help!

MANY TIA,
Brandon

Have you tried something like this?

Yes. I get an Error 207: Invalid column name 'Column1Val', invalid column name 'Column2Val'

post your table structure and some sample data.

Ok, only one table. P and E are boat measurements in feet.

tbl_Boats
---------
PK_BID int, id, not null
P decimal 5,2
E decimal 5,2

So, we would have

PK_BID P E
1 10.5 12.78
2 9.76 11.97

So, in my view I'm performing some caluclations to turn feet into meters:
(P*.3048) AS Pm
(E*.3048) AS Em

But when I try to say
(Pm*Em) AS Something

I get the error. Very frustrating! It works in Access, but not SQL Server.

- - -

How about this?


Returns this

More detailed would be this

Which displays this

Ugh. So I'm going to have to go through each calculation before I can arrive at my total instead of being able to call a local variable that represents that total?

Views don't allow the use of variables, stored procedures do.
but you would still have to do this

So can I get a value from a stored procedure and pull it into a view as a column?

If you need to do it that way, then just create a stored procedure instead of a view.

I'm not very familiar with stored procedures at all. How would I get the values out of the stored procedure so I could display them on an ASP page? Can I use a stored procedure as a datasource to create a recordset (or whatever the stored procedure's equivalent to a recordset is)?

Actually what I ended up doing was creating user-defined functions to perform the calculations. Then, starting from the outside and working in, nested the functions within other functions in my view to create the calculations that I needed. Works like a charm! I didn't know I could create a column as the outcome of a function in a table, but you can, which helps my situation tremendously. This way I only have one view that returns all of the calculations.

Thanks for your help, it got me thinking in the right direction!
Brandon