October 14, 2011 at 11:47 am
I have a stored procedure called by SSRS report dataset.
The report parameter would accept two or more values from drop down list.
How Do I test the stored procedure with two or more values for the simple parameter?
For example, here is the proc:
create proc dbo.test_proc
@namenvarchar(50),
@classnvarchar(50),
....
select * from table1 where name = @name and class = @class
...
end
The SSRS parameter selects two names for the @name parameter. How do I manually call the store proc with it?
exec test_proc 'John', 'class4' -- this would work for one name only
How to calll the proc with both 'John' and 'Eric'?
Thank a lot!
October 14, 2011 at 11:49 am
If you are going to pass multiple values in a single parameter you will need to split that parameter inside your sproc. For one of the best methods of splitting your string take a look at the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 12:01 pm
Thanks. It comes from SSRS report drop down selection, I don't think i need to split strings here.
Anyone from SSRS would give me a hint?
October 14, 2011 at 12:04 pm
This article should give you a jump start. http://msdn.microsoft.com/en-us/library/aa337396.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 1:15 pm
Thanks, Sean. In the example, the parameter is used as :
soh.SalesPersonID IN (@BusinessPersonID)
Since I am using the stored procedure, how do I use (@BusinessPersonID) in my exec proc call when testing for 2 @BusinessPersonID input so that both would be accepted.
For example, exec test_proc 'Id1' is for one ID, how do I do for both Id1 and Id2 for @BusinessPersonID's parameter input?
October 14, 2011 at 1:57 pm
I've done this a lot, in the following way.
In the report definition, for the parameter of the stored proc, i specify something like:
=iif(Parameter!NameCount.Value = Parameter!Name.Count,nothing,join(Parameter!Name.Value,", ")
The parameter NameCount contains the number of items available in Name. So, if all is chosen, I pass null, to the Stored procedure, otherwise I pass a commaseparated list. (as a single string).
In the stored proc, I typically use dynamic SQL, and most commonly I use the string pretty much as it is. For strings, I fix quoting of course.
If you should happen to define a query directly in your report, you can use multivalued parameters directly:
...
where Name in (@Name)
October 14, 2011 at 2:03 pm
That looks like a pretty good approach to me. The only thing I would do differently is to use the Delimited splitter from Jeff's article. It is crazy fast and incredibly easy to work with.
Jeff - when are you going to pay me for pimping your work?? 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 2:16 pm
Can you pass in an xml data type? The xml could look something like this: <root><row name="name1" class="class1"></row><row name="name2" class="class2"></row></root>
And then you'd write your stored procedure to look something like below:
create proc dbo.test_proc
@xmldata xml
AS
begin
select t1.name,t1.class
from @xmldata.nodes('/root/row') AS T(Item)
inner join #table1 t1 on t1.name = T.Item.value('@name','varchar(50)') and T.Item.value('@class','varchar(50)') = t1.class
end
October 15, 2011 at 6:39 pm
Thank you all! Looks like the easiest way is not to define data set using stored procedure, but to define the query directly in the report so I could use "in" and don't have to do string splitting.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply