1

Can I use FOR XML PATH in sql for int columns ? So that I could use it for something like:

declare @contactIds = (select id from contacts)

and then use it like this:

select * from calls where contactId in (@contactIds)

Is it possible ?

underscore_d
  • 6,309
  • 3
  • 38
  • 64
Liran Friedman
  • 4,027
  • 13
  • 53
  • 96
  • 1
    You mean you want to store a collection of IDs in a variable, so you can use that later on in the WHERE clause of a query? You can do that with a temporary table. – Ruud Helderman Mar 03 '14 at 12:52
  • if `@contactIds` is a string (which it will be if you use `FOR XML PATH`, then SQL will not inspect the contents of that string, find commas, and then decide to treat that *single* string as if it was in fact several *separate* parameters to `IN()`. I don't know of any language that would do this, so I'm always surprised when people try to do it with SQL. – Damien_The_Unbeliever Mar 03 '14 at 13:57

1 Answers1

2

Is this what you want?

select @contactIds = stuff((select ','+cast(id as varchar(8000))
                            from contacts
                            for xml path('')
                           ), 1, 1, '');

You can also use a subquery directly or a table variable:

select *
from calls
where contactId in (select id from contacts);

My guess is that your problem is more complex than the question, so this doesn't really solve the problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have a query to bring contact name and the number of his outgoing calls and number of incoming calls. So I use subqueries but it takes 12 seconds to run, so I am looking how to improve it... – Liran Friedman Mar 03 '14 at 13:08
  • There might be ways to actually improve the query. I would suggest that you ask *another* question with information about what you are actually doing. You cannot use `@ContactIds` in an `in` statement (well, you can, it just doesn't do what you want). – Gordon Linoff Mar 03 '14 at 13:14