如何传递参数执行存储过程并解决varchar转datetime类型报错问题

核心原因 #

该报错本质是SQL Server隐式日期转换规则不一致导致的：

• 直接执行INSERT语句时，SQL会参照当前会话的DATEFORMAT配置、目标列类型解析你传入的日期字符串，刚好匹配你的yyyy-MM-dd HH:mm:ss.fff格式所以能插入成功
• 调用存储过程时，参数的隐式转换优先采用服务器全局的日期格式配置，若全局配置和你传入的字符串格式不匹配，就会抛出转换失败错误

解决方法 #

1. 调用时显式转换日期格式 #

直接在调用语句中用CONVERT指定日期格式编码（121对应ODBC标准的yyyy-MM-dd HH:mm:ss.fff格式，不受服务器配置影响），调用语句修改为：

Financial.spInsertValidity 36168, 
CONVERT(datetime, '2021-10-14 14:43:07.530', 121), 
CONVERT(datetime, '2021-10-20 14:43:07.530', 121), 
147, 1, 0, 'thiagocorrea@pedidoeletronico.com'

2. 先声明日期类型变量再传参 #

提前定义datetime类型变量赋值，再传入存储过程，避免参数传递阶段的隐式转换：

DECLARE @dtInicioVigencia datetime = '2021-10-14 14:43:07.530';
DECLARE @dtFimVigencia datetime = '2021-10-20 14:43:07.530';

EXEC Financial.spInsertValidity 36168, @dtInicioVigencia, @dtFimVigencia, 147, 1, 0, 'thiagocorrea@pedidoeletronico.com';

3. 修改存储过程兼容字符串传参（长期兼容方案） #

如果需要支持直接传入日期字符串调用，可以调整存储过程的参数类型，内部统一做显式转换，修改后的存储过程参数部分如下：

Create Procedure Financial.spInsertValidity(
    @idEmpresa bigint
,   @dtInicioVigencia nvarchar(30) -- 改为字符串类型接收参数
,   @dtFimVigencia nvarchar(30) -- 改为字符串类型接收参数
,   @idProduto int 
,   @qtdProduto int
,   @stPeriodoTolerancia bit
,   @User nvarchar(256)
)
As
Begin
-- 内部显式转换为datetime类型
DECLARE @dtInicio datetime = CONVERT(datetime, @dtInicioVigencia, 121);
DECLARE @dtFim datetime = CONVERT(datetime, @dtFimVigencia, 121);

-- 后续逻辑中将原来使用@dtInicioVigencia、@dtFimVigencia的地方替换为@dtInicio、@dtFim即可

报错截图参考：

内容的提问来源于stack exchange，提问作者Thiago Corrêa