locked
Use of IN keyword with variable RRS feed

  • Question

  • Since I installed sql server 2005 and sql server 2008 I can't use stament of the type SELECT * FROM table WHERE field IN (@Filtro) the field type is varchar and @filtro is an array of varchar fields, I used a lot this type of statements with reporting service for managin multi values parameters.
    Even in the adventure works database I had this problem, I got not result for this type of queries. Thanks for any help.

    USE AdventureWorks
    GO

    declare

     

    @stt as varchar(15)

    set

     

    @stt = '''' + 'Mr.' + '''' + ',' + '''' + 'Ms.' + ''''

    print

     

    @stt

    select

     

    * from Person.Contact where Title in (@stt)

    • Moved by Tom Phillips Monday, April 27, 2009 4:15 PM TSQL Question (From:SQL Server Database Engine)
    Monday, April 27, 2009 4:13 PM

Answers

  • I'm afraid that is not a valid way of doing what you're trying to do.

    Check out the following article... it will give you a whole bunch of ways to accomplish what you're trying to do:

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


    --Brad
    Monday, April 27, 2009 4:26 PM
  • There is no such functionality in T-SQL as macro-substitution, and I do not think you need it in this case.

    Here is an outstanding article about simulating arrays in T-SQL.

    Arrays and Lists in SQL Server
    http://www.sommarskog.se/arrays-in-sql.html


    If you are using SQL Server 2008, then check the topic "Table-valued parameters".


    AMB
    Monday, April 27, 2009 4:29 PM
  • Here's something I wrote up as a "cheat sheet" for myself over the last couple of months describing how to use the XML approach to what you're doing:

    Produce a list of records for a comma-delimited string

    declare @idlist varchar(20), @x xml

    set @idlist='10248,10254,10257'

    set @x='<i>'+replace(@idlist,',','</i><i>')+'</i>'

    select x.i.value('.','int') as id from @x.nodes('/i') x(i)  --produces a record for each value

     

    Then get the orders for that:

    select * from orders

    where orderid in (select x.i.value('.','int') as id from @x.nodes('/i') x(i))

     

    How the xml stuff works above:

    --the .nodes() method takes an XML and pulls out all the nodes that have an <i> tag

    --That rowset is given a derived table name of x and it calls the column i

    --Perhaps it would be more clear as:

    --  FROM @x.nodes('/i') AS DerivedTableName (DerivedColumnNameXMLNode)

    from @x.nodes('/i') x(i)

    --We can’t really just output those nodes in the rowset. 

    --Instead we have to do some kind of XML method on them,

    --like .exist() or .nodes() or .query() or .value()

    --So we will use the .value(<xquery>,<type>)

    --Going along with the comments above, perhaps it would be more clear as:

    --  SELECT DerivedTableName.DerivedColumnNameXMLNode('.','int') as id

    select x.i.value('.','int') as id

     

     

    Here’s the above in a single SELECT (ugly but it works):

    use northwind

    go

    declare @idlist varchar(20)

    set @idlist='10248,10254,10257'

    select orders.*

    from orders

    where orderid in

      (select xmlElement.value('.','int')

         from (select convert(xml,'<i>'+replace(@idlist,',','</i><i>')+'</i>') as xmlList) as xmlConstruct

         cross apply xmlList.nodes('/i') as xmlNode (xmlElement))

    --Explanation:

    --  select convert(xml,...) as xmlList creates an XML field called xmlList that looks like

    --    <i>10248</i><i>10254</i><i>10257</i>

    --  Then we cross apply that to (i.e. perform a function on that with)...

    --  xmlList.nodes('/i') as xmlNode (xmlElement)

    --    This pulls out all the nodes in the xmlList that have an <i> tag and

    --    essentially creates a derived table called xmlNode with a field called xmlElement

    --    that contains those nodes with an <i> tag

    --  But that xmlElement field contains a node that we can’t do anything with

    --    unless we perform some kind of XML method against it, like .exist() or .nodes() or .query()

    --    or .value().  So we will use the .value(<xquery>,<type>) method...

    --  xmlElement.value('.','int')

    --  So with that, we are pulling the value from the node as an integer... in other words, we

    --    are essentially creating a set of integer values:  10248 and 10254 and 10257

    --  Therefore we check to see if Orders.OrderID is IN that set of values.


    --Brad
    Monday, April 27, 2009 4:31 PM
  • Arnie Rowland has an example on the Forums Example site that provides a table valued function to do exactly what you are trying to do, and exactly what has been done in Brad's example, but in a bit easier to read manner:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home

    all you have to do is join to the dbo.Split(@delimiter, @arraystring) TVF and it will allow you to do exactly what the IN clause allows.  See Arnies example titled:
    Using the Split Function in a JOIN to retrieve rows from a Table with 'matching' Values.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, April 27, 2009 10:51 PM

