How can I parse dynamic SQL before execution in T-SQL?

Question

I'd need to know how to parse a query to know if it is well build before executing it. If the parsing is correct then execute it, if not discard it.

I haven't been able to accomplish this using SET NOEXEC, SET PARSEONLY, TRY/CATCH.

I m using dynamic sql inside a loop. Sometimes the dynamic sql is incorrect not for my fault but for the information existing in the database. For this reason I would like to parse it before execution.

Granlund

In C# code? Inside SQL Server Management Studio?? Not clear..... – Arsphenamine

Answer 1

This might be possible with some sp_executesql trickery:

-- The query you would like to parse
declare @sql nvarchar(max)
set @sql = 'select 1'

declare @testsql nvarchar(max)
declare @result int
set @testsql = N'set parseonly on; ' + @sql
exec @result = sp_executesql @testsql

-- If it worked, execute it
if @result = 0
    begin
    exec sp_executesql @sql
    end

If I use an incorrect query, like 'salact 1', the @result value is nonzero.

The TRY/CATCH does not seem to play well with sp_executesql, so I'm checking the return value instead.

Hearn

Answer 2

Try using SET FMTONLY ON and SET FMTONLY OFF.

SET PARSEONLY ON your code SET PARSEONLY OFF should work for most though.

MSDN FMTONLY

Ambiguity

Note in documentation: "SET FMTONLY ON has no effect when the Transact-SQL batch is parsed. The effect occurs during execution run time." – Bedrock

Answer 3

What are you trying to accomplish?

As stated before, T-SQL won't compile unless the query is parseable.

If you just want to have a way to verify that the query is fine (ex. to verify that you didn't forget the where statement or so) then maybe the showplan will help you

set showplan_xml on

this will tell the sql server to just parse the query (the query itself is never executed) and return the execution plan for it. It is mostly used for performance issues, but can also be used as a pointer in case something is really incorrect with the query.

Kight

wonderful solution! i alwyas used set ftmonly on, but it is a deprecated feature in Sql Server 2012 :( – Questa