Find out if the function call is the culprit by only calling it once, using a subquery like this:
SELECT in_catalog_id
,in_product_id
,vc_product_name
,vc_product_image_large
,vc_product_image_small
,mn_sale_price
,vc_product_full_name
FROM
(
SELECT d.in_catalog_id
,a.in_product_id
,a.vc_product_name
,a.vc_product_image_large
,(CASE a.bt_product_image_exist WHEN 1 THEN a.vc_product_image_small WHEN 0 THEN '/product_small_images/comingsoon1.gif' END) AS vc_product_image_small
,dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) AS mn_sale_price
,d.vc_catalog_name + ' ' + a.vc_product_name AS vc_product_full_name
FROM dbo.product a
INNER JOIN dbo.catalog_product b ON a.in_product_id = b.in_product_id
INNER JOIN dbo.website_catalog c ON b.in_catalog_id = c.in_catalog_id
INNER JOIN dbo.catalog d ON c.in_catalog_id = d.in_catalog_id
WHERE a.bt_active = @bt_active
AND b.bt_active = @bt_active
AND c.bt_active = @bt_active
AND d.bt_active = @bt_active
AND c.in_website_id = @in_website_id
AND d.in_catalog_sub_type_id = @in_catalog_sub_type_id
AND (@bt_best_sellers = 0 OR b.bt_best_sellers = @bt_best_sellers)
)
WHERE mn_sale_price > 0
ORDER BY vc_product_name