Cacher is the code snippet organizer for pro developers

CREATE FUNCTION 
      GetProductInfoByID(@ProductID INT)
RETURNS TABLE
AS
    RETURN
      (SELECT ProductID,
              Name,
              ProductNumber,
              ProductSubcategoryID
       FROM   Production.Product
       WHERE  ProductID = @ProductID) 

SELECT * FROM GetProductInfoByID(1);

-- Usage
SELECT SO.SalesOrderID,
       SO.SalesOrderDetailID,
       SO.CarrierTrackingNumber,
       SO.UnitPrice,
       SP.ProductID,
       SP.Name
FROM   [Sales].[SalesOrderDetail] SO
       CROSS|OUTER APPLY 
           dbo.GetProductInfoByID(So.ProductID) SP