SQL Server Error Messages - Msg 352

Error Message

Server: Msg 352, Level 15, State 1, Line 1
The table-valued parameter <Parameter Name> must be declared with the READONLY option.

Causes

Table-valued parameters are a new parameter type introduced in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement, stored procedure or function without creating a temporary table or many parameters.

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 benefit of providing a simple programming model, being strongly typed, not having to acquire locks for the initial population of data from a client, enabling of the inclusion of complex business logic in a single routine, having a table structure of different cardinality and enabling the client to specify sort order and unique keys.

One of the restrictions of table-valued parameters when passed to a stored procedure or user-defined function is that they must be passed as input READONLY parameters. If the table-valued parameter is not passed as an input READONLY parameter, then this error message will be generated.

To illustrate, given the following [Product] table type with the given table structure as well as the [dbo].[Product] table, a stored procedure needs to be created that will accept as a parameter the list of new products stored in a variable with the [Product] table type. The output of the stored procedure will be the same set of newly created products together with the system-generated ID.

CREATE TYPE [Product] AS TABLE (
    [ProductID]      INT,
    [ProductName]    NVARCHAR(100),
    [UnitPrice]      MONEY,
    [Quantity]       INT
)
GO

CREATE TABLE [dbo].[Product] (
    [ProductID]    INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [ProductName]  NVARCHAR(100),
    [UnitPrice]    MONEY,
    [Quantity]    INT
)
GO

The following stored procedure, which accepts a list of new products to be inserted in the [dbo].[Product] table and as an output returns the same list of products but with the system-generated Product ID, will produce this error since the table-valued parameter is not declared as READONLY parameter:

CREATE PROCEDURE [dbo].[usp_ProcessNewProducts]
    @NewProducts          Product READONLY,
    @NewProductsWithID    Product OUTPUT
AS

    INSERT INTO [dbo].[Product] ( [ProductName], [UnitPrice], [Quantity] )
    OUTPUT [inserted].[ProductID], [inserted].[ProductName],
           [inserted].[UnitPrice], [inserted].[Quantity]
    INTO @NewProductsWithID
    SELECT [ProductName], [UnitPrice], [Quantity]
    FROM @NewProducts
GO

Msg 352, Level 15, State 1, Procedure usp_ProcessNewProducts, Line 3
The table-valued parameter "@NewProductsWithID" must be declared with the READONLY option.

Solution / Work Around

As mentioned earlier and as the error message suggests, one of the restrictions of table-valued parameters when passed to a stored procedure or user-defined function is that the table-value parameter must be passed as input READONLY parameter.

Adding the READONLY property to the OUTPUT parameter in the stored procedure above will not solve the problem as a different error message will be encountered:

CREATE PROCEDURE [dbo].[usp_ProcessNewProducts]
    @NewProducts          Product READONLY,
    @NewProductsWithID    Product READONLY OUTPUT
AS

    INSERT INTO [dbo].[Product] ( [ProductName], [UnitPrice], [Quantity] )
    OUTPUT [inserted].[ProductID], [inserted].[ProductName],
           [inserted].[UnitPrice], [inserted].[Quantity]
    INTO @NewProductsWithID
    SELECT [ProductName], [UnitPrice], [Quantity]
    FROM @NewProducts

GO

Msg 102, Level 15, State 1, Procedure usp_ProcessNewProducts, Line 3
Incorrect syntax near 'READONLY'.

In addition to having the table-valued parameter defined as READONLY in the stored procedure, DML operations such as UPDATE, DELETE or INSERT on the table-valued parameter in the body of the stored procedure is not allowed.

To work around this restriction with table-valued parameter and still be able to accomplish the task of retrieving the system generated Product ID for each new product pass as a table-valued parameter, the stored procedure needs to be modified to remove the second table-valued parameter and instead have this variable populated outside the stored procedure:

CREATE PROCEDURE [dbo].[usp_ProcessNewProducts]
    @NewProducts    Product READONLY
AS

    INSERT INTO [dbo].[Product] ( [ProductName], [UnitPrice], [Quantity] )
    OUTPUT [inserted].[ProductID], [inserted].[ProductName],
           [inserted].[UnitPrice], [inserted].[Quantity]
    SELECT [ProductName], [UnitPrice], [Quantity]
    FROM @NewProducts

GO

DECLARE @NewProductsWithID    Product
DECLARE @NewProducts          Product

INSERT INTO @NewProductsWithID ( [ProductID], [ProductName], [UnitPrice], [Quantity] )
EXECUTE [dbo].[usp_ProcessNewProducts] @NewProducts
GO

As can be seen from the updated stored procedure, it now returns, as a result set, the newly inserted products together with the system-generated Product ID. The stored procedure uses the OUTPUT clause, introduced in SQL Server 2005, within the INSERT statement to accomplish this task. The newly inserted products together with the Product ID are then inserted into the table-valued variable.