EXEC in SELECT question
-
I'm trying to convert a price and return it as part of a select statement. Syntax:
SELECT
EXECUTE TicksToPrice Exchange, Price
AS TransPrice,
FROM Trade_Confirmations_Main
--------- Procedure -----
CREATE procedure TicksToPrice
@exchange nvarchar(5),
@instrument nvarchar(7),
@price money OUTPUT
as
select @price = MinTickValue /POWER(10.0, MinTickValueShift)
from CreditClassCurrencies
where
PrimaryExchangeID =@exchange
and ContractProductID =@instrument
GO
I cannot figure out how to do this and even if I can. Any help appreciated.
-
If you are running SQL Server 2000, change the stored procedure to a scalar User-Defined function.
hth,
Lynn
-
To place the value into a resultset, you need a user-defined function, not a stored procedure.
Having said that, using a user-defined function, where the function itself performs a query on another table will give you horrible, cursor-like performance. Just join to the other table:
SELECT C.MinTickValue / POWER(10.0, C.MinTickValueShift) As TransPrice
FROM Trade_Confirmations_Main As T
INNER JOIN CreditClassCurrencies AS C
ON C.PrimaryExchangeID = T.Exchange AND
C.ContractProductID = T.Price
-
This solution worked very well. Thanks for helping out a newbie.
Thanks as well Lynn
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply