验证 T-SQL 查询的方法? [英] Ways to validate T-SQL queries?

问题描述

我可以访问 Access 数据库，并且在该数据库中包含填充了 TSQL 查询的字段.这些查询由服务器上的 T-SQL 处理.因此，当我编写这些 SQL 查询并将它们放入一个字段以供终端服务器使用时，我无法验证语法/等.我可以在该 Access 数据库中创建一个临时查询，但它不是相同的查询语言.例如，Access 会正确使用 IIF，但 TSQL 不会(而是使用 CASE).

I have access to an Access database and within that database are fields filled with TSQL queries. These queries are processed by T-SQL on a server. So when I write these SQL queries and put them into a field for use by the end server, I'm unable to validate the syntax/etc. I could create a temporary query in that Access database, but it's not the same query language. For example, Access would correctly use IIF but TSQL would not (it would instead be CASE).

我无法通过 TSQL 直接访问此服务器，有什么方法可以验证我的 T-SQL 查询(语法等)?也许是在线网络工具?

I don't have direct access to this server with TSQL, is there a way I can validate my T-SQL queries (for syntax and the like)? Perhaps a web tool online?

我应该注意到我没有访问 SQL 服务器的权限.只有 Access db 和那个.我知道它不会验证表名等，我不希望它会.

I should note I do not have access to the SQL server. Only the Access db and that alone. I understand it will not validate table names and the like, I wouldn't expect it to.

推荐答案

实际上，MattMc3 的答案和 FremenFreedom 的答案的组合应该有效.

Actually, a combination of MattMc3's answer and FremenFreedom's answer should work.

下载 SQL Express.

Download SQL Express.

然后，声明以下存储过程:

Then, declare the following stored procedure:

create procedure IsValidSQL (@sql varchar(max)) as
begin
    begin try
        set @sql = 'set parseonly on;'+@sql;
        exec(@sql);
    end try
    begin catch
        return(1);
    end catch;
    return(0);
end; -- IsValidSQL

您可以通过以下方式进行测试:

You can test it with:

declare @retval int;
exec @retval = IsValidSQL 'select iif(val, 0, 1) from t';
select @retval

或与:

declare @retval int;
exec @retval = IsValidSQL 'select val from t';
select @retval

注意:这将捕获 IIF() 问题.它不会捕获与表结构或列结构相关的任何内容.您需要为此使用架构和稍微不同的方法(select top 0 * from () t")可以做到.

Note: this will catch the IIF() issue. It will not catch anything related to the table structures or column structures. You would need the schema for that and a slightly different approach ("select top 0 * from () t") woudl do it.

您也许可以在线使用 SQL Fiddle 做一些事情.但是，我建议您拥有数据库的本地副本.

You might be able to do something with SQL Fiddle online. However, I would suggest having a local copy of the database.

这篇关于验证 T-SQL 查询的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！