Using a stored procedure with where value in ()

Question

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

I'd add to the duplicate answers that [SQL Server 2016 has `STRING_SPLIT`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) — Panagiotis Kanavos, Jun 13 '17 at 12:17

michał kudanowski · Answer 1 · 2017-06-13T11:59:09.703

0

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

edited Jun 13 '17 at 11:59

answered Jun 13 '17 at 11:54

michał kudanowski

33
6

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? – Panagiotis Kanavos Jun 13 '17 at 12:10
Table parameter require from you to create data type – michał kudanowski Jun 13 '17 at 12:12
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. – michał kudanowski Jun 13 '17 at 12:13
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 Jun 13 '17 at 12:13
As for the temporary table, why? Is that in any way *easier* than using a table type? Don't forget concurrency issues – Panagiotis Kanavos Jun 13 '17 at 12:14

Using a stored procedure with where value in ()

1 Answers1