SQL SERVER 语法检查方法

最新推荐文章于 2024-06-29 21:36:25 发布

tanhua103292

最新推荐文章于 2024-06-29 21:36:25 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

文章标签： sql server function compilation query object debugging

本文链接：https://blog.youkuaiyun.com/tanhua103292/article/details/4179184

本文介绍了两种在SQL Server中检查Transact-SQL语句语法的方法：使用SET NOEXEC进行编译但不执行，以及使用SET PARSEONLY仅进行解析。SET NOEXEC适用于验证语法和对象名称，而SET PARSEONLY只解析语句。通过示例，展示了如何使用这两个选项来检查有效、无效的对象名和语法错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.SET NOEXEC 方式 : 编译但不执行语句

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

The execution of statements in SQL Server has two phases: compilation and execution. This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing. It is also useful for debugging statements that would generally be part of a larger batch of statements.

The setting of SET NOEXEC is set at execute or run time and not at parse time.

The following example uses NOEXEC with a valid query, a query with an object name that is not valid, and a query with incorrect syntax.

示例：

USE AdventureWorks;
GO
PRINT 'Valid query';
GO
-- SET NOEXEC to ON.
SET NOEXEC ON;
GO
-- Inner join.
SELECT e.EmployeeID, e.Title, v.Name
FROM HumanResources.Employee e
   INNER JOIN Purchasing.PurchaseOrderHeader poh
   ON e.EmployeeID = poh.EmployeeID
   INNER JOIN Purchasing.Vendor v
   ON poh.VendorID = v.VendorID;
GO
-- SET NOEXEC to OFF.
SET NOEXEC OFF;
GO

PRINT 'Invalid object name';
GO
-- SET NOEXEC to ON.
SET NOEXEC ON;
GO
-- Function name used is a reserved keyword.
USE AdventureWorks;
GO
CREATE FUNCTION dbo.Values(@EmployeeID INT)
RETURNS TABLE
AS
RETURN (SELECT PurchaseOrderID, TotalDue
FROM dbo.PurchaseOrderHeader
WHERE EmployeeID = @EmployeeID)
-- SET NOEXEC to OFF.
SET NOEXEC OFF
GO

PRINT 'Invalid syntax';
GO
-- SET NOEXEC to ON.
SET NOEXEC ON;
GO
-- Built-in function incorrectly invoked
SELECT *
FROM fn_helpcollations;
-- Reset SET NOEXEC to OFF.
SET NOEXEC OFF;
GO

2.SET PARSEONLY :解析但不编译或执行语句

When SET PARSEONLY is ON, SQL Server only parses the statement. When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.

The setting of SET PARSEONLY is set at parse time and not at execute or run time.

Do not use PARSEONLY in a stored procedure or a trigger. SET PARSEONLY returns offsets if the OFFSETS option is ON and no errors occur.

示例：

SET PARSEONLY on

CREATE FUNCTION dbo.Values(@EmployeeID INT)
RETURNS TABLE
AS
RETURN (SELECT PurchaseOrderID, TotalDue
FROM dbo.PurchaseOrderHeader
WHERE EmployeeID = @EmployeeID)

SET PARSEONLY off

以上两种方法都经过实际应用检验，其执行效果与通过"查询分析器”进行语法分析效果是一致的。