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 */
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 */
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
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!
I suggest you rewrite
sp_products_listas 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
@typeselection 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.