Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to extract varchar from SSRS multi value string in a stored procedure?

Avatar of tekkie2
tekkie2Flag for United States of America asked on
Microsoft SQL ServerDB Reporting ToolsMicrosoft SQL Server 2005
2 Comments1 Solution772 ViewsLast Modified:
I have 2 parameters on my SSRS report that are both multi value strings, I need to pass these multi values to a stored procedure on my MS SQL 2005 instance and then do a lookup in a table based on the values in the 2 multi values strings.

In my SSRS report's Dataset Parameters section I declare the following:
@Logger =Join(Parameters!Component.Value,",")
@Level =Join(Parameters!Level.Value,",")

According to what I was able to find on the internet and various discussion @EE this should do the trick, unfortunately it only works half :( meaning that if I only select 1 value for each of the parameters I get data back, if I select multiple values for any of the parameters the query fails silently.

I've used the option to execute the stored proc from the Data tab on my report and no matter what combo I put into the parameters, i.e. ERROR, FATAL or 'ERROR','FATAL' etc. the stored procedure doesn't return any values.
@Logger VARCHAR(MAX),
@Level VARCHAR(MAX)
AS
BEGIN
	SELECT
		a.Date
		, a.Thread
		, a.Logger
		, a.[Level]
		, a.Message
		, a.Exception
	FROM
		Logs AS a
	WHERE a.Logger IN(@Logger) AND a.[Level] IN(@Level)
	ORDER BY a.Date DESC
END;