Stored procedure logic for select

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!

1 answer

  • answered 2021-11-29 13:12 Charlieface

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum