Multiple value in a parameter

Question

I have a query that has 3 parameters Start Date, End Date and Location. The query is stored in SQL Stored Procedure, and I am calling the Stored Procedure in SSRS (EXEC dbo.ups_Calls @Start, @End, @Location) (see below code).

CREATE PROC dbo.Calls
    @Start DATETIME=NULL,
    @End DATETIME=NULL,
    @Location VARCHAR(25)=NULL
AS
BEGIN

SELECT CALLDATE, LOCATION
FROM CALL_DETAILS WITH (NOLOCK)
WHERE CALLDATE BETWEEN @START AND @END
AND LOCATION IN (@Location)

END
GO

ISSUE: Calling the stored procedure from SSR query box (see below) is only pulling one site from the SSRS location parameter even though I selected more than one location, BUT if I embed the query itself in SSRS then I am able to pull multiple sites.

score 0 · Answer 1 · answered Oct 06 '16 at 05:09

When you use a stored procedure, it needs to be able to handle multiple parameters. SSRS usually passes multi-value parameters as comma separated values, so you could use the following function combined with your stored procedure to process the values:

CREATE FUNCTION [dbo].[udf_Split]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END
GO

Stored procedure:

CREATE PROC dbo.Calls
    @Start DATETIME=NULL,
    @End DATETIME=NULL,
    @Location VARCHAR(25)=NULL
AS
BEGIN

SELECT CALLDATE, LOCATION
FROM CALL_DETAILS WITH (NOLOCK)
WHERE CALLDATE BETWEEN @START AND @END
AND LOCATION IN (SELECT DATA FROM [dbo].[udf_Split](@Location,','))

END
GO

Multiple value in a parameter

1 Answers1