How to pass value to parameter in a parameterized query with IN clause

Take a look at the following MS SQL query:

SELECT MT.MeetingTypeName, COUNT(DISTINCT(A.MemberId)) AS AttendanceTotal

FROM MeetingAttendance AS MA INNER JOIN MeetingType AS MT

ON MA.MeetingTypeId = MT.MeetingTypeId

WHERE (MA.MeetingDate = @meetingDate) AND (MT.MeetingTypeId IN (1,2,3))

SELECT MT.MeetingTypeName, COUNT(DISTINCT(A.MemberId)) AS AttendanceTotal

FROM MeetingAttendance AS MA INNER JOIN MeetingType AS MT

ON MA.MeetingTypeId = MT.MeetingTypeId

WHERE (MA.MeetingDate = @meetingDate) AND (MT.MeetingTypeId IN @metingTypeIDs)

The answer to the above question is “Yes” as long as you know how to pass the value to the parameter @meetingTypeIDs. If you try to pass a string value to the parameter, you will find it does not work. For example, if you pass ‘(1,2,3)’ to @meetingTypeIDs, then you will either get nothing, or get an error about the mismatched types. After some Google on the Internet, I finally figured out a way to correctly pass value to parameterized query with IN clause.

My solution requires you to use Stored Procedure. The basic idea is to pass an XML value to the parameter, then pass the XML data to populate the IN clause.

1. Create the following stored procedure

CREATE PROCEDURE [dbo].[sp_GetAttendanceTotalByDate_Types]

(

    @meetingDate datetime,

    @meetingTypeIds xml

)

AS

SELECT MT.MeetingTypeName, COUNT(DISTINCT(A.MemberId)) AS AttendanceTotal

FROM MeetingAttendance AS MA INNER JOIN MeetingType AS MT

ON MA.MeetingTypeId = MT.MeetingTypeId

WHERE (MT.MeetingDate = @meetingDate) AND (MA.MeetingTypeId IN

 (SELECT nref.value('@ID[1]', 'int') ID from @serviceTypeIds.nodes('//MeetingTypeID') AS R(nref) ))

GROUP BY MT.MeetingTypeName

2. Build the XML data in the code

You can manually build your XML data in a way like this:

StringBuilder sb = new StringBuilder();for (int i = 1; i < 5; i++)
{
    sb.Append("<MeetingTypeID ID=\"");
    sb.Append(i.ToString());
    sb.Append("\"></MeetingTypeID>");
}

Then you can pass sb.ToString() to the stored procedure and it will be all set.