CREATE PROCEDURE sp_Get_Inventory_Balances @PARAMETER VARCHAR(1) AS /* Answer to the below question. Jason Vollaire: 07062013. http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/3dc5b3e2-bcf8-4cec-ac7f-6743e88f4e10 */ -----------------------Declare The Parameters-------------------------------- DECLARE @ORDER1 NVARCHAR(100), @ORDER2 NVARCHAR(100), @ORDER3 NVARCHAR(100), --@PARAMETER VARCHAR(1), @ORDERBY NVARCHAR(100), @SQL NVARCHAR(MAX) -----------------------Set the Parameters------------------------------------ SET @ORDER1 = 'Part.PRODUCT_CODE, Inventory_Balance.POSTING_DATE ASC' SET @ORDER2 = 'Part.COMMODITY_CODE, Inventory_Balance.POSTING_DATE' SET @ORDER3 = 'Inventory_Balance.Posting_Date ASC' --SET @PARAMETER = '' ---------------------------------------------------------------------------- CREATE TABLE #INVENTORY (Product_Code VARCHAR(10), Posting_Date DATE, Commodity_Code VARCHAR(10)) ---------------------------------------------------------------------------- INSERT INTO #INVENTORY (Product_Code, Posting_Date, Commodity_Code) /* Use this to generate one line of "spoof" data. When adding to SSRS will create the fields you need for your table. */ SELECT '' Product_Code, '' Posting_Date, '' Commodity_Code ---------------------------------------------------------------------------- SET @ORDERBY = (CASE WHEN @PARAMETER = 'X' THEN @ORDER1 WHEN @PARAMETER = 'Y' THEN @ORDER2 WHEN @PARAMETER = '' THEN @ORDER3 END) SET @SQL = ' SELECT Inventory_Balance.Product_Code, Inventory_Balance.Posting_Date, Part.Commodity_Code FROM dw_Staging.dbo.Inventory_Balance LEFT JOIN dw_Staging.dbo.Part ON Inventory_Balance.Product_Code = Part.Product_Code ORDER BY ' + @ORDERBY + '' INSERT INTO #INVENTORY (Product_Code, Posting_Date, Commodity_Code) EXECUTE msdb.dbo.sp_executesql @SQL SELECT Product_Code, Posting_Date, Commodity_Code FROM #INVENTORY WHERE ISNULL(Product_Code,'') != '' DROP TABLE #INVENTORY Hi Dave Had a quick look, and built this. Not sure if it is best practice from a performance perspective, but seems to work well. Depending on the amount of data, you may need to tweak or not use. I added this to SSRS and worked fine. The logic is basically create a "spoof" set of data so that when you click "refresh fields" on the data set in SSRS it will create the fields for you (without the dynamic sql having to execute). When dynamic SQL does execute, then you'll get the data you're looking for based on the parameter. Not sure how you are planning to feed that in, so I worked with what I had. If it helps, then great. Regards, Jason Vollaire

Hello, I am using SQL Server 2005 and have a coding question for SSRS. What I am trying to do is to have an ORDER BY statement that contains a specific passed parameter and then it "builds" the code for the statement... Something like: ORDER BY ( CASE (When @Parameter = 'x' THEN 'PART.PRODUCT_CODE, INVENTORY_BALANCE.POSTING_DATE ASC') (WHEN @Parameter = 'y' THEN 'PART.COMMODITY_CODE, INVENTORY_BALACE.POSTING_DATE') ELSE INVENTORY_BALANCE.POSTING_DATE ASC END) No matter how I approach this, I always get some form of error back. Any suggestions? Thanks!

there might be better approach, but try this: order by (case @Parameter when 'x' then PART.PRODUCT_CODE when 'y' then PART.COMMODITY_CODE else INVENTORY_BALANCE.POSTING_DATE end), INVENTORY_BALANCE.POSTING_DATE

Hi, What error you are getting ? Try like this ORDER BY CASE WHEN @Parameter = 'x' THEN PART.PRODUCT_CODE, INVENTORY_BALANCE.POSTING_DATE ASC WHEN @Parameter = 'y' THEN PART.COMMODITY_CODE, INVENTORY_BALACE.POSTING_DATE ELSE INVENTORY_BALANCE.POSTING_DATE ASC END Try this link - Using CASE in an ORDER BY clause - http://msdn.microsoft.com/en-us/library/ms181765.aspxsathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

It does not like the comma between the two column names. " Msg 102, Level 15, State 1, Line 27 Incorrect syntax near ','. "

declare @tt table ( col1 int, col2 int, col3 int ) insert into @tt select 2,10,25 union all select 1,18,27 union all select 4,12,23 union all select 3,15,21 select * from @tt declare @param char(1) set @param = 'x' select * from @tt order by (case @param when 'x' then col1 when 'y' then col2 else col3 end),col1

CREATE PROCEDURE sp_Get_Inventory_Balances @PARAMETER VARCHAR(1) AS /* Answer to the below question. Jason Vollaire: 07062013. http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/3dc5b3e2-bcf8-4cec-ac7f-6743e88f4e10 */ -----------------------Declare The Parameters-------------------------------- DECLARE @ORDER1 NVARCHAR(100), @ORDER2 NVARCHAR(100), @ORDER3 NVARCHAR(100), --@PARAMETER VARCHAR(1), @ORDERBY NVARCHAR(100), @SQL NVARCHAR(MAX) -----------------------Set the Parameters------------------------------------ SET @ORDER1 = 'Part.PRODUCT_CODE, Inventory_Balance.POSTING_DATE ASC' SET @ORDER2 = 'Part.COMMODITY_CODE, Inventory_Balance.POSTING_DATE' SET @ORDER3 = 'Inventory_Balance.Posting_Date ASC' --SET @PARAMETER = '' ---------------------------------------------------------------------------- CREATE TABLE #INVENTORY (Product_Code VARCHAR(10), Posting_Date DATE, Commodity_Code VARCHAR(10)) ---------------------------------------------------------------------------- INSERT INTO #INVENTORY (Product_Code, Posting_Date, Commodity_Code) /* Use this to generate one line of "spoof" data. When adding to SSRS will create the fields you need for your table. */ SELECT '' Product_Code, '' Posting_Date, '' Commodity_Code ---------------------------------------------------------------------------- SET @ORDERBY = (CASE WHEN @PARAMETER = 'X' THEN @ORDER1 WHEN @PARAMETER = 'Y' THEN @ORDER2 WHEN @PARAMETER = '' THEN @ORDER3 END) SET @SQL = ' SELECT Inventory_Balance.Product_Code, Inventory_Balance.Posting_Date, Part.Commodity_Code FROM dw_Staging.dbo.Inventory_Balance LEFT JOIN dw_Staging.dbo.Part ON Inventory_Balance.Product_Code = Part.Product_Code ORDER BY ' + @ORDERBY + '' INSERT INTO #INVENTORY (Product_Code, Posting_Date, Commodity_Code) EXECUTE msdb.dbo.sp_executesql @SQL SELECT Product_Code, Posting_Date, Commodity_Code FROM #INVENTORY WHERE ISNULL(Product_Code,'') != '' DROP TABLE #INVENTORY Hi Dave Had a quick look, and built this. Not sure if it is best practice from a performance perspective, but seems to work well. Depending on the amount of data, you may need to tweak or not use. I added this to SSRS and worked fine. The logic is basically create a "spoof" set of data so that when you click "refresh fields" on the data set in SSRS it will create the fields for you (without the dynamic sql having to execute). When dynamic SQL does execute, then you'll get the data you're looking for based on the parameter. Not sure how you are planning to feed that in, so I worked with what I had. If it helps, then great. Regards, Jason Vollaire