August 18, 2009 at 5:57 am
just a small question -
is there any way that you can test to see if a SQL statement would execute correctly, but not actually run it?
the reason i'm asking is because i'm writing an application that involves running SQL queries against a database in order to generate result sets. I'd like to be able to allow the user to save queries in order to re-use them.
My plan for saving the queries was to store the query code in a database table, so that it can be re-executed in the future.
now, for my current situation, it's not too necessary, since the only things the user should be doing is running select statements anyway, and executing a select statement and then just setting a field to "valid = true" if the statement executes successfully should be sufficient for now, but thinking ahead in the future, there may at some point come a time where more complex statements could be necessary for the program, involving inserts, updates, or deletes.
i know i could nest the statement in a transaction, and if there's any errors, roll back the transaction, but that results in identity fields still incrementing, which i'd rather avoid.
basically i just want to be able to know that the statement will correctly execute without any errors.
August 18, 2009 at 7:26 am
What's correct? You can parse a statement to see if it's syntactically valid, but that doesn't mean it will execute as you want it to execute. The only way to do that is either have a simple SELECT (or insert/update/delete) that you can see will wokr, or you need to execute the statements to be sure they work as you intend.
August 18, 2009 at 7:29 am
for these purposes it's sufficient for me to just know that the statement is syntactically valid, IE it will execute. whether it produces the result that the user intended is not necessary to test.
i know if i was using SSMS, i could easily just use the parse check, but is that something which can be invoked through a statement?
August 18, 2009 at 7:37 am
August 18, 2009 at 7:46 am
Hm.
A few things about that -
first, it says its not usable within a stored proc. this is unfortunate, since i'd really like to be able to have the stored proc test it before executing the statements.
also, it doesn't seem to catch all errors. like, for example, if i say:
select * from tablea
and tablea doesn't exist, it will still report that it was executed successfully.
August 18, 2009 at 7:58 am
That's a parse, not a compile. A parse isn't a check against the existence of a table.
August 18, 2009 at 8:36 am
You could start with parsing, and then you'd have to build something yourself to test for validity.
It could be as simple as taking the SQL statement and running it as if it were dynamic SQL, and then making sure it gets rolled back at the end. You could wrap it in a Try Catch structure to test it that way. If it passes the test, it's valid. If it fails, you'll have an error message you can use to communicate what the problem is.
However, even if a statement can run and even get the desired result, that doesn't mean it will be valid forever. Test it today and it passes. Someone adds a column to a table, and tomorrow the query could fail. But it'll be stored in your table of queries with a flag indicating that it's valid. That's probably not a good idea.
To accommodate situations like that, you'll need to have a version ID for the database, or at least a datetime value. If you do DDL logging, you would be able to set up your system so that if there are any DDL changes since the query was validated, you would be able to indicate that it needs to go through another validation test.
There are probably more ramifications to a solution like this, which you'll find in design/build/maintenance, but I'd definitely at least look at these things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2009 at 11:54 am
SET NOEXEC ON
That will compile but not execute.
1. SET NOEXEC ON
2. Execute the code
3. SET NOEXEC OFF
...and react to any errors.
-Eddie
Eddie Wuerch
MCM: SQL
August 18, 2009 at 12:46 pm
I had completely forgotten about NOEXEC.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply