Stored procedure logic for select-CodePudding

I have the following procedure:

CREATE PROCEDURE sp_types
    @type varchar(100)
AS
BEGIN
    DECLARE @products table(productId int)

    IF @type = 'Merchandise'
    BEGIN
        INSERT INTO @products
            SELECT productId
            FROM dbo.product
            WHERE type = @type
    END
    ELSE IF @type = 'Electronics'
    BEGIN
        INSERT INTO @products
            SELECT productId
            FROM dbo.product
            WHERE type = @type
    END
    ELSE IF @type = 'Home'
    BEGIN
        INSERT INTO @products
            SELECT productId
            FROM dbo.product
            WHERE type = @type
    END
    ELSE
    BEGIN
        INSERT INTO @products
            SELECT productId
            FROM dbo.product
            WHERE type = @type
    END

    /* here we have logic to convert all the productids in the @products table into an XML format
    <products>
      <productId>1</productId>
      <productId>2</productId>
    ....
    ....
    ....
      <productId>100</productId>
    </products>
    */

    /* after we get the XML string, it is passed to another procedure to print out details about the products */
    EXEC sp_products_list @xml = @productXml

END /* procedure ends here */

Here's the sp_products_list procedure:

CREATE PROCEDURE sp_products_list
    @xml XML
AS
BEGIN
    DECLARE @products TABLE (productId int)

    INSERT INTO @products
        SELECT @xml.value('productId','int')
        FROM @xml.nodes('products')

    /* Select statement */
    SELECT
        a.productId, a.productName, 
        b.productRegion, b.ProductQuantity, 
        c.productSupplier
    FROM
        products a
    JOIN 
        productRegion b ON a.productid = b.productid
    JOIN 
        productSupplier c ON c.productRegion = b.productRegion
    WHERE 
        a.productId IN (SELECT productId FROM @products)

END /* procedure end */

The sp_products_list is called by many other procedures other than the sp_types procedure. I have a requirement wherein when I pass a type of 'Merchandise' to the sp_types procedure, then I need some additional columns like productSupplierRegion, productSupplierCount etc. displayed.

But for the rest of the types, I only need to display what the select statement in the sp_products_list procedure currently displays.

If I simply add the columns that I need to the select statement in the current sp_products_list procedure then they will be displayed for any type that is passed to the sp_types procedures and that is not what I want.

My solution: one of the solutions I could think of was receiving a @type variable as an input in the sp_products_list procedure and having an if-else statement for the select statement. If the type of 'Merchandise' is passed in then display the select with additional columns otherwise display the regular columns.

The problem I might face in the future with this approach is what if we want to add different types of columns for different @type variables that are passed in. In that case, I'll have to have to do multiple if-else statements for each type. I was planning on using dynamic SQL but my idea was shot down since my team is not a huge fan of dynamic SQL.

Now I'm trying to find a robust solution for this problem that might work in any scenario. Any thoughts or suggestions? Thank you!

CodePudding user response：

I suggest you rewrite sp_products_list as a table-valued function, then you can just join it with any extra columns you need

CREATE OR ALTER FUNCTION dbo.products_list ( @productId int )
RETURNS TABLE    
AS RETURN

    SELECT
        p.productId, p.productName, 
        pr.productRegion, pr.ProductQuantity, 
        ps.productSupplier
    FROM
        products p
    JOIN 
        productRegion pr ON p.productid = pr.productid
    JOIN 
        productSupplier ps ON ps.productRegion = pr.productRegion
    WHERE 
        p.productId = @productId;

GO

Then you simply use that function in each of the procedures. For example.

CREATE PROCEDURE ProductByType
    @type varchar(100)
AS

    IF @type = 'Merchandise'
    BEGIN
        SELECT pl.*, p.OtherColumns, t.OtherTablesColumns
            FROM dbo.product p
            CROSS APPLY dbo.products_list (p.productId) pl
            LEFT JOIN OtherTable t ON SomeCondition
            WHERE p.type = @type;
    END;
    ELSE
    BEGIN
        SELECT pl.*
            FROM dbo.product p
            CROSS APPLY dbo.products_list (p.productId) pl
            WHERE p.type = @type;
    END;

You may find it more performant to push the @type selection into the function also. Then you can simply do

SELECT pl.*
FROM dbo.products_list_by_type (@type) pl

The server will remove any columns that are not needed from the query plan.