Multi-value params to SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Multi-value params to SP

Author

Topic

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 01:05:00

I need to pass multi-value parameters to a stored procedure. What are the options on how to do it?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-07 : 01:09:27

http://www.sommarskog.se/arrays-in-sql-2005.html

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 01:52:03

Thanks Sunil - a very good round-up. I assume I will also need something like a join for my SP call in SSRS?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 02:14:12

Created another param with "join(Parameters!safety_topic_type_id.Value)" as its default value.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 23:00:28

Param with "join(Parameters!safety_topic_type_id.Value)" isn't working

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 23:02:11

When Parameters!safety_topic_type_id.Value is changed, this is not reflected in the second dependent parameter. Is this expected behaviour?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-07 : 23:03:16

I guess there is multiple value option in Report-Parameter option

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 23:33:42

Yes- multi-value is on for the first param(1st). Initial values are set correctly, but it doesn't update the dependent(2nd) param if I change the 1st.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-07 : 23:54:14

I checked this on another example & it does seem to be expected, so now I am back to the problem of how do I include a multi value param in the SP call from SSRS?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 14:36:16

quote:
Originally posted by dexter.knudson

I checked this on another example & it does seem to be expected, so now I am back to the problem of how do I include a multi value param in the SP call from SSRS?

either use string comparison method using LIKE operator
or make use of UDF to parse the comma seperated values to table row values and then join onto it in your query.
suppose if your parameter is @ListParam then you can use like this

using LIKE
-------------

SELECT fields
FROM yourTable
WHERE ',' + @ListParam + ',' LIKE '%,' + CAST(yourcolumn as varchar(length)) + ',%'

cast it only if its of non character type.

UDF method
----------
use a UDF to parse values to table (you can find some of them inside forums itself)

SELECT fields
FROM yourTable
WHERE yourcolumn IN (SELECT Val FROM dbo.ParseValues(@ListParam))

SELECT fields
FROM yourTable t
INNER JOIN (SELECT Val FROM dbo.ParseValues(@ListParam)) tmp
ON tmp.Val=t.yourcolumn

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-10 : 23:17:43

Thanks Visakh. Yes SSRS can interpret IN (@MultiValueParam) as a clause in the SQL data set, but what I was hoping to do as a data set was
exec myStoredProcedure @paramA, @MultiValueParam, @paramB
and I haven't found a way to make this work. I ended up just pasting the SQL into the data set & used the IN clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 00:15:38

quote:
Originally posted by dexter.knudson

Thanks Visakh. Yes SSRS can interpret IN (@MultiValueParam) as a clause in the SQL data set, but what I was hoping to do as a data set was
exec myStoredProcedure @paramA, @MultiValueParam, @paramBand I haven't found a way to make this work. I ended up just pasting the SQL into the data set & used the IN clause

this should work. why whats the error you got when you tried with above exec?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-11 : 01:52:09

Too many parameters error, because @MultiValueParam is actually an array. SSRS seems to have processing that converts the array for the IN (@MultiValueParam), but when it does this conversion for the SP call, they become extra parameters. If you look at it in the profiler, with the IN clause it looks something like this:
exec sp_executesql 'SELECT ....WHERE employee_id IN (N''0052639'',N''060512'') ...', @job_no=N'BEG601', @package_id=NULL etc
So it takes the params & puts it in the sql. It also tries to do this with the SP call, but they become extra parameters:
exec sp_executesql N'exec my_sp
@login_id,@job_no,@package_id,@contractor_name,N''MCMAINTENA'',N''MKPLANT'',N''MWINDUSTRI'',N''MJCAIRNS'',N''MLMTRANSPO'',N''MAROOFING'',N''MACDONNELL'',N''MACDOWABIJ'',N''SWM'',N''TBX'',N''0584'',N''2588'',@trainer_id,N''0052639'',N''0060512'',N''0043497'',N''0056847'',N''0051121'',N''0045645'',N''0054389'',N''0038493'',N''0054219'',@task_team,@sub_task_team,@reports_to_id,@date_completed_from,@date_completed_to,@suspended,@terminated,@demobilised',N'@login_id
nvarchar(8),@job_no nvarchar(6),@package_id nvarchar(4000),@contractor_name nvarchar(4000),@trainer_id nvarchar(4000),@task_team
nvarchar(4000),@sub_task_team nvarchar(4000),@reports_to_id nvarchar(4000),@date_completed_from nvarchar(4000),@date_completed_to
nvarchar(4000),@suspended nvarchar(4000),@terminated nvarchar(4000),@demobilised
nvarchar(4000)',@login_id=N'xxxxxx',@job_no=N'BEG601' ..etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 03:05:33

