October 28, 2013 at 3:18 pm
I have inherited a process that allows users to select dates or text and then verify their syntax. Currently, the process creates the from and where clause, then adds Select 1 in front of it (not even Top 1 *, but just Select 1). They originally thought that that would be fast, now they realize by doing this, the query is running against the server and taking a long time. So here's my issue. I tried Set NoExec and FmtOnly but they don't do what I need them to do. I also tried adding where 1=2 to the end of the syntax, but it returns successful even if the date format is incorrect. The DateField is a datetime field, but the month is incorrect. My results are below, but basically I need it to return an error without running the query
--Returns successful even if table doesn't exist
et NoExec on
select * from tableA where DateField = '31/31/2013'
Set NoExec off
-- At least returns an error that table doesn't exist, but doesn't return error for invalid date format
Set FmtOnly on
select * from tableA where DateField = '31/31/2013'
Set FmtOnly off
-- At least returns an error that table doesn't exist, but doesn't return error for invalid date format
select * from tableA where DateField = '31/31/2013' and 1 = 2
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 28, 2013 at 4:09 pm
I'm not sure if it's of help, but SQL Server won't throw an error for DateField = '31/31/2013' if the table doesn't exists because it will treat it as a string instead of a date.
If table becomes correct, then the error will come out.
October 28, 2013 at 4:21 pm
Why don't you just simply add the top 1? It seems like a good idea.
SQL Server will ignore other conditions if 1=2 is present, since it will know that the WHERE clause will return false and won't need to go through all rows with the other conditions.
Maybe the ISDATE() function will help you if you just want to validate date format or running a SELECT to that value converted to a date data type. What happens if a user writes '1/2/2013'? Will they expect January 2nd or February 1st? How would they know for sure?
EDIT: Have you tried SET PARSEONLY { ON | OFF }?
October 28, 2013 at 5:50 pm
It's not just a date field, they are allowed to add other criteria. It's just that the date was an easy example to show how they syntax would be validated. The dates are using the localized settings so that's not a problem. I can check the parse only command in the morning
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply