Sql server - "Map" function?

Question

I have a stored procedure that takes a product_id and returns some data about the product. I would like to make a query or procedure that maps this stored proc over a "select * from products" query. Is there a way to do this?

Answer 1

It sounds like you need a user-defined function and not a stored proc. A user-defined function can be evaluated as part of a query (e.g. SELECT id, myFunc(id) AS 'Product Info' FROM Products).

See CREATE FUNCTION in SQL Server BOL

By the way, stored procs are permitted to be non-deterministic while UDFs are not. If your current stored proc is non-deterministic, you'll need to do so refactoring/redesiging in order to create UDFs that can be included in your query.

Answer 2

Your stored procedure takes a product_id as an argument, and returns info for a single product. And what you want to do is use that stored procedure in a query so you can get a set of results, is that correct?

To do that you will have to either use a View (if it's simple enough to do so) or more than likely rewrite the stored procedure as a User-Defined Function. If you have a UDF called GetInfoByProductId( @product_id ) that returns a bunch of info on a single product, then you can write,

select product_id, dbo.GetInfoByProductId( product_id ) from products where ...

Answer 3

I suppose you could define an inline table-valued function and use it in a Cartesian-product style JOIN against your products table. That kind of join would mimic the "Map" behavior you're talking about.

SELECT d.*
FROM products p JOIN getProductData(p.product_id) d

See Inline User-Defined Functions.

Answer 4

Another approach could be to create a VIEW that simplifies the stored procedure(s) and lets you reuse the dataset across two different procedures.

Your view would be responsible for doing all the JOINs and getting the primary and supporting columns together.

You could have one stored procedure that takes in product_id and would return a record set for that one product. This procedure would have something like select * from [viewname] where product_id = @product_id.

The other procedure would return all. This procedure would look like select * from [viewname].