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.