存储过程tsql中的数据转换

I have column of type of datetime, that I am using in my stored procedure by declaring the two local variables as @From datetime and @To datetime, but no matter what I do I get the error or it simply run the stored procedure without returning any records(completely blank).

我有一个datetime类型的列，我在存储过程中使用它，通过声明两个本地变量为@From datetime和@To datetime，但是无论我做什么，我都会得到错误，或者它只是运行存储过程而不返回任何记录(完全为空)。

set @mySql ='
select * from abc where (MyDATE between '''+ cast(@From as datetime) +''' and '''+ cast(@To as datetime)+''')'

2 个解决方案

#1

Try to keep your data in variables of the appropriate type, whenever possible.

尽可能将数据保存在适当类型的变量中。

For instance, here you can do:

例如，你可以这样做:

--@From and @To are declared previously as datetimes
set @mySql ='select * from abc where (MyDATE between @From and @To)'

--Other code that constructs/works on @mySQL

--Finally, run the dynamic sql:
EXEC sp_executesql @mySql,
                   '@From datetime,@To datetime`,
                   @From,
                   @To

And everything should work beautifully because you're not forcing back and forth between strings and datetimes, and its those conversions that introduce the opportunity to have formatting issues.

所有东西都应该运行得很好，因为您不会在字符串和日期时间之间来回转换，而正是这些转换引入了出现格式问题的机会。

#2

The only "correct" way to do this is to preserve them as parameters inside the dynamic SQL. For example:

唯一“正确”的方法是将它们保存为动态SQL中的参数。例如:

set @mySql =N'select * from abc where MyDATE between @from and @to';

exec sp_executesql @mySql, N'@from datetime, @to datetime', @fromOuter, @toOuter;

This keeps them correctly typed in the dynamic code, and avoids both formatting concerns and SQL injection risks. Note that the names inside and outside the dynamic code do not need to match, as shown in the example above (@from and @to are the names in the dynamic code; @fromOuter and @toOuter are the names in the calling code).

这将使它们在动态代码中保持正确的类型，并避免格式化问题和SQL注入风险。注意，动态代码内外的名称不需要匹配，如上面的示例所示(动态代码中的名称为@from和@to);@fromOuter和@toOuter是调用代码中的名称)。

Note that it doesn't matter if you pass in more parameters than you actually use (this would be pretty normal for a dynamic filtering method).

请注意，如果传入的参数比实际使用的多，这并不重要(对于动态过滤方法来说，这是非常正常的)。

#1