SQL Server 2008: How to load/parse a SQL script from within TSQL?

Question

Been working with SQL Server since it was Sybase (early 90s for the greenies) and I'm a bit stumped on this one.

In Oracle and DB2, you can pass a SQL batch or script to a stored procedure to test if it can be parsed, then execute conditional logic based on the result, like this pseudocode example:

if (TrySQLParse(LoadSQLFile(filename)) == 1
   { execute logic if parse succeeds }
else
   { execute logic if parse fails }

I'm looking for a system proc or similar function in SQL Server 2008 -- not SHOWPLAN or the like -- to parse a large set of scripts from within a TSQL procedure, then conditionally control exception handling and script execution based on the results. But, I can't seem to find a similar straightforward gizmo in TSQL.

Any ideas?

Answer 1

You could call an exec(), passing in the script as a string and wrap it in a Try/Catch

Answer 2

There isn't a mechanism in SQL Server to do this. You might be able to do it with a CLR component and SMO, but it seems like a lot of work for questionable gain.

How about wrapping the script in a try/catch block, and executing the "if fails" code in the catch block?

Answer 3

Potentially very dangerous. Google up "SQL injection" and see for yourslef.

Answer 4

The general hacky way to do this in any technology that does a full parse/compile before execution is to prepend the code in question with something that causes execution to stop. For example, to check if a vbscript passes syntax checking without actually running it, I prepend:

Wscript.exit(1)

This way I see a syntax error if there are any, or if there are none then the first action is to exit the script and ignore the rest of the code.

I think the analog in the sql world is to raise a high severity error. If you use severity 20+ it kills the connection, so if there are multiple batches in the script they are all skipped. I can't confirm that there is 100.00000% no way some kind of sql injection could make it past this prepended error, but I can't see any way that there could be. An example is to stick this at the front of the code block in question:

raiserror ('syntax checking, disregard error', 20, 1) with log

So this errors out from syntax error:

raiserror ('syntax checking, disregard error', 20, 1) with log
create table t1()
go
create table t2()
go

While this errors out from the runtime error (and t1/t2 are not created)

raiserror ('syntax checking, disregard error', 20, 1) with log
create table t1(i int)
go
create table t2( i int)
go

And to round out your options, you could reference the assembly C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.SqlParser.dll in a clr utility (outside of the db) and do like:

SqlScript script = Parser.Parse(@"create proc sp1 as select 'abc' as abc1");