I had posted this to SO without a bite and am lost how as to how best achieve the needed result.
I currently have a query in TSQL/MSSQL 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 dynamically generated query string 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.
I didn't find the right solution from the internet.
Сообщение отредактировал JensenBreck: 17 Ноябрь 2017 - 12:08