Parsing T-SQL
-
I'm looking for a way to parse T-SQL queries stored in a table in order to verify their syntax before executing them. The parsing should be done by the SQL engine (no external program). I tried with SHOWPLAN_ALL but can't get it working:
DECLARE @string nvarchar(4000)
SET @string=(SELECT TOP 1 myqueries FROM dbo.mytable)
EXEC ('
SET SHOWPLAN_ALL ON
GO
sp_executesql '+@string+'
GO
SET SHOWPLAN_ALL OFF
GO
')
Has someone an idee?
<hr>
-
SET PARSEONLY ON
-- Execute your query
SET PARSEONLY OFF
* Noel -
It is that! Just one thing: you cannot use SET PARSEONLY ON and SET PARSEONLY OFF in the same statement because the query would be executed and not parsed! So here is the right one:
SET PARSEONLY ON
-- Execute your query
GO
SET PARSEONLY OFF
<hr>
-
The PARSEONLY option checks the SQL syntaxt but not the objets existance.
SELECT * FROM NonExistingTable -> parsing says OK !
How can I verify the querie's objects existance?
<hr>
-
Using a transaction could work butmay be dependant on the SQL being executed. Would
BEGIN TRAN trial
sp_executesql @Sqlstring
ROLLBACK TRAN trial
work? Or is this as dangerous as the little niggling voice in my head says it could be?
-
you've got almost all the pieces...i've done the same thing as well:
To check Syntax:
SET PARSEONLY ON
-- Execute your query
GO
SET PARSEONLY OFF
To Actual Compile and check for objects existance:
SET NOEXEC ON
GO
-- Execute your query
SET NOEXEC OFF
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
you can also try using SET FMTONLY on/off
will execute the query, but not affect any rows or return rows (only metadata)
this would show up errors better than parseonly for missig view/tables
MVDBA
-
note that stored procedures allow for deffered name resolution and you may be missing something although the procedure code didn't complain
* Noel -
I tested every proposed solution. Here's the results:
1. EXEC('SET NOEXEC ON ' + @sqlstring)
2. EXEC('SET PARSEONLY ON ' + @sqlstring)
3. EXEC('SET FMTONLY ON ' + @sqlstring)
-> All 3 returned syntaxical errors => OK
-> Only #3 returned object non existense BUT only for SELECT statement => NOT OK
4. EXEC sp_executesql N'SET NOEXEC ON ' + @sqlstring
5. EXEC sp_executesql N'SET PARSEONLY ON ' + @sqlstring
6. EXEC sp_executesql N'SET FMTONLY ON ' + @sqlstring
-> All 3 returned syntaxical errors => OK
-> Only #3 returned object non existense for all DML statements (SELECT, UPDATE, INSERT, DELETE) => OK
But I want more! What if the user account does not have the permissions to execute the tested query? None of the proposed statements will return a Permition Denied error.
The only way to parse a query syntaxicaly, to verify objects existense and to verify permissions is to use the ROLLBACK TRAN solution:
BEGIN TRAN trial
EXEC sp_executesql @sqlstring
ROLLBACK TRAN trial
<hr>
-
The problem is that our application needs to evaluate strings before they are submitted for processing. and these strings may contain variables, e.g.,
" select *from t where id < @v_id and rdate < getdate()"
All we need to do is to parse the statement (evaluate it) in the context of the column names and table names, where clause, etc. Some databases have packages that allow this evaluation (parse) step. The problem is the syntax checking with variables present.
Doc
-
Denis (4/13/2007)
BEGIN TRAN trialEXEC sp_executesql @sqlstring
ROLLBACK TRAN trial
I would not recommend this. As a previous post noted: this is extremely dangerous. There are lots of things that Rollback cannot undo.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
There are lots of things that Rollback cannot undo.
can you tell me what are all those ?
karthik
-
doc_sewell (7/10/2008)
The problem is that our application needs to evaluate strings before they are submitted for processing. and these strings may contain variables, e.g.," select *from t where id < @v_id and rdate < getdate()"
All we need to do is to parse the statement (evaluate it) in the context of the column names and table names, where clause, etc. Some databases have packages that allow this evaluation (parse) step. The problem is the syntax checking with variables present.
Doc
Sometimes you get tunnel vision where you only think of bending the current solution to the desired result.
Maybe the right thing to do is to give up on the idea of doing it all on the server, and let an application handle the logic instead.
you can create a transaction,(ie DataAdapter.BeginTransaction in .net and try to execute the command in a try...Catch statement, and return an error/success from the application.
The application would require the credentials they are going to use, and an applicaiton 's call for a transaction could be rolled back if they didn't have update permissions on a certain table.
that way you could evaluate the statement sections separately, and skip the whole dynamic sql thing altogether.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
karthikeyan (7/11/2008)
There are lots of things that Rollback cannot undo.
can you tell me what are all those ?
No. I can give you some examples though:
"DROP DATABASE ..."
"Exec xp_CmdShell 'DEL *.*' "
Are two that I can think of.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Thanks rbarryoung.
karthik
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply