1

I have built a Stored Procedure that takes input parameters and returns a result set so that I can consume it for Reporting Services. However, the user wanted to choose the ORDER BY columns of their choice. So, I tried something like this:

CREATE PROCEDURE [dbo].[usp_RevenueReport] 
(@ProductNumber INT, 
@ProductName VARCHAR(MAX), 
@ProductType VARCHAR(MAX), @SortColumns VARCHAR(MAX))

    SELECT [ProductNumber],
    [ProductName], 
    [ProductType]
    FROM [dbo].[Products]
    WHERE [ProductName] IN (SELECT value FROM STRING_SPLIT(@ProductName, ',')  WHERE RTRIM(value) <> '') 
    AND [ProductType] IN (SELECT value FROM STRING_SPLIT(@ProductType, ',')  WHERE RTRIM(value) <> '')
    ORDER BY (SELECT value FROM STRING_SPLIT(@SortColumns, ',')  WHERE RTRIM(value) <> '');

It is throwing an error when I pass multiple columns for sorting. How can I sort based on what I pass?

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here is my environment: Microsoft Azure SQL Data Warehouse - 10.0.10887.0 Jan 23 2020 07:36:54 Copyright (c) Microsoft Corporation

Julaayi
  • 403
  • 2
  • 8
  • 23
  • 2
    You're trying to order by a result set? That doesn't make any sense. And even if you could get that to work, its going to order by the strings passed in, not the columns themselves. The only way to allow generic ordering like that is dynamic SQL. – Dale K Feb 18 '20 at 20:40
  • @DaleK mind posting an example please? – Julaayi Feb 18 '20 at 20:42
  • @jw11432 Sorry, quite didn't get you. If I understand, just give the ability to the user all the orders and choose from them? If so, unfortunately the user wanted to select either one or more columns of their choice to sort. If I add the "Filter" it would end up with multiple permutations and combinations. – Julaayi Feb 18 '20 at 20:54
  • @DaleK Thanks for the link. I'll see if we can consider dynamic SQL. – Julaayi Feb 18 '20 at 20:57
  • 3
    If the report is fairly simple, then look at interactive sorting too, they can sort on columns once the report is rendered. – Alan Schofield Feb 18 '20 at 21:11
  • 1
    Unfortunately, I can't make an answer because people who don't actually use SSRS insist on closing questions about SSRS. You can use the Expression Function (the F(x) on the Dataset Properties) to parse your query together as text - wrapping each line with quotes. `="SELECT * FROM (" & VBCRLF &` . Then add your SORT like `"ORDER BY " & REPLACE(JOIN(Parameters!sort.Value, ","), "'", "")` – Hannover Fist Feb 19 '20 at 16:29
  • @DaleK - there's an easier way to do it in SSRS without resorting to dynamic SQL. – Hannover Fist Feb 19 '20 at 16:35
  • @HannoverFist its not a case of not using SSRS, its a case of the SO specifically asking how to do it in an SP. – Dale K Feb 19 '20 at 18:56
  • @AlanSchofield It's a simple report but when I try to add interactive sorting it is killing the report or crashes. – Julaayi Feb 19 '20 at 21:37
  • @HannoverFist Sorry, I didn't quite get that. – Julaayi Feb 19 '20 at 21:41
  • @Julaayi - I was just saying that instead of resorting to Dynamic SQL, you could use the Expression builder of the dataset in SSRS to create the text that you want with the Parameters parsed in. On the other hand, I don't think you want to convert the Sort parameter into a table with SplitString - you want a **list** of values, not a **table**. – Hannover Fist Feb 19 '20 at 22:41

0 Answers0