SET PARSEONLY

This is the sixth post of SET Statement series.

When this set to on, SQL Server engine examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement. By default this is OFF,

USE AdventureWorks2012
SET PARSEONLY ON

SELECT SOH.SalesOrderID,SOH.OrderDate, P.ProductID, 
P.Name,SOD.OrderQty,SOD.LineTotal FROM Sales.SalesOrderDetail SOD          
INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID = SOH.SalesOrderID         INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID         WHERE SOH.OrderDate = '2005-07-01'       
  AND P.ProductId = 712

 

When the above query is executed, no results will be displayed.

There will be obvious question where it can be used.

Let' us try to set this in a stored procedure.

CREATE PROC InsertProc
AS

SET PARSEONLY ON

SELECT1

This will fail with following error.

Msg 1059, Level 15, State 1, Procedure InsertProc, Line 0
Cannot set or reset the 'parseonly' option within a procedure or function.

You cannot use the ‘PARSEONLY’ option in a procedure or function. Though this error refers to procedure and function, same error will generate if you try to set this option a trigger.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN