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