OUTPUT as default parameter value for stored procedure?
5
votes
1
answer
235
views
I've encountered peculiar behaviour by SQL Server, and I wonder why does it behave like this.
Consider following CREATE PROCEDURE script:
CREATE PROCEDURE dbo.myProc
@parameter INT = OUTPUT
AS
BEGIN
PRINT @parameter;
SET @parameter = 10;
END
Notice the way parameter is defined - it seems like the integer parameter with default value of OUTPUT.
That is, of course, ridiculous.
It is also exactly how it behaves. Why?
If that was default value, I would expect it to be wrapped in single quotes (e.g. @parameter INT = 'OUTPUT'
).
If it was keyword, specifying that this is OUTPUT parameter, I would expect it to fail, since there is redundant =
.
The [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16#:~:text=Transact%2DSQL%20syntax%20for%20stored%20procedures%20in%20SQL%20Server%20and%20Azure%20SQL%20Database%3A) seem to suggest possible formats are @paramter INT OUTPUT
or @parameter INT = OUTPUT
, where the former does not specify default value, and the latter does. However, the mangled middle form does seem to "work" as well. (At least in sense of being valid syntax.) I wonder if it is bug or some obscure interaction of known features.
[Fiddle](https://dbfiddle.uk/5Fwga1Td) .
Asked by Yano_of_Queenscastle
(1998 rep)
May 6, 2025, 01:38 PM
Last activity: May 6, 2025, 05:02 PM
Last activity: May 6, 2025, 05:02 PM