Using a stored procedure with where value in () [duplicate]

Question

This question already has answers here:

How to pass an array into a SQL Server stored procedure (13 answers)

T-SQL stored procedure that accepts multiple Id values (5 answers)

Closed 8 years ago.

So, In my stored procedure I have

Update Table
Set Table.Value = 2
Where Table.ID In (1,2,3)

I call the stored procedure and I want to pass in the 1,2,3. There is an unknown number of values in this list so I can't just use several variables.

I have tried the following but this doesn't work

Declare @IDS Varchar = '1,2,3';
Update Table
Set Table.Value = 2
Where Table.ID In (@IDS)

Is there an array type (or similar) in T-SQL that would allow me to pass multiple values in for this purpose?

Thanks

• ahmed abdelqader: Use Dynamic query.
• Panagiotis Kanavos: I'd add to the duplicate answers that SQL Server 2016 has STRING_SPLIT

Answer 1

You can pass either table variable to stored procedures or xml variable. I prefer xml, because it's more elastic.

Example:

 GO
CREATE PROCEDURE #tmpProcedure
 @xml xml
AS
 UPDATE tbl SET 
 Value = 2
 FROM table tbl
 WHERE 1 = 1 
 AND EXISTS (SELECT 1 
 FROM @xml.nodes('IdsToUpdate/Id') t(x)
 WHERE 1 = 1 
 AND t.x.value('.','int') = tbl.Id
 )
GO
DECLARE @xml xml =
 '
 <IdsToUpdate>
 <Id>1</Id>
 <Id>14</Id>
 <Id>15</Id>
 <Id>2</Id>
 </IdsToUpdate>
 '
GO
EXEC #tmpProcedure @xml

• Panagiotis Kanavos: The code is more cumbersome and probably results in a fullt able scan too. With a TVP you'd only need to join the target table with the parameter. You could improve performance if you extracted the IDs and placed them in a table variable. But why do that, when you can just use a table parameter?
• michał kudanowski: Table parameter require from you to create data type
• michał kudanowski: Another easy way is to create tmp table before you call your stored procedure. That allow you to use tmp table and it's values within stored procedure.
• Panagiotis Kanavos: Yes, and xml requires that you create and hard-code a schema and query. They also require that you pay attention to avoid a full table scan
• Panagiotis Kanavos: As for the temporary table, why? Is that in any way easier than using a table type? Don't forget concurrency issues