1

I have a stored procedure that accepts a table-valued parameter.

I am passing a Datatable from my C# DAL. Are TVPs guaranteed to retain the order of rows when invoking the stored proc. My guess is that it should be (in which case this is a dumb question to ask). But I just want to be dead-sure about it.

Thanks

Vikas

Viking22
  • 545
  • 1
  • 7
  • 19
  • Do you want the _caller_ to determine the order or do you intend to _require_ an order? If the latter then you could just order the TVP in the sproc. Why does the order matter? – D Stanley Oct 24 '13 at 14:34
  • Let me clarify; There is already an interface between the C# code and sproc of TVPs that allows sending a List of records.In a specific case,I need to send just 2 records(as of now only 2; going further it might be more) with the caveat that the 2 records should be saved in order.i want to reuse this interface - hence want to make sure that the order does not get scrambled when invoking the sproc using TVP. – Viking22 Oct 24 '13 at 14:39
  • can't you just have two columns in your TVP, where one is your sequence? – KM. Oct 24 '13 at 14:41

1 Answers1

1

Order should be preserved.

From MSDN:

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits:

  • Do not acquire locks for the initial population of data from a client.
  • Provide a simple programming model.
  • Enable you to include complex business logic in a single routine.
  • Reduce round trips to the server.
  • Can have a table structure of different cardinality.
  • Are strongly typed.
  • Enable the client to specify sort order and unique keys.
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • I read the documentation to state that the _client_ specifies the sort order, thus the server does not change it. – D Stanley Oct 30 '13 at 12:42
  • I'm not sure if "enables the client to specify sort order" means that it retains the order in which the values were. Maybe it just means that you _could_ specify it, f.e. by adding a sort-column to the type, provide it and sort by that column. Actually i also need to know if the order is guaranteed. – Tim Schmelter Sep 13 '16 at 09:51