Saturday, March 30, 2013

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.


image


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

SELECT
1


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

No comments:

Post a Comment