How to Use T-SQL to determine whether SQL syntax is correct

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