troubleshooting Question

SQL Server & SSRS working with parameters and multiple select values.

Avatar of RUA Volunteer2?
RUA Volunteer2?Flag for United States of America asked on
SQLMicrosoft SQL ServerSSRSMicrosoft Server OS
2 Comments2 Solutions15 ViewsLast Modified:
I think I need to use some variant of the split function or string_split or the c_split.
IN the SQL Stored Procedure I have declared the value
@location_code varchar (100)

I have referenced it in a table. l_location. The table is joined on. 
and I have it in the where clause ...AND ..tbl.location_code in (@location_code)

When the report runs in SSRS the lookup values show up and they can be selected individually or in multiples. Currently I can only select one value and it runs. If I select 2 or more the report shows no results but runs. That is why I think that I need to use split somewhere. I have tried many different ways and none of them worked. If anyone knows what I need to make sure I include please advise. Thank you. 

ALTER FUNCTION [dbo].[c_Split]
(
@InputString VARCHAR(MAX)
,@Separator VARCHAR(MAX)
)
RETURNS @ValueTable TABLE (Value VARCHAR(MAX))
AS
BEGIN

    DECLARE @SeparatorIndex INT, @TotalLength INT, @StartIndex INT, @Value VARCHAR(MAX)
    SET @TotalLength=LEN(@InputString)
    SET @StartIndex = 1

    IF @Separator IS NULL RETURN

    WHILE @StartIndex <= @TotalLength
    BEGIN
        SET @SeparatorIndex = CHARINDEX(@Separator, @InputString, @StartIndex)
        IF @SeparatorIndex > 0
        BEGIN
            SET @Value = SUBSTRING(@InputString, @StartIndex, @SeparatorIndex-@StartIndex)
            SET @StartIndex = @SeparatorIndex + 1
        END
        ELSE
        BEGIN
            Set @Value = SUBSTRING(@InputString, @StartIndex, @TotalLength-@StartIndex+1)
            SET @StartIndex = @TotalLength+1
        END
        INSERT INTO @ValueTable
        (Value)
        VALUES
        (@Value)
    END

    RETURN
END

Open in new window


ASKER CERTIFIED SOLUTION
Andrei Fomitchev
Sr. DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros