SQL Server sp_execute -在动态SQL中使用DateTime变量出错

Question

我尝试使用sp_executesql创建动态SQL，但它给我这个错误消息:

Msg 137，第15层，第2州，第20行
必须声明标量变量"@start"

这是我的存储过程脚本

CREATE PROCEDURE sp_test (@start datetime, @end datetime)
AS
BEGIN
   DECLARE @sql nvarchar(MAX)
   SET @sql = 'SELECT * FROM table1 WHERE '
   SET @sql = @sql + N'startDate BETWEEN @start AND @end'
   EXEC sp_executesql @sql

Answer 1

下面的T-SQL应该可以解决您的问题。不过，我不建议在存储过程名称前加上"sp_"前缀，因为系统存储过程使用这种命名约定。你不会想让你的存储过程和系统存储过程混淆，或者更糟的是，微软决定用你的名字来命名他们未来的一个系统存储过程。

注意事项:

• 在@ParameterDefinition变量中定义/声明所有想要传递到动态SQL语句中的自定义参数。

• 您将每个自定义变量添加到sp_executesql调用中，就好像它们已经是过程的一部分一样。

  IF OBJECT_ID('sp_test', 'P') IS NOT NULL DROP PROCEDURE sp_test
  GO
  -- ============================================================================
  -- CALLING EXAMPLE:
  -- EXEC sp_test '01/01/1901', '01/02/1901'
  -- ============================================================================
  CREATE PROCEDURE sp_test (@start datetime, @end datetime)
  AS
  BEGIN
  DECLARE @sql nvarchar(max)
  SET @sql = 'SELECT * FROM table1 WHERE '
  SET @sql = @sql + N'startDate BETWEEN @start AND @end'
  -- Build the Parameter Definition list for the dynamic SQL statement below  
  DECLARE @ParameterDefinition nvarchar(1000);
  SELECT @ParameterDefinition = ''  
  + ' @start datetime'  
  + ',@end datetime' 
  EXEC sp_executesql  
      @statement  = @sql  
     ,@params     = @ParameterDefinition  
     -- Assign values to any of custom parameters defined in @ParameterDefinition:  
     ,@start      = @start  
     ,@end        = @end  
  END
  GO