why are using exec inside sp_executesql?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-11 : 03:27:27

That's how SSRS processes it. In the data set, I just have:
exec my_sp @login_id,@job_no,@package_id,@contractor_name,@employer_id,@safety_topic_type_id,@safety_topic_id,@trainer_id,@employee_id,@task_team,@sub_task_team,@reports_to_id,@date_completed_from,@date_completed_to,@suspended,@terminated,@demobilised

SSRS does some processing on this before it hits the database & turns it into what I have pasted-in above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 03:57:54

quote:
Originally posted by dexter.knudson

That's how SSRS processes it. In the data set, I just have:
exec my_sp @login_id,@job_no,@package_id,@contractor_name,@employer_id,@safety_topic_type_id,@safety_topic_id,@trainer_id,@employee_id,@task_team,@sub_task_team,@reports_to_id,@date_completed_from,@date_completed_to,@suspended,@terminated,@demobilised

SSRS does some processing on this before it hits the database & turns it into what I have pasted-in above.

sorry i didnt get you. what processing? As i understand what you need to do is to pass a comma seperated list as param value and just right code in sql to handle it as i posted earlier. I havent noticed any other processing done by SSRS in between yet. Am i missing anything?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-11 : 06:10:55

Yes. I think you may be missing something. If you would like to investigate it, try setting up a trace in SQL Server Profiler & then run one of your reports & then look at what runs in the profiler & you should see what I mean. I was running the reports from VS & not the Report Server, but I assume they would both behave the same way in relation to this.
What you should see is that it wraps "exec sp_executesql" around the SQL from your dataset.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 06:50:32

quote:
Originally posted by dexter.knudson

Yes. I think you may be missing something. If you would like to investigate it, try setting up a trace in SQL Server Profiler & then run one of your reports & then look at what runs in the profiler & you should see what I mean. I was running the reports from VS & not the Report Server, but I assume they would both behave the same way in relation to this.
What you should see is that it wraps "exec sp_executesql" around the SQL from your dataset.

ok. i will try this out and let you know my observations. Thanks.

Jmkoon
Starting Member

2 Posts

Posted - 2008-08-22 : 11:57:50

I am experiencing the exact same problem with trying to use multi select on a parameter passed to a stored procedure in SSRS. Did you ever come up with a solution?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-22 : 21:08:34

If you are passing your parameters from an application to report viewer, for example, & the user doesn't enter them into RS, then the extra param with the join (see above) will work. Otherwise, no, sorry. I have seen billions of posts on how to manage multi-value params in a stored procedure call, but you have to have a single variable which is a list first. I am still looking & trying things. Will post back when I have found something.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-24 : 17:53:46

Finally found what I was doing wrong! Now I know why others couldn't get that I couldn't get it.
When you create a new dataset, on the pop-up, there is an option for 'Command type'. This must be "StoredProcedure". Once this has been selected, enter only the stored procedure only name with no paramaters in the query string section (ie instead of "Exec MyStoredProc @multiValueParam", just enter "MyStoredProc"). Then click OK, so you go back to the data tab. Then click the "..." next to the DataSet name to open the data set again. Then click the "Parameters" tab again, and voila, your paramater is there. Then you just need the processing described here: http://www.sommarskog.se/arrays-in-sql-2005.html to handle the list which is passed to your stored procedure. Happy to clarify further if anyone needs more help.
Cheers

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources