SQL Server : have stored procedure return dataset and carry out comparison

Question

I have created a stored procedure QueryProductLines @ParamProductLineId Int. This works fine and returns the results it should.

However, I now want to create an additional stored procedure FindCommonProductLines @ParamProductLineIdOne INT, @ParamProductLineIdTwo that will call the QueryProductLines stored procedure twice and carry out a comparison and return any common products.

For example:

• Product Line One:
  • Product A Product B Product C Product D Product E
• Product Line Two:
  • Product C, Product D, Product F, Product G

The stored procedure would return Product C and Product D (as they are common in both Product Lines).

Is this possible within a stored procedure only? Any outline of how this could be achieved would be greatly appreciated.

Answer 1

One approach is to call the existing stored procedure for each product line using INSERT...EXEC to capture the results of each into a temp table/variable. The table/variable schema must match that of the QueryProductLines result set, although you can ignore unneeded columns in the final query.

Below is an example of this technique.

CREATE PROCEDURE dbo.FindCommonProductLines
      @ParamProductLineIdOne int
    , @ParamProductLineIdTwo int
AS
SET NOCOUNT ON;

DECLARE @ProductLineIdOne TABLE(
    ProductName varchar(100) PRIMARY KEY
);
DECLARE @ProductLineIdTwo TABLE(
    ProductName varchar(100) PRIMARY KEY
);

INSERT INTO @ProductLineIdOne(ProductName)
    EXEC dbo.QueryProductLines @ParamProductLineId = @ParamProductLineIdOne;
INSERT INTO @ProductLineIdTwo(ProductName)
    EXEC dbo.QueryProductLines @ParamProductLineId = @ParamProductLineIdTwo;

SELECT p1.ProductName
FROM @ProductLineIdOne AS p1
JOIN @ProductLineIdTwo AS p2 ON p2.ProductName = p1.ProductName;
GO

From a performance perspective, but at the expense of code reuse, it would be more efficient to develop a specialized query to return the common products.