All replies

  • I'm afraid that is not a valid way of doing what you're trying to do.

    Check out the following article... it will give you a whole bunch of ways to accomplish what you're trying to do:

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


    --Brad
    Monday, April 27, 2009 4:26 PM
  • There is no such functionality in T-SQL as macro-substitution, and I do not think you need it in this case.

    Here is an outstanding article about simulating arrays in T-SQL.

    Arrays and Lists in SQL Server
    http://www.sommarskog.se/arrays-in-sql.html


    If you are using SQL Server 2008, then check the topic "Table-valued parameters".


    AMB
    Monday, April 27, 2009 4:29 PM
  • Here's something I wrote up as a "cheat sheet" for myself over the last couple of months describing how to use the XML approach to what you're doing:

    Produce a list of records for a comma-delimited string

    declare @idlist varchar(20), @x xml

    set @idlist='10248,10254,10257'

    set @x='<i>'+replace(@idlist,',','</i><i>')+'</i>'

    select x.i.value('.','int') as id from @x.nodes('/i') x(i)  --produces a record for each value

     

    Then get the orders for that:

    select * from orders

    where orderid in (select x.i.value('.','int') as id from @x.nodes('/i') x(i))

     

    How the xml stuff works above:

    --the .nodes() method takes an XML and pulls out all the nodes that have an <i> tag

    --That rowset is given a derived table name of x and it calls the column i

    --Perhaps it would be more clear as:

    --  FROM @x.nodes('/i') AS DerivedTableName (DerivedColumnNameXMLNode)

    from @x.nodes('/i') x(i)

    --We can’t really just output those nodes in the rowset. 

    --Instead we have to do some kind of XML method on them,

    --like .exist() or .nodes() or .query() or .value()

    --So we will use the .value(<xquery>,<type>)

    --Going along with the comments above, perhaps it would be more clear as:

    --  SELECT DerivedTableName.DerivedColumnNameXMLNode('.','int') as id

    select x.i.value('.','int') as id

     

     

    Here’s the above in a single SELECT (ugly but it works):

    use northwind

    go

    declare @idlist varchar(20)

    set @idlist='10248,10254,10257'

    select orders.*

    from orders

    where orderid in

      (select xmlElement.value('.','int')

         from (select convert(xml,'<i>'+replace(@idlist,',','</i><i>')+'</i>') as xmlList) as xmlConstruct

         cross apply xmlList.nodes('/i') as xmlNode (xmlElement))

    --Explanation:

    --  select convert(xml,...) as xmlList creates an XML field called xmlList that looks like

    --    <i>10248</i><i>10254</i><i>10257</i>

    --  Then we cross apply that to (i.e. perform a function on that with)...

    --  xmlList.nodes('/i') as xmlNode (xmlElement)

    --    This pulls out all the nodes in the xmlList that have an <i> tag and

    --    essentially creates a derived table called xmlNode with a field called xmlElement

    --    that contains those nodes with an <i> tag

    --  But that xmlElement field contains a node that we can’t do anything with

    --    unless we perform some kind of XML method against it, like .exist() or .nodes() or .query()

    --    or .value().  So we will use the .value(<xquery>,<type>) method...

    --  xmlElement.value('.','int')

    --  So with that, we are pulling the value from the node as an integer... in other words, we

    --    are essentially creating a set of integer values:  10248 and 10254 and 10257

    --  Therefore we check to see if Orders.OrderID is IN that set of values.


    --Brad
    Monday, April 27, 2009 4:31 PM
  • Thanks a lot for the replies, is the firts time a expose a problem in a forum and is glad to see how effective is this site. As a matter in fact the problem what I want to solve is passing a multi value parameter of text type from Reporting Service to a store procedure and filter the records based in the comma-separated list contained in the parameter. In fact the data is stored in a 2005 database and I using Reporting Service 2008.

    Thanks a lot for your time and your help.
    Monday, April 27, 2009 9:35 PM
  •    That is (for all intesive purposes) what Brad just did.
    Monday, April 27, 2009 10:25 PM
  • Arnie Rowland has an example on the Forums Example site that provides a table valued function to do exactly what you are trying to do, and exactly what has been done in Brad's example, but in a bit easier to read manner:

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home

    all you have to do is join to the dbo.Split(@delimiter, @arraystring) TVF and it will allow you to do exactly what the IN clause allows.  See Arnies example titled:
    Using the Split Function in a JOIN to retrieve rows from a Table with 'matching' Values.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, April 27, 2009 10:51 PM
  • you may try the execute

    nothing to change, just put your select statement in to the execute function

    USE AdventureWorks
    GO

    declare

    @stt as varchar(15)

    set

    @stt = '''' + 'Mr.' + '''' + ',' + '''' + 'Ms.' + ''''

    print

    @stt

    execute('select * from Person.Contact where Title in ('+@stt+')')


    Meng Chew

    Tuesday, April 28, 2009 12:50 AM
  • you may try the execute

    nothing to change, just put your select statement in to the execute function

    USE AdventureWorks
    GO

    declare

    @stt as varchar(15)

    set

    @stt = '''' + 'Mr.' + '''' + ',' + '''' + 'Ms.' + ''''

    print

    @stt

    execute('select * from Person.Contact where Title in ('+@stt+')')


    Meng Chew

     


    This is a very dangerous use of Dynamic SQL and is prone to SQL Injection Attacks.  It should not be used given that there are alternatives that do not expose the risk to your database server and/or data to SQL Injection.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, April 28, 2009 1:01 AM