SQL Passing tables as parameters?

Question

I am performing a multiplication that requires two columns from two different tables, and then using the results in a separate query. I think this would be possible as a view:

SELECT SUM(A.salesAmt * B.sales%) AS rebateAmount

But would a table-valued function be possible here, or more appropriate? Could both table A and B be passed as parameters to return the final rebateAmount as a sum?

• Table A contains: salesID, salesAmt
• Table B contains: salesID, sales%

Would like the TVF to return the sum of (salesAmt * sales%) as rebateAmount if possible? This is SQL Server 2014.

Thanks in advance.

Answer 1

Passing a table as a parameter doesn't seems to resolve your issue.

Instead there seems to be a simpler way: -- create temp tables Create Table #a (salesID int, salesAmt decimal(15,2) ) Create Table #b (salesID int, salesPerc decimal(5,2) )

-- insert data
Insert into #a
Select 1, 2567.34
Union
Select 2, 335.57
Union
Select 3, 95.35
Union
Select 4, 303.83
Union
Select 5, 743.66

-- insert data
Insert into #b
Select 1, 4.5
Union
Select 2, 10.0
Union
Select 3, 2.5
Union
Select 4, 6.0
Union
Select 5, 20.0

-- to get the data of individual columns + the multiple of the salesAmt & salesPerc
Select a.*, b.salesPerc, convert(decimal(15,2), a.salesAmt * b.salesPerc )                        as Mult from #a a inner join #b b on a.salesID = b.salesID

-- to get the sum of the multiple of the salesAmt & salesPerc
Select Sum (convert(decimal(15,2), a.salesAmt * b.salesPerc )) as SumOfMult from #a a inner join #b b on a.salesID = b.salesID