I continued to check SQL Server 2011 “Denali” for few things I dislike on 2008 R2 and tried to find out whether they were improved (first part is here).

User defined table value types are great step forward in T-SQL programming. They worth in many situations, for me it was encapsulating list of business objects with them and passing them to stored procedures.

Unfortunately you must treat these kind of parameters as readonly (with READONLY clause), so you cannot pass table variable in stored procedure and update it there.

Life will be much easier if you could do this (this code is obviously only schematic):

--------------------------------------------
-- create Stock table type
--------------------------------------------
CREATE TYPE Stock AS TABLE
(
    -- many important columns
    Value VARCHAR(500)
)
GO
--------------------------------------------
-- create stored proc which does some complex calculations over list of stocks
--------------------------------------------
CREATE PROC ap_Do_Some_Very_Complex_Calculation_Over_Stocks
    @stocks Stock  -- here should be READONLY clause
AS
BEGIN
    UPDATE @stocks
        SET Value = 'complex calculation'
    -- @stocks variable leaves this procedure changed
END
--------------------------------------------
-- do calculations over list of stocks
--------------------------------------------
DECLARE @stocks AS Stock 
INSERT INTO @stocks
    SELECT * FROM -- fill @stocks table from somewhere
EXEC ap_Do_Some_Very_Complex_Calculation_Over_Stocks @stocks
-- here @stocks table content would be changed by procedure

I was eager to try it in SQL Server 2011 “Denali” but was dissappointed because there was no improvement of this feature (still hoping that “yet”).

Here is proof, you are still required to make table value type readonly while used as SP parameter in SQL Server 2011 “Denali”:

Martin Catherall commented my previous post and advised me to raise connect item if not exists yet. Connect item for this issue already exists for quite long time but with promising comment from Microsoft that it may be implemented in version 2011. Let’s hope.

In case this features will not be introduced in SQL Server 2011, you can use “workaround” with XML datatype variable as input parameter to stored procedure. This approach is described with connect item I’ve referred to. But If you are focused at least a little to performance, I wouldn’t recommend it. XML handling has always performance drawbacks and cannot replace table value type especially if you update it within stored procedure. You can also read excellent article on this theme here – Why Read-only Table Parameters is Not Enough.