Thursday, 1 July 2010

Validating an SQL query programmatically, with gotcha

If you want to check the validity of a TSQL statement programmatically, you can make use of the SET NOEXEC statement. As this MSDN reference states, SET NOEXEC ON will compile the query but won't actually execute it. This is ideal (well, nearly....) if perhaps you have a dynamically generated statement that you want to check is valid before executing it.

Example time
SET NOEXEC ON 
GO
SELECT TOP * FROM sys.tables
GO
SET NOEXEC OFF
GO
This results in the following error, as I've failed to specify the number of rows to return for the TOP:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
So if we correct the statement, then try again:
SET NOEXEC ON
GO
SELECT TOP 10 * FROM sys.tables
GO
SET NOEXEC OFF
GO
This time, as we'd expect, we get:
Command(s) completed successfully.
Gotcha
However, it appears that in SQL Server 2008, SET NOEXEC does not work exactly as documented. The documentation states it will validate the syntax and object names. As shown above, it did validate the syntax. But I found it didn't actually error when invalid objects are referenced.
e.g.
SET NOEXEC ON
GO
SELECT * FROM NonExistentTable
GO
SET NOEXEC OFF
GO
This comes back as successful, despite the table not existing.
SQL Server 2005 DOES behave as documented, and throws error 208 - "Invalid object name".
I've raised a connect bug report - if you can reproduce it in SQL Server 2008, please add your input to that bug report. If anyone is using SQL Server 2008 R2, I'd be interested to know the behaviour in that - drop me an email or add as a comment below.

Another statement worth looking at, is SET FMTONLY which as per MSDN, returns metadata from the query without actually running the query. This does throw error 208 when referencing an invalid table name. If the query is valid, it will return the metadata of the resultset which is handy if you want to test the response format without executing the statement.


8 comments:

  1. Yes it happens on 2008 R2. Validated, voted, and added that comment to the Connect item.

    ReplyDelete
  2. @Aaron @John Thanks for the feedback

    ReplyDelete
  3. The really weird thing is that you can STOP the Error 208 from coming up in SQL2005 if you put a valid statement in the batch BEFORE the statement with the invalid object:

    SET NOEXEC ON
    GO
    PRINT 'What the heck?'
    SELECT * FROM NonexistentTable
    GO
    SET NOEXEC OFF
    GO

    The above returns 'Command(s) completed successfully.'

    So did this really work in SQL2005? Only sometimes.

    --Brad

    ReplyDelete
  4. @Brad - Interesting, I didn't realise that. Thanks for the follow up!

    ReplyDelete
  5. Hi,
    I use SQL Server 2008 R2 and I have the same behaviour:
    SET NOEXEC ON
    SELECT * FROM NonExistingFile
    SET NOEXEC OFF

    returns "Command(s) completed successfully"

    ReplyDelete
  6. can you tell how to validate sql syntax using java??

    ReplyDelete
  7. @Hanu - I'm afraid I don't know java, but you could use the SQL I've given in this post and execute against a db connection from java

    ReplyDelete