Code to validate SQL Scripts

Question

How can I validate sql scripts before executing them using .net 2.0 and c#?

If the sql is not valid I want to return error rows.

Answer 1

If you are creating a tool that allows the user enter some sql code by hand and you want to validate the code entered using C# code before execution on sql server, you can create a method like this:

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

public class SqlParser
{
        public List<string> Parse(string sql)
        {
            TSql100Parser parser = new TSql100Parser(false);
            IScriptFragment fragment;
            IList<ParseError> errors;
            fragment = parser.Parse(new StringReader(sql), out errors);
            if (errors != null && errors.Count > 0)
            {
                List<string> errorList = new List<string>();
                foreach (var error in errors)
                {
                    errorList.Add(error.Message);
                }
                return errorList;
            }
            return null;
        }
}

As of 2018 and new database versions, this might be newer version:

using Microsoft.SqlServer.TransactSql.ScriptDom;

(download with npm: PM> Install-Package Microsoft.SqlServer.TransactSql.ScriptDom -Version 14.0.3811.1 )

public bool IsSQLQueryValid(string sql, out List<string> errors)
{
    errors = new List<string>();
    TSql140Parser parser = new TSql140Parser(false);
    TSqlFragment fragment;
    IList<ParseError> parseErrors;

    using (TextReader reader = new StringReader(sql))
    {
        fragment = parser.Parse(reader, out parseErrors);
        if (parseErrors != null && parseErrors.Count > 0)
        {
            errors = parseErrors.Select(e => e.Message).ToList();
            return false;
        }
    }
    return true;
}

Answer 2

SSMS has a way of doing this.

If you use the SQL Profiler you will see that it executes SET PARSEONLY ON, then the SQL and then SET PARSEONLY OFF and any errors are risen without compiling or executing the query.

SET PARSEONLY ON;
SELECT * FROM Table; --Query To Parse
SET PARSEONLY OFF; 

PARSEONLY

I have never tried this from c# but I see no reason why it should not work, it works from SSMS after all.

As Martin Smith points out in the comments you can use SET NOEXEC ON

MSDN says the following about both commands.

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

When SET PARSEONLY is ON, SQL Server only parses the statement. When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.

That indicates that NOEXEC will also compile the query where PARSEONLY will not. So NOEXEC may catch errors that PARSEONLY does not. The usage is the same.

SET NOEXEC ON;
SELECT * FROM Table; --Query To Parse
SET NOEXEC OFF; 

NOEXEC