Select SP value into query

Transact-SQL
1

Hey all!

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.

2

Just checking: accuracy of the report is not important and OK to have some rows missing and some rows present twice ?

Sorry, but you can't (not that I know of anyway). You could call a FUNCTION from a View's Definition, but not an SProc.

(Ideally best to, also, avoid scalar functions as they tend to have poor performance /scalability)

You also can't declare parameters and INSERT INTO a TEMP table and so on. within a VIEW

You could do all that in a Stored Procedure, and the SProc could return a resultset very similar to a VIEW - maybe that would do?

3

P.S.

Best Practice is not to name Stored Procedures (I know this is intended to be a VIEW ...) with the prefix "SP_" as that is reserved for SQL itself. When you EXEC an SProc with a name starting with "SP_" then SQL will look in multiple places for the SProc, which is slower than just looking in the current DB, so best to use a different prefix for the name.

4

how often does exchange rate change?
do you need to keep track of historical exchange rates?
is exchange rate per country per monetary value etc , or is this just local exchange or international

5

Doesn't help in this scenario, but for this particular comment you can do

FROM ORDERHEADER AS O
    JOIN @ExchangeRate AS E
        ON 1=1
    ...

which would mean that you have the columns in @ExchangeRate available in your SELECT statements. This will be a Cartesian JOIN - so every row in @ExchangeRate will join to every row in ORDERHEADER, so it usually only makes sense to do this if you only have one row in @ExchangeRate.

There are other styles of Cartesian JOIN, some people probably won't like my "ON 1=1" style ... so if you go down that route you might want to pick a different style!!

6

Ignore the with(NOLOCK), i just copied it from somewhere else but i won't be using it.

I'll see if it's possible to create it as a function. Thanks for the tip!

7

sp_salesreport_ca is actually the name of the view; sp stands for Sharepoint :stuck_out_tongue:

8

You just lit a lightbulb over my head; I just remember i can just join our Exchange rates table log ( We insert new values daily and apply the monthly average to the $ values for revenue, cost and profit.

Thank you! I think i have this covered now..i always overthink things and jump the gun with my posts!

9

cool, just add me to the profit beneficiaries list.

10

Excellent :slight_smile: