Ways to validate T-SQL queries?

Question

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.

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

Answer 2

You can use the NOEXEC option:

SET NOEXEC ON

SELECT 1 AS Test

SET NOEXEC OFF