[SOLVED] Execute Multiple SQL Statements In Stored Procedure With Single Result Return

Execute Multiple SQL Statements In Stored Procedure With Single Result Return

I am looking to return a single set of data from my stored proceedure, but the result are returning just the first of the two sets. How do I return just one set of data from the following:

SELECT TOP 1 categoryname, displaypartno
FROM Categories
WHERE catalogid = @CatalogID AND source = @Manufacturer
ORDER BY categoryid DESC

IF @@RowCount=0
BEGIN
    SELECT '' AS categoryname, displaypartno
    FROM Products
    WHERE catalogid = @CatalogID AND source = @Manufacturer
END

Because I need the second SQL to execute only if the first returns no rows, I don't think I can use a UNION.

Solution

SELECT TOP 1 categoryname, displaypartno
FROM (
  SELECT categoryname, displaypartno, 0 AS ResultPriority
  FROM Categories
  WHERE catalogid = @CatalogID AND source = @Manufacturer
  UNION ALL
  SELECT '' AS categoryname, displaypartno, 1 AS ResultPriority
  FROM Products
  WHERE catalogid = @CatalogID AND source = @Manufacturer
) t
ORDER BY ResultPriority, categoryid DESC