Why do we need table value parameter

Question

We have access to actual table inside storedprocedures . Then what is the need to pass table through parameter? Any special advantage?

Answer 1

Table-valued parameters are necessary to pass tabular data to a stored-procedure or function in a way that's "safe", especially from client-code (e.g. SqlCommand and SqlParameter).

The main alternative technique is to create and INSERT into a #temporaryTable first before calling the sproc, but temporary tables are not exactly temporary and they exist in tempdb which introduces namespacing and concurrency issues. You will also have to use Dynamic SQL because you cannot parameterise table names. With non-temporary tables the same issues apply.

Additionally, if you're wanting to pass data to a FUNCTION that you want to throw-away afterwards then you cannot use a temporary table because FUNCTION code is strictly read-only: it cannot drop the temporary table when it's done with it, whereas a table-valued parameter magically disappears after it falls out of scope.

It's also absolutely required if one FUNCTION wants to pass tabular data to another function for the same read-only reason: the function is not allowed to create a #temporarytable, but it can create and populate a table-valued parameter.

By analogy, it's like passing variables on the stack vs. on the heap - using the stack means you get automatic lifetime management and ownership semantics and not need to worry about concurrency as much - whereas using the heap introduces a whole load of issues.

An example

Supposing your application code needs to pass a list of tuples (or a list of primary-keys) to a stored procedure or FUNCTION - or if an existing sproc or function needs to pass data on to another function.

Using temporary-tables your code has to do this:

1. Create and open a SqlConnection.
2. Create and begin a TRANSACTION.
3. Create a new #temporarytable
   • Temporary tables are scoped to the current database session. This is okay for most purposes, but it means you cannot perform multiple concurrent database operations on that temporary table in the same session.
4. LOCK any normal tables you'll be using if necessary because your operation will span several SqlCommand executions.
5. From the client-side or originating stored-procedure, execute an INSERT statement to fill the temporary table. If you're inserting data from a client application you may need to execute a single-row INSERT operation many times too - this is very inefficient, especially in high-latency connection environments because the TDS protocol used by SQL Server is very chatty (ironically, you can perform a single multi-row INSERT operation using SqlCommand but you have to use a Table-Valued Parameter to contain the multiple rows of data).
6. Call the sproc or FUNCTION that will use the temporary table.
7. Tear down the #temporaryTable if you'll be keeping the session alive, or end the session immediately to prevent wasting memory.

But if you use a Table-Valued parameter it's much simpler:

1. Create and open a SqlConnection.
2. Create and begin a TRANSACTION.
3. Create your SqlCommand object that will call your sproc or FUNCTION, but you can use a Table-Valued Parameter directly and populate it all from the client in a single pass. The client software then pushes the table data stream to the server in a single operation which is far more efficient.
4. The sproc then runs. No teardown or session-ending is necessary.