I saw a post in the Forum a few days ago asking if I could check the SQL Server syntax in the application. I was not clear at the time. Today, I checked SQL Server set settings and found that there are two options to implement this function:
1. Set parseonly option (similar to cntrl + F5 of SSMs)
This option checks the syntax of each Transact-SQL statement and returns any error message without compiling or executing the statement.
Next we create a stored procedure to check whether the input statement is correct:
Create
Procedure isvalidsql (@ sqlvarchar (max)
Begin
Begin
Try
Set
@ SQL = 'set parseonly on; '+ @ SQL;
Exec (@ SQL );
End
Try
Begin
Catch
Return (1 );
End
Catch;
Return (0 );
End; -- isvalidsql
-- The syntax of this statement is correct. The returned value is 0.
Declare
@ Retval int;
Exec @ retval = isvalidsql 'select back from t ';
Select @ retval
-- Because the from statement does not exist, a syntax error occurs. The returned value is 1.
Declare
@ Retval int;
Exec @ retval = isvalidsql 'select back f T ';
Select @ retval
Because set parseonly only verifies the syntax and does not produce the execution plan, you can verify it using the following statement:
Set parseonly
On
Go
Select * from [HumanResources]. [Department]
Go
Set parseonly
Off
-- It can be determined that the execution plan has no production
Select '1'
As roundnum, usecounts, cacheobjtype, objtype, text
From SYS. dm_exec_cached_plans
Cross applysys. dm_exec_ SQL _text (plan_handle)
Where usecounts> 0and
Text like '% HumanResources %'
And textnotlike '% check %'
Order byusecountsdesc;
Go
Note: Set parseonly
Is set during analysis, rather than during execution or running.
Do not use parseonly in stored procedures or triggers. In addition, this check can prove that the syntax is correct, but does not check whether the object does not exist or the logic is faulty.
2. Set noexec: compile each query, but do not execute this query.
When set noexec is on, SQL Server compiles and processes each batch of transact-SQL statements but does not execute them. When
When set noexec is set to off, all batch processing will be executed after compilation.
The following example uses noexec for a valid query, a query containing invalid object names, and a query containing incorrect syntax.
Use adventureworks2012;
Go
Print 'valid query ';
Go
-- Setnoexec to on.
Set noexec
On;
Go
-- Innerjoin.
Select E. businessentityid, E. jobtitle, V. Name
From HumanResources. employeease
Inner
Join purchasing. purchaseorderheaderaspoh
On E. businessentityid = Poh. employeeid
Inner
Join purchasing. vendorasv
On Poh. vendorid = V. businessentityid;
Go
-- Setnoexec to off.
Set noexec
Off;
Go
Print 'invalid object name ';
Go
-- Setnoexec to on.
Set noexec
On;
Go
-- Function name uses is a reserved keyword.
Use adventureworks2012;
Go
Create Function
DBO. Values (@ businessentityidint)
Returns
Table
As
Return (selectpurchaseorderid, totaldue
From
DBO. purchaseorderheader
Where
Vendorid = @ businessentityid );
-- Setnoexec to off.
Set noexec
Off;
Go
Print 'invalid syntax ';
Go
-- Setnoexec to on.
Set noexec
On;
Go
-- Built-in function incorrectly invoked.
Select *
From fn_helpcollations;
-- Resetset noexec to off.
Set noexec
Off;
Go
This option will compile each query, which is more than the preceding option check. We can use the try catch option to determine whether the written statements are faulty due to errors.
Set noexec
Set parseonly