CODEWITHSUNDEEP
sqlsql-servert-sqlc#-4.0
Khan
Khan

Reputation: 271

Date convertion in stored procedure 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).

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

Upvotes: 2

Views: 60

Answers (3)

user3104783
user3104783

Reputation: 19

the issue here is that when you are building the Dynamic SQL, you are looking to cast your parameters as DateTime.

What you should actually do is avoid the use of casting. Set the Parameters as date time and store required values before you use them to build your dynamic SQL Statement.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062510

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

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).

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).

Upvotes: 2

Related Questions