在动态查询中,SQL Server中从字符串到日期时间的转换失败

这是设置 @LastUpdated 使用 sp_executesql

-- Declaration
DECLARE @sql nvarchar(max)
DECLARE @LastUpdated datetime
DECLARE @err int

-- Statement
SET @sql = N'
    SELECT TOP 1 @LastUpdated = Purchase.LastUpdated
    FROM Purchase
    WHERE ID = 15
'
-- Execution
EXEC @err = sp_executesql 
    @sql,
    N'@LastUpdated datetime OUTPUT',
    @LastUpdated OUTPUT

-- Test output
IF @err = 0 
   PRINT @LastUpdated
ELSE 
   PRINT 'Error'

如果您试图传入一个常量值,那么使用 sp_executesql

DECLARE @LastUpdated datetime;
SET @Update = N'    
SELECT TOP 1 @LastUpdated as LastUpdated
FROM    Purchase
WHERE   ID = 15';

EXEC sp_executesql @Update,
                   N'@LastUpdated datetime',
                   @LastUpdated=@LastUpdated;  

要更新数据,需要使用 UPDATE . 但您不需要动态SQL:

UPDATE Purchase
    SET LastUpdated = @LastUpdated
    WHERE ID = 15;

如果 ID 不是唯一的,您要将其限制为一(任意)行:

UPDATE p
    SET LastUpdated = @LastUpdated
    FROM (SELECT TOP (1)
          FROM Purchase
         ) p
    WHERE ID = 15;

ORDER BY TOP 所以你可以控制你影响哪一行。