SQL Server function to check dynamic SQL syntax

By: Eli Leiba | Comments (9) | Related: > Functions User Defined UDF

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" ')