1

Question: Is there any way to detect if an SQL statement is syntactically correct?

Explanation:

I have a very complex application, which, at some point, need very specific (and different) processing for different cases.

The solution was to have a table where there is a record for each condition, and an SQL command that is to be executed.

That table is not accessible to normal users, only to system admins who define those cases when a new special case occurs. So far, a new record was added directly to the table. However, from time to time there was typos, and the SQL was malformed, causing issues.

What I want to accomplish is to create a UI for managing that module, where to let admins to type the SQL command, and validate it before save.

My idea was to simply run the statement in a throw block and then capture the result (exception, if any), but I'm wondering of there is a more unobtrusive approach.

Any suggestion on this validation?

Thanks

PS. I'm aware of risk of SQL injection here, but it's not the case - the persons who have access to this are strictly controlled, and they are DBA or developers - so the risk of SQL injection here is the same as the risk to having access to Enterprise Manager

Andriy M
  • 76,112
  • 17
  • 94
  • 154
bzamfir
  • 4,698
  • 10
  • 54
  • 89
  • 1
    Some Suggestion here: http://stackoverflow.com/questions/1599547/how-to-validate-sql-query-syntax – Meysam Tolouee May 30 '14 at 15:47
  • How come devs senior enough to be doing this aren't senior enough to unit test their queries before putting them into the table? – HLGEM May 30 '14 at 17:36

2 Answers2

4

You can use SET PARSEONLY ON at the top of the query. Keep in mind that this will only check if the query is syntactically correct, and will not catch things like misspelled tables, insufficient permissions, etc.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
0

Looking at the page here, you can modify the stored procedure to take a parameter:

CREATE PROC TestValid @stmt NVARCHAR(MAX)
AS 
BEGIN 

IF EXISTS ( 
SELECT 1 FROM sys.dm_exec_describe_first_result_set(@stmt, NULL, 0) 
WHERE error_message IS NOT NULL 
AND error_number IS NOT NULL 
AND error_severity IS NOT NULL 
AND error_state IS NOT NULL 
AND error_type IS NOT NULL 
AND error_type_desc IS NOT NULL ) 

BEGIN 

SELECT error_message  
FROM sys.dm_exec_describe_first_result_set(@stmt, NULL, 0) 
WHERE column_ordinal = 0 

END 
END
GO

This will return an error if one exists and nothing otherwise.

wlchastain
  • 373
  • 6
  • 14