How can this code work? I've never found using text without delimiting with single quotes to work, but this does and should not. Maybe I've found a bug in SQL Server. If so, do I get a medal?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Trevor
-- Create date: 20/10/2023
-- Description: Parameter Test
-- =============================================
CREATE OR ALTER PROCEDURE spParameterTest
@a INT
,@b VARCHAR(50)
,@c DATE
AS
BEGIN
SELECT
@a AS a
,@b AS b
,@c AS c
END
GOand
EXEC spParameterTest
@a = 1
,@b = Trevor
,@c = '2023/10/20'Gives unexpected results, as
a|b|c
1|Trevor|2023-10-20
(1 row affected)Expected results
EXEC spParameterTest
@a = 1
,@b = 0Trevor
,@c = '2023/10/20'causes this
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Trevor'.but this
EXEC spParameterTest
@a = 1
,@b = '0Trevor'
,@c = '2023/10/20'gives
a|b|c
1|0Trevor|2023-10-20
(1 row affected)as expected.
1条答案
按热度按时间gjmwrych1#
SQL Server parses the unquoted string as an identifier and, if valid, uses the value as the procedure parameter value. This can be observed as follows:
The above results in successful proc execution with the value truncated to 50 characters.
Passing a longer value:
results in the error below. Note the word "identifier" in the error message.
Msg 103, Level 15, State 4, Line 233 The identifier that starts with 'StringOf129Charactersxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' is too long. Maximum length is 128.