How to pass multiple parameters in SQL stored procedure for 'Order By clause'
-
Hello World... I am using a SP for sorting purpose. I was sending a sort parameter dynamically and it was working fine, as... CREATE PROCEDURE SP_Inventory @SortParam VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY CASE WHEN @SortParam = 'ItemName' AND @SortOrder = 'ASC' THEN Item.ItemName END ASC, CASE WHEN @SortParam = 'ItemName' AND @SortOrder = 'DESC' THEN Item.ItemName END DESC, CASE WHEN @SortParam = 'ItemPrice' AND @SortOrder = 'ASC' THEN Item.ItemPrice END ASC, CASE WHEN @SortParam = 'ItemPrice' AND @SortOrder = 'DESC' THEN Item.ItemName END DESC END It gives me expected result. But in this case I am sending only one parameter as a sort parameter. I want to send two sort parameters. Such as... CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY ..... Now what will be the condition in Order By clause ? I tried as follows CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY CASE WHEN @SortParam1 = 'ItemName' AND @SortParam2 = 'ItemPrice' AND @SortOrder = 'ASC' THEN Item.ItemName, Item.ItemPrice END ASC, CASE WHEN @SortParam1 = 'ItemName' AND @SortParam2 = 'ItemPrice' AND @SortOrder = 'DESC' THEN Item.ItemName, Item.ItemPrice END DESC END But it is not allowing multiple column names separated with ',' after THEN Can any one help me. Thanks Ajit
-
Hello World... I am using a SP for sorting purpose. I was sending a sort parameter dynamically and it was working fine, as... CREATE PROCEDURE SP_Inventory @SortParam VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY CASE WHEN @SortParam = 'ItemName' AND @SortOrder = 'ASC' THEN Item.ItemName END ASC, CASE WHEN @SortParam = 'ItemName' AND @SortOrder = 'DESC' THEN Item.ItemName END DESC, CASE WHEN @SortParam = 'ItemPrice' AND @SortOrder = 'ASC' THEN Item.ItemPrice END ASC, CASE WHEN @SortParam = 'ItemPrice' AND @SortOrder = 'DESC' THEN Item.ItemName END DESC END It gives me expected result. But in this case I am sending only one parameter as a sort parameter. I want to send two sort parameters. Such as... CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY ..... Now what will be the condition in Order By clause ? I tried as follows CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY CASE WHEN @SortParam1 = 'ItemName' AND @SortParam2 = 'ItemPrice' AND @SortOrder = 'ASC' THEN Item.ItemName, Item.ItemPrice END ASC, CASE WHEN @SortParam1 = 'ItemName' AND @SortParam2 = 'ItemPrice' AND @SortOrder = 'DESC' THEN Item.ItemName, Item.ItemPrice END DESC END But it is not allowing multiple column names separated with ',' after THEN Can any one help me. Thanks Ajit
Hi Ajit, Instead of using CASE you could build dynamic query and then execute the query. Here is the Modified SP: Option 1: CREATE PROCEDURE SP_Inventory @SortParam1 VARCHAR(50), @SortParam2 VARCHAR(50), @SortOrder VARCHAR(5) AS BEGIN EXEC ('SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY '+ @SortParam1 + ',' + @SortParam2 + ' ' + @SortOrder) Option 2: (You can send the column names in single parameter using comma seperated values for eg @SortParam = 'ItemName, ItemPrice') CREATE PROCEDURE SP_Inventory @SortParams VARCHAR(100), @SortOrder VARCHAR(5) AS BEGIN EXEC ('SELECT * FROM ITEM WHERE CategoryId = 2 ORDER BY '+ @SortParams + ' ' + @SortOrder)