Execute Multiple SQL Statements In Stored Procedure With Single Result Return

Question

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.

Answer 1

So you want to select one row if you have a category or all matching product rows:

You were almost there but you need to put both parts in the IF...ELSE statement.

IF EXISTS (SELECT * 
           FROM Categories 
           WHERE catalogid = @CatalogID AND source = @Manufacturer) 

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

ELSE

    SELECT '' AS categoryname, displaypartno
    FROM Products
    WHERE catalogid = @CatalogID AND source = @Manufacturer

END

Don't worry about calling it twice unless you have huge demands on this query or are doing something silly elsewhere it won't gause big performance issues.