Share via

How to Check the Syntax of Dynamic SQL Before Execution

This article is about the system function sys.dm_exec_describe_first_result_set  that can be used to check the syntax of dynamic SQL before execution. This system function, sys.dm_exec_describe_first_result_set, was introduced in SQL Server 2012.

Create sample table and insert sample data:

CREATE Table  Test (Id INT  NOT NULL  Primary Key,Name VARCHAR(100))  
INSERT Test SELECT 1 , 'Sathya' 
GO

Create sample stored procedure:

CREATE  PROC TestProc  
AS 
BEGIN 
   
DECLARE @SQL NVARCHAR(MAX) = 'SELECT *, FROM Test' 
   
IF EXISTS (  
SELECT 1 FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0)  
WHERE error_message IS NOT NULL 
AND error_number IS NOT NULL 
AND error_severity IS NOT NULL 
AND error_state IS NOT NULL 
AND error_type IS NOT NULL 
AND error_type_desc IS NOT NULL  )  
   
BEGIN 
   
SELECT error_message   
FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0)  
WHERE column_ordinal = 0  
   
END 
   
ELSE  
   
BEGIN 
   
EXEC (@SQL)  
   
END 
   
END 
GO

If you examine the dynamic SQL in the above stored procedure, you will notice incorrect syntax in that query with an extra comma before FROM clause.

To execute the stored procedure:

EXEC TestProc  
GO

After removing the comma before the FROM clause in the @SQL variable, alter the stored procedure.

ALTER  PROC TestProc  
AS 
BEGIN 
   
DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Test' 
   
IF EXISTS(  
SELECT 1 FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0)  
WHERE error_message IS NOT NULL 
AND error_number IS NOT NULL 
AND error_severity IS NOT NULL 
AND error_state IS NOT NULL 
AND error_type IS NOT NULL 
AND error_type_desc IS NOT NULL  )  
   
BEGIN 
   
SELECT error_message   
FROM sys.dm_exec_describe_first_result_set(@SQL, NULL, 0)  
WHERE column_ordinal = 0  
   
END 
   
ELSE  
   
BEGIN 
   
EXEC (@SQL)  
   
END 
   
END

To execute the stored procedure:

EXEC TestProc  
GO

See Also