-2

I'm trying to use TSql150Parser.Parse() to validate sql queries - unit tests will check queries.

I took as a basis: https://stackoverflow.com/a/33834869/6836124 My current version: https://dotnetfiddle.net/1Anhh9

Same code:

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
                    
public class Program
{
    public static void Main()
    {
        var query = "DECLARE @NEW_VERSION_NUMBER int = 1;"+
                    "DECLARE @NEW_VERSION_NUMBER int = 1;";
        
        IList<string> errors;
        var result = query.IsValidSql(out errors);
        Console.WriteLine(result);
        Console.WriteLine(string.Join("; ", errors.ToArray()));
    }
}

public static class SqlStringExtensions
{
    public static bool IsValidSql(this string query, out IList<string> errors)
    {
        if (string.IsNullOrWhiteSpace(query)) throw new ArgumentNullException("asdasd");

        IList<ParseError> parseErrors;
        using (var reader = new StringReader(query))
        {
            new TSql150Parser(false).Parse(reader, out parseErrors);
        }
        errors = parseErrors.Select(err => err.Message).ToList();

        return !errors.Any();
    }
}

I expected there to be an error: The variable name '@NEW_VERSION_NUMBER' has already been declared But in fact there is no error.

Why is the first error being skipped?

Артём Гродецкий
  • 5
  • 1
  • 5
  • Please put relevant code in the question itself as a [mre], rather than an external link which can break later. And you get no error because the parser validates *syntactical* validity only. That redeclaring a variable is illegal is a semantic property, that requires executing it with an engine. This is not really specific to T-SQL; in most languages the syntax layer would not be responsible for variable scopes. – Jeroen Mostert Sep 23 '20 at 08:43
  • Even if that error was caught, no validator can catch the most important errors caused by bad table and column names or column mismatches because it doesn't have any schema information. It's like trying to validate C# code without having the actual type definitions – Panagiotis Kanavos Sep 23 '20 at 08:49
  • 1
    You'd get more useful test results if you used an actual (probably empty) database, perhaps even a LocalDB instance to test your SQL code. You could even use T-SQL unit tests with [tSQLt](https://tsqlt.org/) – Panagiotis Kanavos Sep 23 '20 at 08:51
  • Adding to what @PanagiotisKanavos said, I've had success using the parser in build pipelines for code analysis rules and containers to execute tSQLt unit tests. – Dan Guzman Sep 23 '20 at 10:00
  • Thanks for tsqlt, but as I understand from https://tsqlt.org/user-guide/ and its subpages tsqlt is not applicable for using from .net framework projects. And I want to validate sql queries as part of build process for pull requests - there is no any DB at this step. So tsqlt is not suitable for me. – Артём Гродецкий Sep 23 '20 at 14:35
  • Thanks, @JeroenMostert! Seems to be it is unable to catch redeclaring a variable error with this parser. I will try to move sql validation (with an engine) to the step after system deployment. – Артём Гродецкий Sep 23 '20 at 14:40
  • It seems that the library only parses SQL, it doesn't tries to run it. In this case, the SQL is perfectly valid (syntactically speaking), even though at runtime we know it'll always blow up. The parse function of SSMS also has some of these pitfalls (for example it don't spot missing tables), but not this particular one, thgouh. – Alejandro Sep 23 '20 at 14:50

1 Answers1

0

I will answer my own question to close it with a copy of my last comment:

Thanks, @JeroenMostert! Seems to be it is unable to catch redeclaring a variable error with this parser. I will try to move sql validation (with an engine) to the step after system deployment.

Артём Гродецкий
  • 5
  • 1
  • 5