Sample Header Ad - 728x90

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

21 votes
2 answers
14789 views
According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters. At first I thought the goal of forcing TVP to be declared READONLY was to clearly signal to developers that the TVP can't be used as an OUTPUT parameter, but there must be more going on because we can't declare non-TVP as READONLY. For example the following fails: create procedure [dbo].[test] @a int readonly as select @a > Msg 346, Level 15, State 1, Procedure test
The parameter "@a" can not be declared READONLY since it is not a table-valued parameter. 1. Since statistics aren't stored on TVP what is the rationale behind preventing DML operations? 2. Is it related to not wanting TVP to be OUTPUT parameters for some reason?
Asked by Erik (4833 rep)
Nov 11, 2015, 04:04 PM
Last activity: Mar 17, 2021, 11:53 AM