Pass multiple rows to UDF in TSQL using PHP PDO

Question

I currently have a query in TSQL that takes in a collection of product IDs and associated prices and calculates discounted pricing. At the moment, the product IDs and initial pricing are in the database, and the query I've written joins against those items. The query sets pricing based on other products that have come before it in the query, so I can't use individual queries for the price of product one, then product two, etc. They have to be evaluated as a collection together.

I now have to refactor this discount logic into a table-valued UDF that will be used in two ways:

1. The existing approach, where it will need to perform discount pricing against a collection of IDs and pricing that existing in the DB.
2. A new approach where I will need to pass the IDs and initial pricing into the UDF from PHP (they won't exist in the DB).

My problems for each respective point are:

1. I essentially need to pass a table into the UDF. From what I've read, that means I need to create my own user-defined table type, query my product IDs/pricing in the database into that table type, and then pass as an argument to the UDF. Is that correct?
2. The new approach is where I'm really lost, as I'm not sure how to take what is basically an array of ID/price pairs in PHP (think shopping cart) and pass that to my UDF to get the resulting pricing. I could manually hack together a query that generates my user-defined table my from multiple one-line statements and UNION them together, but otherwise have no idea how to pass multiple 'rows' from PHP to a UDF.

What is the correct way of handling this?

Answer 1

Full credit to https://forums.whirlpool.net.au/forum-replies.cfm?t=2598049

Define a "User Defined Table Type" that defines the table structure to pass to the UDF, eg CREATE TYPE [PRODUCTIDPRICING] AS TABLE( PRODUCTID INT, PRODUCTPRICE NUMERIC(18,2) ) When wanting to update your pricing, your TSQL will do the following:

1. Declare a variable as the table type DECLARE @PRODUCTSTODISCOUNT PRODUCTIDPRICING;
2. Insert the data into table insert into @PRODUCTSTODISCOUNT values (1,1.23)
3. execute your UDF passing the @PRODUCTSTODISCOUNT variable