How to Check the Syntax of Dynamic SQL Before Execution

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 extra comma before FROM clause .

To execute Stored procedure :

EXEC TestProc 
GO


After removing 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 Stored procedure :

EXEC TestProc 
GO


See also



Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Richard Mueller edited Revision 3. Comment: Fixed the tag "SQL Server 2012"

  • Naomi  N edited Original. Comment: Nice article!

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: Nice article!

  • Dear SathyanarrayananS,

    First of all, thanks for your efforts and thanks to Naomi that is everywhere and correct everything. I love this function because I needed it since 2008 in our ERP application. But the purpose of this function is to identify the headers of output result set of the procedures. It's so helpful in MVVM architecture particularly in dynamic applications such ERPs that we need to know what   are the output columns names and data types from one procedure that created out of the c# source code.

    I expected by the title, that this is such PARSE function. But its not.

    By the way, what is the role of TRY/CATCH block? In TRY/CATCH block we execute once and get syntax error. But in this approach we execute it twice, if there is no errors. This add extra performance problems in large dynamic procedures particularly in OLTP databases with many transactions and users.

    Regards,

    Saeid

  • Richard Mueller edited Revision 3. Comment: Fixed the tag "SQL Server 2012"

Page 1 of 1 (3 items)