SQL Server中sp_executesql调用存储过程时datetime参数失效问题排查及正确写法咨询

Great question—let's jump right into what's broken and how to fix it.

The Problem in Your Code #

The core issue is a disconnect between the dynamic SQL statement you're executing and the parameters you're passing to sp_executesql:

• In your @statement value (N'mysp @timestamp=0'), you're hardcoding @timestamp=0 instead of using a placeholder that links to the @0 parameter you defined.
• This means the @0 = '2021-04-08 20:59:10.987' value you're passing is completely ignored. Instead, your stored procedure mysp receives the literal 0, which SQL Server automatically converts to the datetime 1900-01-01 00:00:00.000 (the default "zero" value for datetime types).

Fixed sp_executesql Call #

You need to update the dynamic statement to reference the parameter placeholder, then map it to your stored procedure's @TimeStamp parameter. Here's the correct syntax:

exec sp_executesql 
    @statement = N'mysp @TimeStamp = @0', -- Map the sp_executesql parameter @0 to the procedure's @TimeStamp
    @params = N'@0 datetime', 
    @0 = '2021-04-08 20:59:10.987'

Quick Best Practices to Avoid This Issue #

• Always use parameter placeholders for dynamic SQL that accepts inputs—this not only fixes issues like yours but also prevents SQL injection.
• Explicitly name parameters when calling stored procedures (like @TimeStamp = @0) instead of relying on positional order. This makes your code more readable and less prone to breakage if the procedure's parameter order changes later.
• Double-check that the parameter data types in @params match both the value you're passing and the stored procedure's parameter type (in this case, datetime is consistent, which is good).

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