convert to UDF from select statement

Question

How would one go about converting this into a scalar UDF? I'll pass the product_id to the UDF.

Running SQL 2k5

 SELECT 
 sum(qty) as qty,
  product_id
  FROM  vProductQuantity
 WHERE 
 product_id = @product_id
 GROUP BY product_id

Answer 1

Edit Undeleted in case you decide to go this route...

   CREATE FUNCTION [dbo].[products]
        (
        @product_id int
        )
    RETURNS TABLE
    AS RETURN
        (
    SELECT 
     sum(qty) as qty,
      product_id
      FROM  vProductQuantity
     WHERE 
     product_id = @product_id
    )

Answer 2

Here is how you would create the UDF so that it will return the integer value of product quantity.

CREATE FUNCTION getProductQuantity (
   @product_id int
   )   
RETURNS int  
AS  
BEGIN  
  DECLARE @Return int
  SELECT   @Return = sum(qty) as qty   FROM  vProductQuantity  WHERE   product_id = @product_id  GROUP BY product_id

  RETURN @return  
END

As Martin answered, create it as a table valued function to return multiple values.

Answer 3

I strongly recommend you not use scalar UDFs for any significant processing. For individual calls, it's not bad, but calling it for any significant number of rows generally performs very poorly.

If you were to call this for multiple products in some other rowset the UDF will not scale well.

A view or inline table-valued function will perform far better: i.e.

CREATE VIEW vQuantity AS
SELECT 
 sum(qty) as qty,
  product_id
  FROM  vProductQuantity
 GROUP BY product_id

and

SELECT *
FROM t
LEFT JOIN vQuantity ON vQuantity.product_id = t.product_id

or

CREATE FUNCTION udfQuantity(any params) RETURNS TABLE AS
RETURN (
    SELECT 
     sum(qty) as qty,
      product_id
      FROM  vProductQuantity
     GROUP BY product_id
)

and

SELECT *
FROM t
LEFT JOIN udfQuantity(any params) AS Quantity ON Quantity.product_id = t.product_id

I strongly recommend you review the execution plan of any process where you use a scalar UDF.