Reporting Services - Passing Parm String to Stored Procedure ?

Question

Reporting Services - Passing Parm String to Stored Procedure ?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Smendle (6/27/2016)
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.

I decided to try without the split string.

Seems as though RS puts extra quotes when selecting multiple States, compared to selecting a single state

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 2

Do the values in your parameter already contain quotes?

If so, in the data set under the parameter tab in the parameter value for state set the expression to be as below. (where State is the name of your report parameter.

=Join(Parameters!State.Value, ",")

--Without quotes

exec Test @State=N'CA'

exec Test @State=N'CA,BC,DV'

--Parameter Values are defined with quotes

exec Test @State=N'''CA'''

exec Test @State=N'''''CA'''',''''FC'''',''''BV'''''

--Parameter Values are defined with quotes but use the join function

exec Test @State=N'''CA'''

exec Test @State=N'''CA'',''FC'''

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I believe I have the state code worked out. Thanks for all the help.

Now working on the lack of results.

The records selected in the stored procedure's SELECT JOB, STATE FROM TABLE are not displaying.

EDIT: Started a new thread since it's a new topic

Smendle Hall of Fame Points: 3969 More actions · Answer 4

homebrew01 (6/28/2016)
I believe I have the state code worked out. Thanks for all the help.
Now working on the lack of results.
The records selected in the stored procedure's SELECT JOB, STATE FROM TABLE are not displaying.

Your missing the where predicate(s) as determined by your parameter being passed from ssrs.

SELECT JOB, STATE FROM TABLE WHERE STATE IN (@P1)