How do I verify a valid sql statement programatically ??

  • Thread starter Thread starter C Newby
  • Start date Start date
C

C Newby

Is there an object somewhere that i can use to programatically verify the
syntactic correctness of a given T-SQL statement? As of now, I am submitting
the query and catch an exception which requires a trip to the DB. This might
seem ok...after all, why would i bother if i wasn't going to run the query
in the first place. But my case is different. So far as i can tell anyway,
and having to hit the DB is not required for my business flow.

Any ideas?
 
Hi C Newby:
C Newby said:
Is there an object somewhere that i can use to programatically verify the
syntactic correctness of a given T-SQL statement? As of now, I am submitting
the query and catch an exception which requires a trip to the DB. This might
seem ok...after all, why would i bother if i wasn't going to run the query
in the first place. But my case is different. So far as i can tell anyway,
and having to hit the DB is not required for my business flow.
Click to expand...

If you use Query Analyzer and validate the syntax, it will tell you if the
syntax conforms or not, but it doesn't guarantee that it's 'valid'. If I
typed this in QA:

SELECT * from Blah where FirstName = 'Blah' it would parse.
However, since those tables don't exist, it's not valid In order for me to
confirm that it's valid, I have to fire it against the DB. AFAIK there's
nothign in SQLDMO that will do this for you b/c you'd have to at least
connect to the db to know the field names and table names to know if they
are real or not... so short of loading all of these into some structure and
writing a pretty poweful parser, I don't think you can do it.

Hopefully I'm wrong but I really don't think there's a programmatic way to
do this without a WHOLE lot of work.
 
If you're willing to take a round-trip to the server it's not too bad. Just
execute:

SET PARSEONLY ON

before your statement, and then execute the statement on the same
connection. While this is on, sql server will only check the syntax of
statements on that connection, and not compile or execute them.

- Dave
 
Hi Dave:

While his question mentioned not wanting to take the extra trip, I've
wondered about this and in every instance I've wanted it, it's worth the
extra trip. I can of course try it myself (which I plan on doing shortly
but I figured I'd ask while I have you here) but was wondering what is
returned with if a problem is found. When I run through
sp_helptext(proc_name) for instance, I get the whole text of the proc with
the formatting characters... what I 'm wondering is if I fire a multiline
text query using ParseOnly, and I have a syntax error on a line with exactly
carriage returns before it, will it indicate Syntax error on line 4 like
Query Analyzer will?

The reason I ask is that I'm building a tool which is roughly like QA on
steroids and parsing the text is the only problem I don't have built. I am
going to allow search and replace funtionality for field names for instance
and I'm going to put in find and replace. So the problem I have
(particularly b/c I'm allowing regex replace functionality) is that I'll
output say 60 lines of procedures in window1 and then in window2 I'll have
the line where whatever appears, like Visual Studio does. I even have the
dockable windows. But with a replace, they may break somethign if they
aren't careful... misnaming an object for instance. So I want tobe able to
highlight the line and give them an error line number. If it will return an
error message like Syntax error.... on line 4 and line 4 corresponds to the
same line that it would if I opened it in a new window in QA, I'm in great
shape. If I can do this, I'm thinking of making it a VS.NET add-in so the
find/replace would be seamless and people could search hundreds to procs for
field or variable names (for instance, if I wanted to change the name of a
field and wanted to see how many procedures referenced the field and then
wnated to do a mass replace) and if those numbers correspond, you just
Solved my biggest problem.

I'm going to get cracking on it now, but if you get this in the near future
, I'd appreciate your feedback or suggestions you may have.

Thanks again!!!!!!!!!!!!!!!!! You rock!
 
Back
Top