none
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

    Monday, June 11, 2012 11:41 AM

Answers

  • Monday, June 11, 2012 12:19 PM
    Answerer
  • 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


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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



    Monday, June 11, 2012 1:01 PM
  • 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/


    Monday, June 11, 2012 1:47 PM
    Moderator

All replies

  • 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/

    Monday, June 11, 2012 11:43 AM
    Answerer
  • Hi

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


    Tiny

    Monday, June 11, 2012 12:01 PM
  • 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.


    Monday, June 11, 2012 12:18 PM
  • Monday, June 11, 2012 12:19 PM
    Answerer
  • SET @CNa = '''test3'',''test4'',''test5'''


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

    You can do something like this

    Regards
    Satheesh

    Monday, June 11, 2012 12:29 PM
  • 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, esquel@sommarskog.se
    Monday, June 11, 2012 12:39 PM
  • 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


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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



    Monday, June 11, 2012 1:01 PM
  • 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/


    Monday, June 11, 2012 1:47 PM
    Moderator