如何用T-SQL判断SQL语法是否正确

前几天在论坛看到有个帖子提问是否可以在应用程序中检查SQL Server的语法，当时自己也不清楚。

今天在看SQL Server Set设置，发现有两个选项可以实现这个功能：

1．SET PARSEONLY选项(类似于SSMS的Cntrl+F5)

这个选项检查每个 Transact-SQL 语句的语法并返回任何错误消息，但不编译和执行语句。

下面我们创建一个存储过程用来检查输入的语句是否正确：

create procedure IsValidSQL(@sqlvarchar(max))as

begin

begin try

set @sql='set parseonly on;'+@sql;

exec(@sql);

end try

begin catch

return(1);

end catch;

return(0);

end;-- IsValidSQL

--这条语句语法没有问题，返回值为0

declare @retvalint;

exec@retval=IsValidSQL'select back from t ';

select@retval

--因为from语句没有了，所以语法错误，返回值为１

declare @retvalint;

exec@retval=IsValidSQL'select back f t ';

select@retval

因为SET PARSEONLY只是验证语法不会生产执行计划，可以通过下面的语句验证:

SET PARSEONLY ON

go

select*from[HumanResources].[Department]

go

SET PARSEONLY off

--可以确定执行计划没有生产

SELECT'1'AS RoundNum,usecounts, cacheobjtype,objtype,text

FROM sys.dm_exec_cached_plans

CROSS APPLYsys.dm_exec_sql_text(plan_handle)

WHERE usecounts> 0 AND

text like'%HumanResources%'

AND text NOT LIKE '%Check%'

ORDER BY usecounts DESC;

GO

注意：SET PARSEONLY 的设置是在分析时设置，而不是在执行或运行时设置。

在存储过程或触发器中不要使用 PARSEONLY。另外这个检查虽然可以证明语法没有问题，但是不会检查到对象不存在或者逻辑上的问题。

2. SET NOEXEC : 编译每个查询，但不执行该查询。

当 SET NOEXEC 为 ON 时，SQL Server 将编译每一批处理 Transact-SQL 语句但并不执行它们。当 SET NOEXEC 设置为 OFF 时，所有批处理将在编译后执行。

以下示例在有效查询、包含无效对象名称的查询以及包含不正确语法的查询中使用 NOEXEC。

USEAdventureWorks2012;

GO

PRINT'Valid query';

GO

-- SETNOEXEC to ON.

SET NOEXEC ON;

GO

-- Innerjoin.

SELECTe.BusinessEntityID,e.JobTitle,v.Name

FROM HumanResources.EmployeeAS e

INNER JOINPurchasing.PurchaseOrderHeaderAS poh

ON e.BusinessEntityID=poh.EmployeeID

INNER JOIN Purchasing.VendorAS v

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*

FROMfn_helpcollations;

-- ResetSET NOEXEC to OFF.

SET NOEXEC OFF;

GO

这个选项会编译每个查询，比上面的选项检查的要多。我们可以用Try Catch选项判断，针对错误判断写出来的语句是否有问题。

 

SET NOEXEC

SET PARSEONLY

本文转自 lzf328 51CTO博客，原文链接:

http://blog.51cto.com/lzf328/1256813