SQL Server How can this proc execute work without quoting passed parameter value?

vtwuwzda  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(80)

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
GO

and

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.

gjmwrych

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:

EXEC spParameterTest
     @a = 1
    ,@b = StringOf128Charactersxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    ,@c = '2023-10-20';

The above results in successful proc execution with the value truncated to 50 characters.

Passing a longer value:

EXEC spParameterTest
     @a = 1
    ,@b = StringOf129Charactersxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
    ,@c = '2023-10-20';

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.

相关问题