Using 'IN' In A Procedure Based Report

We have a report that uses several data sets. In 3 data sets we are using multiselect drop downs to select criteria for the report to run. As long as we create the DataSet in SRS there is no problem and things work as expected.

Our code looks something like this

SELECT ID, NAME
FROM PROPERTY
WHERE (PROPERTY.AREA IN (@AREA))

If we select multiple values the report give multiple results

However we have some very complex querys that use #temp tables. If we put these querys directly in the SRS dataset it wiill not allow us to use the fields because SRS can't read past the #temp tables, thus we need to use procedures.

When we use procedures we want to still use "IN" statement in order to utilize the multiselect. But SRS is forcing us to chage the Int type to string, and then it is not returning the proper results if we select more then one value.

Do we have to do something different to use IN statements in a procedure?

Here is the code that will not run as a proc from SRS


SELECT SITE.SITEID, SITE.PROPID, SITE.LOCATIONNAME, SITECONTROLDATA.R1PROGRAM, PROPERTY.PROPERTY, CLIENT.CLIENTID, CLIENT

INTO [#TEMP1]

FROM PROPERTY INNER JOIN

SITE ON PROPERTY.PROPID = SITE.PROPID INNER JOIN

SITECONTROLDATA ON SITE.SITEID = SITECONTROLDATA.SITEID INNER JOIN

CLIENT ON PROPERTY.CLIENTID = CLIENT.CLIENTID

WHERE (SITECONTROLDATA.CALC_DATE > GETDATE() - 8) AND (PROPERTY.AREA IN (@AREA)) AND (CLIENT.CLIENTID IN(@CLIENTID))