Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)
Error Messages
Home > SQL Server Error Messages > Msg 346 - The parameter "<Parameter Name>" can not be declared READONLY since it is not a table-valued parameter.
SQL Server Error Messages - Msg 346 - The parameter "<Parameter Name>" can not be declared READONLY since it is not a table-valued parameter.

Error Message

Server: Msg 346, Level 15, State 1, Line 1
The parameter "<Parameter Name>" can not be declared
READONLY since it is not a table-valued parameter.

The READONLY attribute of a parameter was introduced in SQL Server 2008 together with the table-valued parameter. As the attribute suggests, READONLY indicates that the parameter cannot be updated or modified within the body of the procedure or function. If the parameter type is a table-valued parameter, then READONLY must be specified.

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.

Since the READONLY attribute is only applicable to table-valued parameters, using it in any other parameters will generate this error message, as can be seen in the following user-defined function:

CREATE FUNCTION [dbo].[ufn_Factorial] ( @Integer INT READONLY )
RETURNS INT
AS
BEGIN
    DECLARE @Factorial    INT

    WHILE @Integer > 0
    BEGIN
        SET @Factorial = ISNULL(@Factorial, 1) * @Integer
        SET @Integer = @Integer - 1
    END 
	
    RETURN @Factorial
END
GO

Msg 346, Level 15, State 1, Procedure ufn_Factorial, Line 2
The parameter "@Integer" can not be declared READONLY since it is not a table-valued parameter.

Solution / Work Around

Since the READONLY attribute is only applicable to table-valued parameters, and as the error message suggests, simply remove this attribute when creating a stored procedure or function if the parameter is not a table-valued parameter.

CREATE FUNCTION [dbo].[ufn_Factorial] ( @Integer INT )
RETURNS INT
AS
BEGIN
    DECLARE @Factorial    INT

    WHILE @Integer > 0
    BEGIN
        SET @Factorial = ISNULL(@Factorial, 1) * @Integer
        SET @Integer = @Integer - 1
    END 
	
    RETURN @Factorial
END
GO

If the reason for adding this attribute in the parameter definition is to make sure that the value passed to the parameter of the stored procedure or function does not get modified by the stored procedure or function, it should be noted that parameters are passed by value and not passed by reference. This means that the stored procedure or function makes a local copy of the parameter before doing any updates on it.

To illustrate, here’s a script that uses the function shown above.

DECLARE @Input        INT
DECLARE @Factorial    INT

SET @Input = 5
SET @Factorial = [dbo].[ufn_Factorial] ( @Input )

SELECT @Input AS [Input], @Factorial AS [Factorial

Input   Factorial
------- -----------
5       120

As can be seen from this script, the parameter or local variable @Input retained its value even if it was modified within the function.

Related Articles :