SQL Server query with list parameter

Question

I am using SQL Server and in a stored procedure I want to execute a query with a list parameter something like this:

select * from table where type in @list_types

Is it possible to make this? Or must I use temporary tables?

everything after 2008 you can use the http://msdn.microsoft.com/en-us/library/bb675163.aspx (table valued parameters). Otherwise you would have to rely on a platform above. Some discussion about this can be found here: http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — Najzero, May 22 '13 at 07:57
This question shows lack of research. http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause (one of hundreds) — Tim Schmelter, May 22 '13 at 08:15

score 2 · Accepted Answer · answered May 22 '13 at 08:01

You could use table-valued parameters. For example:

-- A table valued parameter must have a type.
-- This command creates the type.
create type YourType as table (type varchar(50))
go
create procedure dbo.YourStoredProcedure(
    @types YourType readonly)
as
    select  *
    from    YourTable
    where   type in (select type from @types)
go

You can invoke the stored procedure like this:

declare @types YourType
insert @types (type) values ('Type1'), ('Type2')
exec dbo.YourStoredProcedure @types

ADO.NET supports passing a DataTable as a table-valued parameter.

score 1 · Answer 2 · answered May 22 '13 at 07:57

Try this one -

DECLARE @temp TABLE
(
      [type] INT
    , name NVARCHAR(50)
)

INSERT INTO @temp ([type], name)
VALUES 
    (1, '1'),
    (2, '2')

DECLARE @list_types VARCHAR(30)
SELECT @list_types = '1,3,4,5'

;WITH cte AS 
(
    SELECT [type] = p.value('(./s)[1]', 'INT') 
    FROM (
        SELECT field = CAST('<r><s>' + REPLACE(@list_types, ',', '</s></r><r><s>') + '</s></r>' AS XML) 
    ) d
    CROSS APPLY field.nodes('/r') t(p)
)
SELECT *
FROM @temp
WHERE [type] IN (SELECT [type] FROM cte)

SQL Server query with list parameter

2 Answers2