Решение:

Сама функция:
CREATE FUNCTION SalesByStore (@id int)
RETURNS TABLE AS
RETURN (SELECT *
FROM bsh_products
WHERE partID=@id)
Вызов:
ALTER PROCEDURE [dbo].[tst_Proc1]
AS
SET NOCOUNT ON
SELECT * FROM SalesByStore(4)