Hi all,

I've written a stored proc as a select query which does some numeric 
calculation and adds the results of the calcuations as separate fields, 
e.g.

Select ContractPrice, CostToDate, CostToComplete,
P=(CostToDate)/(CostToDate+CostToComplete),
EP=((CostToDate)/(CostToDate+CostToComplete))*(ContractPrice-CostToDate-
CostToComplete),......

Two questions about this:

1.  The calculations inside the SELECT, P and EP are not members of any 
included tables. They seem always to be returned as integers.Since P is 
actually the calculation of a percentage it is always a non-integer 
decimal, i.e. 86.2%.  Does anyone know a method for declaring the data 
type of a calculated field?  For the moment I have solved the problem by 
multiplying the results by 10000 to get a four digit integer and have to 
take the extra step of coverting it back as the process continues.  It 
would be more elegant certainly to be able to declare the P and EP fields 
as floats.  So #1 is Can you define the data type of a derived field?

2.  Even though P's value is calculated before EP is calculated 'P' cannot 
apparently be used as a value in the EP calculation; rather the entire 'P' 
expression must be written out specifically.  Again readability and 
maintainability would be served if P really did serve as a value as soon 
as its value was known.  Anybody got any experience or advice with this 
scenario.

Note: The SELECT statement returns a recordset which would be expected to 
contain several but an unknown number of rows so using OUTPUT values from 
the sp doesn't seem to make sense and would be extraordinaliy verbose.

Are there possibly different methods of writing this query which would 
overcome the rather obvious implication that I'm making this SELECT do 
things that aren't natural to the syntax.

There are many, many opportunities in my application to use this construct 
to extrapolate numeric values so I would greatly appreciate any help or 
advice if you have dealt with this issue.

Thanks very much in advance for any suggestions; much appreciated.

Peter