Where ColumnName IN (@CNa)

Question

-- drop table #TABLENAME

SELECT

*

into

#TableName

FROM

(SELECT 'test' as [ColumnName] union SELECT 'test1' union SELECT 'test2' union SELECT 'test3' union SELECT 'test4' union SELECT 'test5' union SELECT 'test6' union SELECT 'test7' )X

----------------------------------------

DECLARE

@CN VARCHAR(50),@CNa VARCHAR(50)

SET

@CN = 'test3' SET @CNa = 'test3,test4,test5'

----------------------------------------

SELECT COLUMNNAME

FROM #TABLENAME

----------------------------------------

SELECT COLUMNNAME

FROM #TABLENAME

WHERE COLUMNNAME IN ('test3')

----------------------------------------

SELECT COLUMNNAME

FROM #TABLENAME

WHERE COLUMNNAME IN ('test3','test4','test5')

----------------------------------------

SELECT COLUMNNAME

FROM #TABLENAME

WHERE COLUMNNAME IN (@CN)

----------------------------------------

SELECT COLUMNNAME

FROM #TABLENAME

WHERE COLUMNNAME IN (@CNa)

----------------------------------------

If you run the above Qry, you find that the last Qry does not return any results..... this is where i need help

can anyone show me a uncomplicated way of getting the @CNa working

eventually my store proc will be run by a SSRS report, but i need the qry to run first

Thanks in Advance

---

Tiny

Answer 1

http://www.sommarskog.se/arrays-in-sql.html

DECLARE @t VARCHAR(50)
SET @t = ('white,smith')

SELECT *
  FROM authors
 WHERE ',' + @t + ',' LIKE '%,' + au_lname + ',%'

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and optimization

Answer 2

Hi

thanks for the reply, but i must be doing something wrong as i can not get that to work :-(

---

Tiny

Answer 3

Hi There

I don’t think that you can use declared variable like this a variable can hold only a single value. You need to use table variable if you want to store multiple values.

Otherwise you can create split function like this

CREATE FUNCTION dbo.SplitStrings 
( 
    @List      NVARCHAR(MAX),  
    @Delimiter NVARCHAR(2) 
) 
RETURNS @i TABLE(Item NVARCHAR(MAX))    
AS 
BEGIN 
    DECLARE @x XML; 
 
    SET @x = N'<root><i>'  
        + REPLACE(@List, @Delimiter, '</i><i>')  
        + '</i></root>'; 
 
    INSERT @i(Item) 
        SELECT RTRIM(i.value('.','nvarchar(max)')) 
        FROM @x.nodes('//root/i') AS i(i); 
 
    RETURN; 
END 

And use your query like this

SELECT COLUMNNAME
FROM #TABLENAME
INNER JOIN dbo.SplitStrings(@CNa, ',') AS s
   ON s.Item = #TABLENAME.COLUMNNAME; 
--WHERE COLUMNNAME IN (select @CNa )

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

I see  you are using SSRS ,http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and optimization

Answer 5

SET @CNa = '''test3'',''test4'',''test5'''

print'SELECT COLUMNNAME
FROM #TABLENAME
WHERE COLUMNNAME IN ('+@CNa+')'

You can do something like this

Regards
Satheesh

Answer 6

If you were not able to get the particular trick that Uri showed you to work maybe that's a good thing, because that's the slowest in the book. I suggest that you start reading the article on my web site that Uri was kind to point you to. The article is very long, but you will find that that you don't need to read much at all to get your report going.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

As you are using the SSRS then there might be 3 ways can solve your problem

1. use query parameter and make sure that you ticked the Allow multiple Values. so that you have a variable tablename, which you are able to tick multiple values or even select all, to pass to you variable @tableName, like 'test1, test2, test3,...'

your SSRS query like Select something,... from your_table (or joins) Where columnname in (@tableName)

SSRS will automatically split the value for you.

2. however, if you decide to wrap everything in to a stored procedure and the option 1 is not possible, as the variable in stored procedure can't hold an array of values. BUT..

in this case, you can use the table-valued parameters, the procedure:

2.1 create a type as table like: create type dbo.MyTableType as Table (Mycol varchar(128))

2.2 Create procedure dbo.usp_myprocedure

@myTableType MyTAbleType ReadOnly

As

Select ....

From yourTAble join @MyTableType

........

2.3 try to use SSRS Split function to populate the @MyTableType variable or write a VB or C# code in SSRS to populate it.

3. write a split function in your database side to split the string value

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Answer 8

SSRS behaves different from native stored procedure calls because it actually relies on dynamic SQL using sp_executesql. IN any case, what you actually get is a dynamic predicate that looks something like this:

set @sql = 'SELECT * FROM dbo.Table WHERE column IN(' + @Param + ')'

So it doesnt really have to do with arrays, but how the actual statement is executed.  The problem with this type of behavior is that a new query plan is generated for every distinct set of param values.  http://jahaines.blogspot.com/2009/11/ssrs-should-i-use-embedded-tsql-or.html

Anyhow, the answer to the OP question.  The reason no results return is becauuse there is no columnname value 'test3,test4,test5'  for any given row.  You will have to let SSRS handle this for you via dynamic SQL (using embeded SQL, with a multi-list box) or use a delimited string split function.

http://jahaines.blogspot.com/2009/11/splitting-delimited-string-part-1.html

---

http://jahaines.blogspot.com/