How to call a stored procedure using table values when scalar is expected-CodePudding

So I'm not an SSRS guru, but I've inherited this SSRS report from someone who is better with SQL. Basically, in SSRS there are parameters at the top of the report, and you can select one, several, or all options in this table of string values.

I'm trying to manually execute the SP, but it's expecting scalar values for something that is not scalar in the SSRS front end. There has to be some way to wrap this up other than one-by-one every possible combination of single selections.

I'll post some code if needed, but I thought this was very straight forward.

• SSRS front end has parameter options for the report

• SP for this report is expecting only one value at a time

EDIT: This is how the SP is doing it for one of the variables:

DECLARE @IBA_list TABLE (IBA VARCHAR(20))
INSERT @IBA_list (IBA) SELECT DISTINCT * from  dbo.splitstring(@IBA)

But the problem is that I have to SELECT * FROM IBA_table to get these values, and in this way, I cannot use the splitstring function.

I've tried just passing the values, and I just get a scalar exepected issue, which makes total sense.

CodePudding user response：

Typically, if you have a multi-valued parameter then the SQL statement in the dataset query might contain a where clause that uses IN. e.g.

SELECT * FROM Employees WHERE Department IN (@dept)

If the users chooses, say "accounts" and "sales" then SSRS injects this into the statement, you can see this if you run a trace on the server. So what will actually get executed on the server will be

SELECT * FROM Employees WHERE Department IN ('accounts', 'sales')

If you run the trace you'll see the query that actually gets used and how the parameters are passed, but the simplest approximation would be something like

declare @paramValues varchar(100) = '''sales'',''accounts'''
declare @sql varchar(max)
set @sql = replace('SELECT * FROM Employees WHERE Department IN (@dept)', '@dept', @paramValues)
exec (@sql)