Ways to validate T-SQL queries?

问题:

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).

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?

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.

回答1:

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

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

or with:

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

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.

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

回答2:

You can parse your T-SQL to check for valid syntax by executing it on the SQL Server machine with a SET PARSEONLY ON as the first line of your script. It will not validate table or field names, but will provide you with any syntax errors.

回答3:

The Data Dude (Gert Drapers) describes how to use the built-in SQL Server T-SQL parser in your application here:

• Getting to the Crown Jewels

If you want to only check the validity of the SQL statements that you have - this might be a nice way to go, and it doesn't require SQL Server per se to be installed where you run your unit tests.

It's a .NET based approach, and it cannot - of course - validate object names in your database if you're not using a live database - but it can catch syntactical errors in your T-SQL statements.

回答4:

You can use the NOEXEC option:

SET NOEXEC ON

SELECT 1 AS Test

SET NOEXEC OFF

回答5:

Is SQL Server Management Studio Express (free download) able to connect to regular SQL Server instances? If so, perhaps you could test the queries there. Even if you could not connect to the actual server, you might be able to create a test version of your database in Express that would at least allow you to catch syntax and naming problems.

回答6:

If they are fairly static, convert them into stored procedures in the Sql Database and then just call them from access.