June 24, 2016 at 7:20 am
I am a R.S. noobie. Another guy here develops in it a bit, so we need "RS for Dummies " help.
We want to have a pull down list of U.S. State codes to select, and pass that list to a stored procedure. The SP will then do a select based on state codes. Could be 0 codes entered, meaning don't select on state, or 1-50 states selected.
He has the pull down list coded, but we can't figure out what format it's parameter will be sent to the SP. Do I need 50 parameters ? State1, state2, state3 ?
What's the best approach for this - passing an unknown number of values to a SP from RS ?
Is there a way to output logging in RS to view how it formats the parameters ?? Similar to a SQL trace or such ?
June 24, 2016 at 7:32 am
If it is a multi select drop down then the parameters will be sent to the stored procedure as a comma separated string.
Depending on what this stored procedure does you may need to split this string into individual parameters - see the link below on splitting strings
June 24, 2016 at 8:09 am
I believe it's a multi select drop down. They can put a check next to multiple states.
In the stored procedure, I was planning to build a WHERE IN clause for each state selected.
WHERE STATE IN ('CA', CT', 'TX', 'VA')
But not clear on the format RS will spit out. 'CA, CT, TX, VA' ??
Is there some debugging function in RS to look at the values as they would get sent to the SP ?
June 24, 2016 at 8:22 am
If you want to see the values then you would need to use SQL profiler. Or you can add a a text box to your report and display the parameter value here - you will need to use join function to display all of them.
So if you have 4 options on your multi select pick list with the values A B C D
If the user selects A you will get a A passed
If the user selects A and B you will get a A,B passed
If the user selects All you will get a A,B,C,D passed
June 24, 2016 at 9:45 am
If it's a multi-select then SSRS passes the SSRS parameter values as a comma delimited string.
If user selects CA then 'CA' is passed to the stored proc. If the user selects CA, CT, TX and VA then the value 'CA,CT,TX,VA' is passed.
Using the splitter that FridayNightGiant referenced in his original response you can write your stored proc logic similar to my example below (I wrote my sample sql in a way that you can just copy/paste and run it locally provided that you have set up delimitedsplit8k).
-- sample of your database table
DECLARE @sampleTable TABLE(id int identity, SomeValue int, [state] varchar(100))
INSERT @sampleTable([state],SomeValue) VALUES ('CA',3),('CT',5),('TX',6),('IL',9);
-- What a multi-select SSRS parameter looks like
DECLARE @states varchar(1000) = 'CA,CT,TX,VA';
-- How you would use a splitter to pass the SSRS multi-select values to your stored proc:
-- Option #1
SELECT st.SomeValue, st.[state]
FROM @sampleTable st
WHERE [state] IN (SELECT item FROM dbo.DelimitedSplit8K(@states,','));
-- Option #2
SELECT st.SomeValue, st.[state]
FROM @sampleTable st
WHERE EXISTS (SELECT item FROM dbo.DelimitedSplit8K(@states,',') WHERE Item = st.[state]);
-- Option #3
SELECT st.SomeValue, st.[state]
FROM @sampleTable st
JOIN dbo.DelimitedSplit8K(@states,',') ds ON ds.item = st.[state];
-- Itzik Ben-Gan 2001
June 27, 2016 at 7:17 am
FridayNightGiant (6/24/2016)
If you want to see the values then you would need to use SQL profiler. Or you can add a a text box to your report and display the parameter value here - you will need to use join function to display all of them.
I have a trace running, but it seems as though the SP is not getting called, because I do not see "exec USP_MY_StoredProcedure" in the trace file. Maybe a parameter mismatch or something.
June 27, 2016 at 8:29 am
Do you have RPC:Starting or RPC:Completed as Events in your trace?
June 27, 2016 at 10:36 am
FridayNightGiant (6/27/2016)
Do you have RPC:Starting or RPC:Completed as Events in your trace?
No, this gives me 0 results, using the active trace file
select *
from fn_trace_gettable('D:\TraceFiles\MyDMLtrace_SQL_x160621_x146_10.trc', 1)
where textdata like '%rpc%'
order by starttime desc
June 27, 2016 at 11:04 am
Alan.B (6/24/2016)
If it's a multi-select then SSRS passes the SSRS parameter values as a comma delimited string.If user selects CA then 'CA' is passed to the stored proc. If the user selects CA, CT, TX and VA then the value 'CA,CT,TX,VA' is passed.
This is not entirely accurate. It depends on what you set as the VALUE portion of your parameter.
I am assuming you didn't "roll your own code" for a parameter and chose to use the interface in SSRS to define your parameter.
based upon that assumption; parameter passing values depend on 2 things:
1. The data connection driver or layer (usually OLEDB, ODBC, .NET)
2. The data type defined for the parameter.
#1 deals with how you define each parameter in SSRS (using ? or @P1 etc)
#2 deals with how SSRS will delimit and separate your values to pass.
There are many properties associated to parameters in SSRS but the 2 properties that you would be interested in are VALUE and LABEL.
Whatever way you define a parameter (whether based upon a SQL Statement or hard coded) the LABEL property, which is what a user will see in the dropdown list, is NOT what is passed by SSRS instead the VALUE property is passed. Most developers use both as the same value and that is fine, but its important to understand the difference here.
This affords the developer an option of passing ID values (or any differing value for that matter) and showing text based information to the user. (i.e. I have 50 states in a State table with a UID for each state and instead of passing 'CA','CT','VA' etc I could pass 31,5,10 and in my SP joined to the state table)
June 27, 2016 at 11:18 am
homebrew01 (6/27/2016)
FridayNightGiant (6/27/2016)
Do you have RPC:Starting or RPC:Completed as Events in your trace?No, this gives me 0 results, using the active trace file
select *from fn_trace_gettable('D:\TraceFiles\MyDMLtrace_SQL_x160621_x146_10.trc', 1)
where textdata like '%rpc%'
order by starttime desc
RPC won't be in text data. To see if your trace is capturing these events try the query below. If you aren't capturing either RPC:Starting or RPC:Completed then you won't exec statement from the reporting server.
You should also be aware of what filters are set on the trace.
select *
from fn_trace_gettable('D:\TraceFiles\MyDMLtrace_SQL_x160621_x146_10.trc', 1)
where EventClass in (10,11)
June 27, 2016 at 12:50 pm
Looks like my trace is only capturing event 12 SQL:BatchCompleted
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID
... snip ...
exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle
exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName
I will add 10, 11 and start over
June 27, 2016 at 12:58 pm
Smendle (6/27/2016)
Alan.B (6/24/2016)
If it's a multi-select then SSRS passes the SSRS parameter values as a comma delimited string.If user selects CA then 'CA' is passed to the stored proc. If the user selects CA, CT, TX and VA then the value 'CA,CT,TX,VA' is passed.
This is not entirely accurate. It depends on what you set as the VALUE portion of your parameter.
I am assuming you didn't "roll your own code" for a parameter and chose to use the interface in SSRS to define your parameter.
based upon that assumption; parameter passing values depend on 2 things:
1. The data connection driver or layer (usually OLEDB, ODBC, .NET)
2. The data type defined for the parameter.
#1 deals with how you define each parameter in SSRS (using ? or @P1 etc)
#2 deals with how SSRS will delimit and separate your values to pass.
There are many properties associated to parameters in SSRS but the 2 properties that you would be interested in are VALUE and LABEL.
Whatever way you define a parameter (whether based upon a SQL Statement or hard coded) the LABEL property, which is what a user will see in the dropdown list, is NOT what is passed by SSRS instead the VALUE property is passed. Most developers use both as the same value and that is fine, but its important to understand the difference here.
This affords the developer an option of passing ID values (or any differing value for that matter) and showing text based information to the user. (i.e. I have 50 states in a State table with a UID for each state and instead of passing 'CA','CT','VA' etc I could pass 31,5,10 and in my SP joined to the state table)
I stand corrected. I was not aware of that. 😎
-- Itzik Ben-Gan 2001
June 27, 2016 at 1:25 pm
I now have 4000+ plus rows after 30 minutes or so !!
select *
from fn_trace_gettable('D:\TraceFiles\MyDMLtrace_SQL_x160627_x1455.trc', 1)
where EventClass in (10,11)
Now I see my "exec USP_MY_StoredProcedure" Call ... Thanks!
June 27, 2016 at 2:50 pm
BUT ....... Now RS seems to handle the parameters in a select list differently if there's 1 chosen or multiples.
If I select just sate CA, I get good results & my trace file shows:
exec usp_JobMatch_List_state @Job_ID=454185,@State=N'''CA'''
But if I select multiple states with check marks, it fails due to too many quotes & the trace file shows extra quotes around the states:
exec usp_JobMatch_List_state @Job_ID=454185,@State=N'''''CA'''',''''MA'''''
June 27, 2016 at 4:58 pm
homebrew01 (6/27/2016)
BUT ....... Now RS seems to handle the parameters in a select list differently if there's 1 chosen or multiples.If I select just sate CA, I get good results & my trace file shows:
exec usp_JobMatch_List_state @Job_ID=454185,@State=N'''CA'''But if I select multiple states with check marks, it fails due to too many quotes & the trace file shows extra quotes around the states:
exec usp_JobMatch_List_state @Job_ID=454185,@State=N'''''CA'''',''''MA'''''
Did you implement the split string function that you marked as the answer? If so, than that's your problem, SSRS should delimit the value(s) just fine for your Stored Proc as long as the data types match.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply