Share via

How to accept multiple rows as input for the stored procedure

Rishabh Patil 161 Reputation points
2022-09-14T14:32:29.363+00:00

Hi All,

I am stuck with one problem where I am trying to create a logic to accept multiple rows as a SP parameter from api and then to insert into table.

I am aware about string, xml, json etc but confused which is the best practice practice solution.

241103-image.png

So, how would I pass these values from api call?
Any help would be appreciated. thanks

Developer technologies | Transact-SQL
Accepted answer
  1. Michael Taylor 60,326 Reputation points
    2022-09-14T14:52:36.143+00:00

    The best practice is to use a Table-Valued Parameter assuming you're using a newer SQL version (2016+, maybe earlier). You then pass the table as a parameter to the sproc.

    To pass such parameters in ADO.NET you should refer to this doc.

    If for some reason that isn't available then strings are the easiest for simple data points but for more complex use XML if you need to pick the data apart using XPath. If you're running SQL 2016+ which has formal support for JSON then JSON might be smaller and faster. But at that point a TVP is probably the better choice anyway.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-09-14T21:35:02.713+00:00

    Provided that your client API supports table-valued parameters, that is the way to go. I have an article on my web site on how do to this from .NET: https://www.sommarskog.se/arrays-in-sql-2008.html. You will find that if you already have the data in a DataSet, it is very simple.

    1 person found this answer helpful.
  2. Tom Phillips 17,771 Reputation points
    2022-09-14T15:01:22.467+00:00

    Unless you must pass them all at the same time, I would suggest simply calling the proc 3 times in your code. It will be much easier to code and not really a performance issue for a few records. This is not a good option if you are inserting 1000s of records.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.