question

Alex.Ritna avatar image
Alex.Ritna asked

IN() clause with parameters

Hi, I have a report I'm generating with SSRS which has a parameter I want people to be able to input a comma separated list of codes to be ignored in the query using a NOT IN() clause. The research I have done so far has pointed towards it not being possible due to how parameters work with IN() but so far I have only really found examples using C#, Java etc and just wanted to know if there was a good way to workaround this using an SSRS report. In case my question isn't clear, the below should help a little -- 1) A regular IN() Clause SELECT * FROM dbo.testINClause WHERE testText IN('AB','CD') -- Works OK -- 2) Using param with a single value DECLARE @PARAM VARCHAR(50); SET @PARAM = 'AB'; SELECT * FROM dbo.testINClause WHERE testText IN(@PARAM); -- Works OK -- 3) Using param with a multiple values DECLARE @PARAM VARCHAR(50); SET @PARAM = 'AB,CD'; SELECT * FROM dbo.testINClause WHERE testText IN(@PARAM); -- No Results The table just contains a generic integer primary key and a varchar(5) testText field, with some sample data in each row (AB, CD, AB, EF, FG, GH, AB, CD, DE etc). Any suggestions or pointers would be greatly appreciated!
t-sqlssrs-2008parameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
Although @prasad.nyalapatla dynamic query solution can work, but I generally stay away from dynamic SQL because of the reasons like it makes your code vulnerable to SQL injection etc. I guess this is time for you to explore the updated 8K Splitter function provided in this great [article][1] by Jeff Moden. [1]: http://www.sqlservercentral.com/articles/Tally+Table/72993/ For brevity, I am posting it here CREATE FUNCTION [dbo].[DelimitedSplit8K_T1] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL -- does away with 0 base CTE, and the OR condition in one go! SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l ; GO Then you just need to tweak your query to something like DECLARE @PARAM VARCHAR(50); SET @PARAM = 'AB,CD'; SELECT * FROM dbo.testINClause WHERE testText IN(SELECT Item FROM [dbo].[DelimitedSplit8K_T1](@PARAM, ',')); Hope it helps.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Usman Thanks (again), works great. @prasad.nyalapatla thanks for the suggestion (which works), though where possible I try to steer from dynamic SQL as well
0 Likes 0 ·
prasad.nyalapatla avatar image
prasad.nyalapatla answered
hi, use Below Query, DECLARE @PARAM VARCHAR(50); SET @PARAM = 'AB,CD'; declare @Query Varchar(max); select @Query =' SELECT * FROM dbo.testINClause WHERE testText IN('+@PARAM+')'; Regards prasad
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.