0

In the following query, I would like to make the values in the IN dynamic.

SELECT DISTINCT party_id 
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (5,6,8)
2
  • Have a subquery look for party_approval_type_id in another table.
    – dreamlax
    Oct 28 '13 at 1:21
  • As you are on SQL Server 2008 the preferred way would probably be to pass these values in via a table valued parameter Oct 28 '13 at 10:54
1

It depends whether the values you want to go in the IN list are from an existing table or from a SQL parameter. If they are from an existing table or query you can do something like this:

SELECT DISTINCT party_id 
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select party_approval_type_id from some_table)

If you want it to be based on parameters to a stored procedure, you can pass a varchar containing a delimited list of values, and use one of the many existing UDFs to split it into a table variable, similar to below:

SELECT DISTINCT party_id 
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select * from dbo.CsvToInt(@values))
1

You could use a XML document as a parameter. This is a trick used when one needs to pass an array of distinct values to a stored procedure. An example from Stack Overflow is nice an illustration about the technique:

declare @xmlDoc XML
set @xmlDoc = '
<ids>
  <id>1</id>
  <id>2</id>
</ids>'

declare @handle int

exec sp_xml_preparedocument @handle output, @XMLDoc

select * from MyTable where id in (
    select id from openxml( @handle, '/ids/id') with (id int '.')
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.