SQL Server function to check dynamic SQL syntax

Problem

Although most of what you read tells you to stay away from dynamic SQL, the reality is there are places where writing T-SQL code dynamically just makes sense.  The one downside to dynamic SQL is that if it is not written correctly the code will fail. So in this tip I am sharing a function I created to help determine if the dynamic code is OK prior to execution.

Solution

The solution involves creating a scalar user defined function that gets an input SQL string to check and then outputs an error message if the SQL string check returns an error or returns OK if the SQL string’s check passed successfully.

The function queries the sys.dm_exec_describe_first_result_set dynamic management view. This dynamic view (or function) takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

If an error exists then the error message, number, severity, state, type and type description will return NOT NULL values. In this case the function will return the error message text as its returned values.  If there are no errors, then the function will return OK as its returned value.

Function to Check Dynamic SQL Code

Create the following function and then we will show example use below.

 -- =============================================
-- Author:  Eli Leiba
-- Create date: 06-2017
-- Description: Check Dynamic SQL Statement Syntax
-- =============================================
CREATE FUNCTION dbo.CheckDynaSQL (@p1 VARCHAR (2000))
RETURNS VARCHAR (1000)
AS
BEGIN
   DECLARE @Result VARCHAR (1000)
   IF EXISTS (
         SELECT 1
         FROM sys.dm_exec_describe_first_result_set (@p1, 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 @Result = [error_message]
      FROM sys.dm_exec_describe_first_result_set(@p1, NULL, 0)
      WHERE column_ordinal = 0
   END
   ELSE
   BEGIN
      SET @Result = 'OK'
   END
   RETURN (@Result)
END
GO

Examples Using Function

All the examples use the Northwind database.  The examples cover various queries like SELECT, UPDATE, INSERT, DELETE, stored procedures and functions.

Note: For these tests it is assumed that in the Northwind database, the Orders table exists and the NewOrders table does not exist.

Checking SELECT statements

 Select dbo.CheckDynaSQL ('SELECT *, FROM Orders')

• Result: Incorrect syntax near the keyword ‘FROM’.

 Select dbo.CheckDynaSQL ('SELECT * FROM NewOrders')

• Result: Invalid object name ‘NewOrders’.

 Select dbo.CheckDynaSQL ('SELECT * FROM Orders')

• Result: OK

Checking UPDATE statements

 Select dbo.CheckDynaSQL ('UPDATE NewOrders set Freight = 35 Where OrderID = 10248')

• Result:  Invalid object name ‘NewOrders’.

 Select dbo.CheckDynaSQL ('UPDATE Orders set Freight = 35 Where OrderID = 10248')

• Result:  OK

Checking DELETE statements

 Select dbo.CheckDynaSQL ('DELETE From NewOrders Where OrderID = 10248')

Result:  Invalid object name ‘NewOrders’.

 Select dbo.CheckDynaSQL ('DELETE From Orders Where OrderID = 10248')

Result:  OK

Checking INSERT statements

 Select dbo.CheckDynaSQL ('INSERT TO orders DEFAULT VALUES')

• Result:  Incorrect syntax near the keyword ‘TO’.

 Select dbo.CheckDynaSQL ('INSERT INTO orders DEFAULT VALUES')

• Result:  OK

Checking Stored Procedure execution statements

 Select dbo.CheckDynaSQL ('exec dbo.SalesByCategory "Beverages", "1999" ')

Result:  OK

 Select dbo.CheckDynaSQL ('exec dbo.SalesByCategory2 "Beverages", "1999" ')

• Result:  Could not find stored procedure ‘dbo.SalesByCategory2’.

Checking Function execution statements

 Select dbo.CheckDynaSQL ('select DateAdd (xx, -3, OrderDate) from orders where orderid = 10248') 

• Result:  ‘xx’ is not a recognized datediff option.

 Select dbo.CheckDynaSQL ('select DateAdd (mm, -3, OrderDate) from orders where orderid = 10248') 

• Result:  OK

Next Steps

• You can create this simple function in your application database and use it for preliminary checking of dynamic SQL queries.
• The procedures were tested using Microsoft SQL Server 2014 Standard Edition

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019