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