|
|
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.
|