How can I programmatically check (parse) the validity of a TSQL statement?

Question

I'm trying to make my integration tests more idempotent. One idea was to execute rollback after every test, the other idea was to some how programatically parse the text, similar to the green check box in Query Analyzer or SSMS.

How do I get SQL Server to parse my command without running it using ADO.NET?

UPDATE: This is what finally worked as desired:

using (DbCommand executeOnly = Factory.DbCommand())
{
    executeOnly.Connection = command.Connection;
    executeOnly.CommandType = CommandType.Text;
    executeOnly.CommandText = "SET NOEXEC ON;" + sqlCommand;
    executeOnly.Connection.Open();
    executeOnly.ExecuteNonQuery();
}
//set more properties of command.
command.Execute();

For inexplicable reasons, "SET PARSEONLY ON" only worked in Query Analyzer. I couldn't set this on an ADO.NET connection. It is just as well because PARSEONLY seems to catch only syntax errors, which isn't a common error. SET NOEXEC ON will catch a wider varieties of errors, such as a view that references a missing table or column or a missing parameter in a stored procedure.

Solution 1

I think the command you are looking for is SET NOEXEC ON. If you set this for your connection, the queries will be parsed but will not be executed. Another option would be SET PARSEONLY ON, but I'm honestly not sure what the difference between the two really is.

Solution 2

+1 to Eric's answer. But I've found SET FMTONLY ON to also be useful as SET NOEXEC ON doesn't appear to throw up all errors.

e.g.

SELECT * FROM ATableThatDoesNotExist

Running that with SET NOEXEC ON says it was successful, despite the table not existing in the database. Running it with SET FMTONLY ON instead, will throw the "Invalid object name" error.

SET FMTONLY ON also returns metadata about the resultset that would be returned, which can come in very handy

Solution 3

SQL Server 2012 can parse your syntax, procedures and tables with the following system procedures and functions:

• sp_describe_first_result_set (Transact-SQL)
• sp_describe_undeclared_parameters (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

They are supposedly replacing "SET FMTONLY".

I have tested them and they work a lot better than "SET NOEXEC ON" and "SET PARSEONLY ON"

Examples:

Will not throw an error:

sp_describe_undeclared_parameters
    @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes;'

Will correctly throw an error ("SET NOEXEC" and "SET PARSEONLY" do not throw an error in this case):

sp_describe_undeclared_parameters 
  @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes;SELECT object_id, name, type_desc FROM sys.NOTaTABLE;'

Solution 4

Use the following query

SET PARSEONLY ON
--Your query here
SET PARSEONLY OFF

Solution 5

SET PARSEONLY : Examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

View more solutions

Comments

[KM.]

+1, SET NOEXEC (Transact-SQL)

[Eric Petroelje]

+1 - I agree. Really executing the statement and doing a rollback after is going to be the most reliable way to test.

[KM.]

I get an error when using SET NOEXEC ON and SELECT * FROM ATableThatDoesNotExist

[Martin Smith]

@KM @Ada Apologies I didn't have the vital "GO" between my SET NOEXEC ON and the SELECT

[KM.]

I didn't use a GO, I just ran the SET NOEXEC ON and got the Command(s) completed successfully. message and then ran the query, which then produced the error. If you run the SET NOEXEC ON;SELECT... you get no error. By butting a GO between the SRET and the SELECT it will give the error

[AdaTheDev]

@KM - Aha. For me, on SQL Server 2008 RTM I never get the error. However, when I try on SQL Server 2005 it does behave as you say when you have the GO statement in. Where you trying on 2005? Seems odd the behaviour has changed

[AdaTheDev]

Following on from the comments under my answer, I think there could be a bug with SET NOEXEC ON in SQL 2K8 - has been reproduced by others too. Check out the MS Connect bug I've raised: connect.microsoft.com/SQLServer/feedback/details/569263/…

[OMG Ponies]

+1: SET NOEXEC ON is only checking the query being valid. A query can be valid, but not return correct results.

[deroby]

This is what SSMS is doing when you press the green check box. You can easily see it happen in SQL Profiler.

[HappyTown]

+1 for SET FMTONLY ON also returns metadata about the resultset. This is really useful in troubleshooting without executing the actual SQL statement, specially for a dev in QA environments :)

[Alex]

unlike set statistics time, io on, your example still executes the query: SET PARSEONLY ON; select 'OK'; SET PARSEONLY OFF; do we have to add go before turning it off?

[CervEd]

PARSEONLY only parses, NOEXEC doesn't execute but parses and compiles. Ie, throws errors on missing tables etc.