score: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 datetime
s, and its those conversions that introduce the opportunity to have formatting issues.
Read More
- Date convertion in stored procedure tsql
- Last Run Date on a Stored Procedure in SQL Server
- How to know TSQL Stored Procedure Update Executed
- Calling a SOAP webservice from TSQL stored procedure
- Executing stored procedure takes too long than executing TSQL
- Is it possible to run stored procedure to set value in update statement - TSQL
score:0
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.
score:2
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).
More questions
- TSQL dynamic adding of columns in stored procedure
- TSQL mutual exclusive access in a stored procedure
- How to Dynamically Add to the TSQL Where Clause in a Stored Procedure
- ms sql call stored procedure with date time parameter
- Stored procedure with nullable value in date field
- Dynamic Stored Procedure with View name and Date as parameters
- TSql Getting meta data of stored procedure parameters
- TSQL - Parse Execution Plan to determine columns to be returned by a stored procedure
- Validating Date Parameter in SQL Server Stored Procedure
- SQL Server stored procedure to add days to a date
- Stored procedure date parameter not working with cast
- Will TSQL return faster results than stored procedure in SQL Server
- Stored procedure to accept starting date and number of consecutive dates beginning from start date
- How to pass a date parameter into a stored procedure
- sp_dropuser stored procedure vs drop user statement in tsql
- is there a way to find out which statement in TSQL stored procedure crashed?
- Stored procedure - Date
- Error when running SQL stored procedure coversion failed when converting date and/or time from character string
- insert or update the date value as sysdate via stored procedure
- SQL Stored Procedure to get Date and Time
- T-SQL stored procedure date variable
- Stored procedure not returning any data for the date passed
- Checking for invalid date in SQL Server Stored Procedure
- Force a date to the first day of the month inside a stored procedure
More questions with similar tag
- Eliminating duplicate combinations from result
- Get duplicates really is it a bad query or bad database design?
- What is the fastest/easiest way to denormalize this heirarchical table into a flat table?
- SQL Check for null values in variables
- DateAdd with a DateTimeoffset sometimes removes the offset
- Convert Date type to Character type error in SQL Server 2008
- Enable xp_cmdshell does not work
- has anyone faced this error "Error: No valid counters" using type perf?
- How to properly truncate a staging table in an ETL pipeline?
- SQL Server: Show a list of Customers that have more Orders than a specify Customer
- How to convert Rows into Columns based on value
- XML Query Attach appropriate ID
- SQL GROUP BY with a SUM CASE statement
- Displaying URL's from SQL Server
- Allow special characters SQL Server 2008
- SQL Transaction Isolation Level Serializable vs Read Committed in Dev vs Production
- SQL server connection in Visual Studio stop working
- how to use is null with case statements
- How can prevent a SQL Server merge using OUTPUT from updating the target table
- T-SQL: Looping through an array of known values
- Create multiple rows for results in multiple days
- sql server replace special character with null
- Refuses to make ExecuteNonQuery : Incorrect syntax near '('
- How to make this code reusable in SQL Server 2016 (except Union all)
- Beside compatibility with SQL Server 2000 and 2005, is there any other issue to use datetime2
- C# avoid SQL Injection in a function
- How to query multiple conditions for one column in SQL?
- Is It Possible To Generate a SQL Update Script For One Row Of Data?
- How to run NUnit test fixtures serially?
- SQL select where matching record exists and no matching record