SQL INSERT CASE查询Int转Date失败：Azure SQL兼容级别150适配求助

已查阅多篇相关文章，了解到数据类型优先级是此类问题的常见诱因，但本次情况有所不同。

本地开发环境（兼容级别110） #

运行SQL Server数据库，兼容级别为110，创建以下两张表：

CREATE TABLE [dbo].[SyncDataRecords](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SyncDataID] [int] NOT NULL,
    [FldVar] [sql_variant] NULL,
 CONSTRAINT [PK_SyncRecords_1] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[SyncDataFields](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SyncDataID] [int] NOT NULL,
    [FldName] [nvarchar](128) NOT NULL,
    [FldBaseType] [nvarchar](32) NOT NULL,
    [PK] [bit] NOT NULL,
 CONSTRAINT [PK_SyncDataFields] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

通过应用程序的SqlClient.SqlCommand参数化命令，可成功执行以下查询：

DECLARE @FldTyp NVARCHAR(16);

SELECT @FldTyp = UPPER(FldBaseType)
FROM   SyncDataFields
WHERE  SyncDataID = @SyncDataID
       AND PK = 1;

INSERT INTO SyncDataRecords
            (SyncDataID,
             FldVar)
VALUES      (@SyncDataID,
             CASE
               WHEN @FldTyp = 'SQL_VARIANT' THEN CAST(@FldVar AS SQL_VARIANT)
               WHEN @FldTyp = 'NVARCHAR' THEN CAST(@FldVar AS NVARCHAR(4000))
               WHEN @FldTyp = 'BIGINT' THEN CAST(@FldVar AS BIGINT)
               WHEN @FldTyp = 'BIT' THEN CAST(@FldVar AS BIT)
               WHEN @FldTyp = 'DECIMAL' THEN CAST(@FldVar AS DECIMAL)
               WHEN @FldTyp = 'INT' THEN CAST(@FldVar AS INT)
               WHEN @FldTyp = 'MONEY' THEN CAST(@FldVar AS MONEY)
               WHEN @FldTyp = 'NUMERIC' THEN CAST(@FldVar AS NUMERIC)
               WHEN @FldTyp = 'SMALLINT' THEN CAST(@FldVar AS SMALLINT)
               WHEN @FldTyp = 'SMALLMONEY' THEN CAST(@FldVar AS SMALLMONEY)
               WHEN @FldTyp = 'TINYINT' THEN CAST(@FldVar AS TINYINT)
               WHEN @FldTyp = 'FLOAT' THEN CAST(@FldVar AS FLOAT)
               WHEN @FldTyp = 'REAL' THEN CAST(@FldVar AS REAL)
               WHEN @FldTyp = 'DATE' THEN CAST(@FldVar AS DATE)
               WHEN @FldTyp = 'DATETIME' THEN CAST(@FldVar AS DATETIME)
               WHEN @FldTyp = 'SMALLDATETIME' THEN CAST(@FldVar AS SMALLDATETIME)
               WHEN @FldTyp = 'TIME' THEN CAST(@FldVar AS TIME)
               WHEN @FldTyp = 'CHAR' THEN CAST(@FldVar AS CHAR)
               WHEN @FldTyp = 'VARCHAR' THEN CAST(@FldVar AS VARCHAR(4000))
               WHEN @FldTyp = 'NCHAR' THEN CAST(@FldVar AS NCHAR)
               WHEN @FldTyp = 'VARBINARY' THEN CAST(@FldVar AS VARBINARY)
               ELSE CAST(@FldVar AS SQL_VARIANT)
             END);

SELECT @@IDENTITY; 

测试时，设置@SyncDataID为1（整数），@FldVar为1234（整数），此时@FldTyp为'INT'，执行正常。

Azure SQL环境问题（兼容级别150） #

在Azure上创建相同的数据库和表，默认兼容级别为150：

• 通过SSMS执行该查询（声明必要变量）可成功运行；
• 通过应用程序连接Azure SQL数据库时，抛出错误：

Explicit conversion from data type int to date is not allowed

仅当将Azure SQL数据库的兼容级别降至110（与本地一致）时，应用程序才能成功执行。即使将CASE改为IF ELSE IF，仍会出现相同错误。

触发错误的应用代码 #

objInsPKCommand.CommandText = "DECLARE @FldTyp nvarchar(16);SELECT @FldTyp=UPPER(FldBaseType) FROM SyncDataFields WHERE SyncDataID=@SyncDataID AND PK=1;INSERT INTO SyncDataRecords (SyncDataID, FldVar) VALUES (@SyncDataID, CASE WHEN @FldTyp='SQL_VARIANT' THEN CAST(@FldVar AS sql_variant) WHEN @FldTyp='NVARCHAR' THEN CAST(@FldVar AS NVARCHAR(4000)) WHEN @FldTyp='BIGINT' THEN CAST(@FldVar AS BigInt) WHEN @FldTyp='BIT' THEN CAST(@FldVar AS Bit) WHEN @FldTyp='DECIMAL' THEN CAST(@FldVar AS decimal) WHEN @FldTyp='INT' THEN CAST(@FldVar AS Int) WHEN @FldTyp='MONEY' THEN CAST(@FldVar AS Money) WHEN @FldTyp='NUMERIC' THEN CAST(@FldVar AS Numeric) WHEN @FldTyp='SMALLINT' THEN CAST(@FldVar AS SmallInt) WHEN @FldTyp='SMALLMONEY' THEN CAST(@FldVar AS SMALLMONEY) WHEN @FldTyp='TINYINT' THEN CAST(@FldVar AS tinyint) WHEN @FldTyp='FLOAT' THEN CAST(@FldVar AS float) WHEN @FldTyp='REAL' THEN CAST(@FldVar AS Real) WHEN @FldTyp='DATE' THEN CAST(@FldVar AS Date) WHEN @FldTyp='DATETIME' THEN CAST(@FldVar AS DateTime) WHEN @FldTyp='SMALLDATETIME' THEN CAST(@FldVar AS SmallDateTime) WHEN @FldTyp='TIME' THEN CAST(@FldVar AS Time) WHEN @FldTyp='CHAR' THEN CAST(@FldVar AS char) WHEN @FldTyp='VARCHAR' THEN CAST(@FldVar AS VarChar(4000)) WHEN @FldTyp='NCHAR' THEN CAST(@FldVar AS NChar) WHEN @FldTyp='VARBINARY' THEN CAST(@FldVar AS varbinary) ELSE CAST(@FldVar AS sql_variant) END);SELECT @@IDENTITY;"
objInsPKCommand.Parameters.Add("SyncDataID", SqlDbType.Int)
objInsPKCommand.Parameters.Add("FldVar", SqlDbType.Variant)
objInsPKCommand.Parameters.Item("SyncDataID").Value = 1
objInsPKCommand.Parameters.Item("FldVar").Value = 1234
intPKID = objInsPKCommand.ExecuteScalar

SyncDataFields表示例数据 #

请求帮助 #

恳请告知该问题的变化节点及兼容级别150下的查询语句修改方案。

内容的提问来源于stack exchange，提问作者Almo