Problems with SQL, using parameters with multiple values in report builder

Question

have a query relating to MS SQL Server.

I'm going to have to be vague with details and change or remove certain parts as i can't say whether the info is confidential or not, but I've written a query that searches for students with their training units spanning across semesters using report builder 2.0:

DS_spanning: (Main dataset)

SET DATEFORMAT dmy

SELECT 
  FIRST_NAME AS FirstName
 ,LAST_NAME AS LastName
 ,START_DATE AS StartDate
 ,END_DATE AS EndDate
 ,UNIT_TYPE AS UnitType
 ,TP_FULLNAME AS TrainingPost
 ,SEMESTER_YEAR AS SemesterYear
FROM AA_GPR_TU

WHERE TU_START_DATE < @checkdate   //Checkdate returns the end date of the
AND TU_END_DATE > @checkdate       // selected semester  

ORDER BY TU_START_DATE

PM_checkdate: (Dataset that @checkdate is linked to)

SET DATEFORMAT dmy
SELECT
  new_semesterenddate
  ,new_semesterstartdate
  ,new_semesternumber
  ,new_semesteryear
  ,new_name
FROM
  FilteredNew_rtpsemester

WHERE new_semesteryear >= 2004

ORDER BY new_semesterenddate DESC

Now, this works fine and does the job i want it to, however i can only select one semester at a time. When i try to tick the 'Allow Multiple Values' box under the Report Parameter Properties for @checkdate, running the report with more than one semester selected gives me this error:

Incorrect syntax near ','.

Query execution failed for dataset 'DS_spanning'.

An error has occurred during report processing.

An error occurred during local report processing.**

(The problem with a ',' is because when i select multiple values for the report, it supplies them to @checkdate as data1, data2, data3... etc.)

Is there something wrong with the way my parameter has been written, or do i need to change my query to accomodate multiple values?

Easiest way i could think of doing this would be using an IN statement in the section

WHERE TU_START_DATE < @checkdate AND TU_END_DATE > @checkdate

but i'm not sure how to use an IN statement in conjunction with and operands.

Any ideas folks?

Luke Franklin Luke Franklin · Accepted Answer · 2013-07-19T09:56:23

i have had select multiple value problem so many times and after searching the internet for numerous hours i found this and it works a treat.

First of all create this function call split into the database you are running your queries from.

USE [DatabaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[split]    Script Date: 07/19/2013 10:49:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

        CREATE FUNCTION [dbo].[split]( 
          @delimited NVARCHAR(MAX), 
          @delimiter NVARCHAR(100) 
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) 
        AS 
        BEGIN 
          DECLARE @xml XML 
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>' 

          INSERT INTO @t(val) 
          SELECT  r.value('.','varchar(MAX)') as item 
          FROM  @xml.nodes('/t') as records(r) 
          RETURN 
        END

the way you then use this in a WHERE clause is by using this line of code be sure to change the word column and parameter leave the word val as this is needed for the function

WHERE [COLUMN] IN (SELECT val FROM dbo.split(@Parameter, ','))

Problems with SQL, using parameters with multiple values in report builder

2 Answers