我如何以编程方式检查（解析）一个TSQL语句的有效性？ [英] How can I programmatically check (parse) the validity of a TSQL statement?

问题描述

我试图让我的集成测试更幂等。一个想法是每次测试后执行回滚，另一个想法是一些如何编程方式分析文本，类似于在查询分析器或SSMS绿色复选框。

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.

我如何获得SQL Server来解析我的命令，而无需使用ADO.NET运行呢？

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;";
    executeOnly.Connection.Open();
    executeOnly.ExecuteNonQuery();
}
//set more properties of command.
command.Execute();

有关莫名其妙的原因，设置PARSEONLY ON 只曾在查询分析器。我不能设置此对ADO.NET连接。这是一样好，因为 PARSEONLY 似乎只捕获语法错误，这不是一个常见的​​错误。 SET NOEXEC ON 将捕获更广泛的品种错误，比如引用一个缺少的表或者列或缺少参数的存储过程中的景色。

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.

推荐答案

我想你要找的命令的 SET NOEXEC ON 。如果设置此为您的连接，查询将被解析但不会被执行。另一种选择是 SET PARSEONLY ON ，但我真的不知道这两者之间的区别到底是什么。

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.

这篇关于我如何以编程方式检查（解析）一个TSQL语句的有效性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！