如何以编程方式检查(解析)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;" + sqlCommand;
    executeOnly.Connection.Open();
    executeOnly.ExecuteNonQuery();
}
//set more properties of command.
command.Execute();

由于莫名其妙的原因，SET 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屋！