Parser allows stored procedure default assignment with no =, contrary to spec
according to it parameter defaulting is optional but requires =
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
specifically [ = default ]
However, omitting = is allowed and SP gets created but would fail to execute, i.e. bug.
This bug is easy to make and hard to catch.
Please ensure that such construct cannot be used when SP is created/altered
-- SP creation succeeds
CREATE OR ALTER PROCEDURE dbo.spTest
@orgID smallint NULL -- incorrect
--@orgID smallint = NULL -- correct
AS
SET NOCOUNT ON
GO
-- but attempts to execute fails
EXECUTE dbo.spTest
GO
Msg 201, Level 16, State 4, Procedure dbo.spTest, Line 0 [Batch Start Line 6]
Procedure or function 'spTest' expects parameter '@orgID', which was not supplied.
Vladimir Moldovanenko
shared this idea
1 comment
-
Vladimir Moldovanenko
commented
Also, this is not allowed. Why NULL should be allowed?
CREATE OR ALTER PROCEDURE dbo.spTest
@orgID smallint 5 -- incorrect
--@orgID smallint = NULL -- correct
AS
SET NOCOUNT ON
GO