SQL要求声明声明的变量

当您exec(@sql)使用自己的局部变量创建新上下文时,并且尚未将@startDate值传递到该上下文中.

相反,为您的SQL字符串声明参数,如下所示:

exec sp_executesql @sql, '@startDate datetime, @endDate datetime', 
      @startDate, @endDate;

这些名称现在可供SQL使用,参数将传递给它们.

此方法更好,因为它将变量视为更安全的参数,因为它降低了SQL注入的风险.

作为附加提示,您还应该将@storeName作为参数传递.据我所知,你不能将@dbname作为参数传递,所以你应该确保它被正确引用.

所以完整的事情是:

Set @sql = 'Insert Into #storeinfo (storename, employeename, employeeaddress, employeephone) '
        +' Select @storename As ''storename'', '
        +' employeename, employeeaddress, employeephone '
        +' From '+QUOTENAME(@dblocation)+' '
        +' where employeestatus = ''Active'' '
        +' and CAST(hiredate As Date) '
        +' BETWEEN CAST(@startDate As Date) AND CAST(@endDate As Date) '


Print(@sql)
exec sp_executesql @sql, 
    '@startDate datetime, @endDate datetime, @storeName nvarchar(100)', 
    @startDate, @endDate, @storeName;