I'm trying to create a view in which i have to apply a Stored procedure to specific dollar values in the result set. I started by declaring a table variable to hold the SP results. The stored procedure paramter would be the o.ord_hdrnumber column the in the select statement, so i need to execute the stored procedure with that number then return the require values from the SP in the main view select statement.
CREATE view [dbo].[sp_salesreport_ca] as Declare @ExchangeRate TABLE (RevCan MONEY, CostCAN MONEY, GP_CAN MONEY) INSERT INTO @ExchangeRate EXEC ExchangeRate_SP @OrderNum in ( ) --I need to pass through whatever results i get from the query below into this SP. select o.ord_hdrnumber as [ord_number], o.mov_number as [mov_number], o.ord_status as [ord_status], CASE WHEN o.ord_invoicestatus='PPD' then 'Y' else 'N' END as [invoiced], convert(date, o.ord_startdate) as [ship_date], convert(date, o.ord_completiondate) as [del_date], o.ord_billto as [billto_id], c.cmp_name as [billto_name], ct1.cty_name as [origin_city], ct1.cty_state as [originstate], ct2.cty_name as [dest_city], ct2.cty_state as [deststate], o.ord_revtype3 as [office], s.ord_revtype2 as [sales_rep], o.ord_subcompany as [sales_rep_code], o.ord_bookedby as [book_rep], @ExchangeRate.RevCAN as [revenue], --I know this is all invalid, but i can't join the Table Variable @ExchangeRate.CostCAN as [cost], --declared above because there is no way to join it to @ExchangeRate.GP_CAN as [gross_profit] --ORDERHEADER from ORDERHEADER o inner join customers c on o.ord_billto = c.cmp_id inner join CITY ct1 on o.ord_origincity = ct1.cty_code inner join CITY ct2 on o.prd_destcity = ct2.cty_code_CD where o.ord_subcompany='SALESID'
I'm not really sure how to go about this, any help would be appreciated! I should be able to edit the SP, but i'd rather not if i don't have to.