Passing an array of parameters to a stored procedure

sql

tvp

xml-serialization

dynamic-sql

byNikita Barsukov·Jan 1, 2025

To pass an array of parameters to a SQL stored procedure, consider using table-valued parameters (TVPs). First, you need to create a custom table type to match the structure of your array. This custom type can then play its role in your stored procedure's paramaters! Let's populate this table and execute that procedure & voilà - you just manipulated an array, SQL style!

In other words, lend your array the form of a table - SQL loves tables!

Picturing it with SQL Server:

-- Whoops, I just made an array
CREATE TYPE AwesomeArrayType AS TABLE ( MagicNumber INT );

-- Now, let's make a stored procedure to handle these "arrays"
CREATE PROCEDURE AddAwesomesauce
    @TheArrayItself AwesomeArrayType READONLY
AS
    SET NOCOUNT ON; -- Pssst, SQL Server becomes superfast with this. Don't tell anyone, our little secret.
    INSERT INTO SomePlaceWelcoming (SomePlaceColumn)
    SELECT MagicNumber FROM @TheArrayItself;

-- Now, the Grand Finale. Populating the array and executing the procedure!
DECLARE @MagicList AwesomeArrayType;
INSERT INTO @MagicList VALUES (10), (42), (66); -- Here's some magic for you.
EXEC AddAwesomesauce @MagicList;

You just transformed @TheArrayItself into an array equivalent, put some magic numbers with INSERT INTO @MagicList and finally EXEC pulls off the whole act, passing the array into the procedure.

Unleashing Advanced TVP's Powers

Cooking with XML Serialization

When TVPs decide to play hard to get, XML serialization comes to the rescue. Here's how XML turns on the charm:

CREATE PROCEDURE AddMagicsWithXML
    @MagicXML XML
AS
    INSERT INTO SomePlaceWelcoming (SomePlaceColumn)
    SELECT N.value('.', 'INT') -- I see right through you, XML. Nice values you got there!
    FROM @MagicXML.nodes('/SomePlace/MagicNumber') as T(N);

Turns out XML can be a great chef, cooking array-like delicacies right in your SQL kitchen.

Dynamic SQL: The Master of Disguise

For a more theatric performance with variably structured parameters, don dynamic SQL. This method can be tricky, disguising itself as your best friend but proving to be your worst nightmare if not used carefully. Beware of SQL Injection monsters hiding in the shadows!

CREATE PROCEDURE AddMagicNumbersDynamic
    @MagicNumbers VARCHAR(MAX) -- Max? Max who? Max Power!
AS
    EXEC ('INSERT INTO SomePlaceWelcoming (SomePlaceColumn) VALUES ' + @MagicNumbers); -- SQL, stop being a show-off!

Riding the dynamic SQL rollercoaster securely requires affirming comma placement and string validation. Safety first!

Engaging the Splits: String Splitting Function

Introducing your array in a "comma-separated tale"? Our charismatic SQL Server 2016 will engage in estimating readability:

--Let the splitting begin!
SELECT [value]
FROM STRING_SPLIT(@String, ',');

Splitting strings into rows proficiently engages performance and entertains larger audiences. However, the split function craves additional parsing, you've been warned.

Real-World Experience Unveiled

The 'NOT IN' clause & Data Deletion

Deleting records not present in the array? Call in the heavy-duty NOT IN clause:

-- The typename's contract enters the stage
DECLARE @Ids AwesomeArrayType;
INSERT INTO @Ids VALUES (1), (3), (4); -- Some numbers walk into a bar...
DELETE FROM DataTable 
WHERE IdColumn NOT IN (SELECT MagicNumber FROM @Ids); -- ...and the bartender says "We don't serve their type here"

The NOT IN clause flexes its muscles, power-packing array-like logic.

Maintaining Integrity: Transactions & Locking

To keep your data secure during massive operations, engage with transactions and all-about-business locking:

BEGIN TRANSACTION
TRY
    -- Yourdelete/update operation goes here
    COMMIT
CATCH
    ROLLBACK

Transactions ensure operations are atomic and deliver the punchline to the integrity joke.

Entity Framework: An Alternative Reality

For .NET developers, Entity Framework presents a rosier reality abstracting the TVP logic:

context.Database.ExecuteSqlCommand("EXEC AddAwesomesauce @MagicList", new SqlParameter("@MagicList", magicList));

Entity Framework is like the cool uncle every .NET developer wishes they had.

explain-codes / Sql / Passing an array of parameters to a stored procedure

Linked

How can I query a value in SQL Server XML column



Convert SQL Server result set into string



Convert Xml to Table SQL Server



Maximum size for a SQL Server Query? IN clause? Is there a Better Approach



How to use GROUP BY to concatenate strings in SQL Server?



Use a LIKE statement on SQL Server XML Datatype



Split function equivalent in T-SQL?



Unleashing Advanced TVP's Powers Real-World Experience Unveiled