asp.net SqlDataSource select statement with IN (...) clause

Question

I need to pass a server-side parameter to my SqlDataSource SELECT IN-clause as follows (notice the parameter @InClause and it's location in the SQL-select that is defined in the aspx (for the SqlDataSource):

SELECT UID_REPORT, TXT_RPT_TEXT, TXT_RPT_NAME, TXT_RPT_ASPX_PAGE, TXT_RPT_TYPE 
FROM REPORTS WHERE (UID_REPORT IN (@InClause)) 
ORDER BY INT_SORT_ORDER

But this does not pass validation in the Test-Query operation. The '@InClause' parameter gets it's value from a HiddenField control. I want to set this value based on some conditions. for example If DIVISION=5 then @InClause would ="45,46" Else ="14,15". Any suggestions on how to pass the IN ( ... ) values by the hiddenfield.Value (which is a string, of course). Thanks.

Answer 1

You need split string function to do this

SELECT uid_report, 
       txt_rpt_text, 
       txt_rpt_name, 
       txt_rpt_aspx_page, 
       txt_rpt_type 
FROM   reports 
WHERE  uid_report IN (SELECT split_value 
                      FROM   Udf_splitstring(@InClause, ',')) 
ORDER  BY int_sort_order 

Check the below links to create a split string function

1. http://sqlperformance.com/2012/07/t-sql-queries/split-strings
2. http://www.sqlservercentral.com/articles/Tally+Table/72993/

or of-course you can use dynamic sql