Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server function to check dynamic SQL syntax


By:   |   Read Comments (6)   |   Related Tips: More > Functions - User Defined UDF


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 17, 2017 - 1:40:29 PM - Gerald Britton Back To Top

 

Note that this will not work if temporary tables are used


Monday, August 07, 2017 - 11:33:53 AM - Robert L Davis Back To Top

 Very clever! Curious why you limited input to varchar(2000) when the dynamic management function accepts nvarchar(max) for that parameter.

 


Friday, August 04, 2017 - 4:29:01 PM - Adrian Hernandez Back To Top

 Excellent. Thanks for sharing your experience and knowledge. This is a lifesaver.

 


Friday, August 04, 2017 - 9:31:52 AM - Bob Feldsien Back To Top

 Upon further checking..  If you use parameterized dynamic sql, it will not detect problems with the parameters.  Notice that in the following example, the data type of the @CaseId parameter does not match the data type in the actual table.  The call to the DMV returns 0 rows.  Still a very good function to know and an excellent tip.

CREATE TABLE ORG_CASE(id int, Description varchar(255))

INSERT INTO ORG_CASE(id, Description)
VALUES(1, 'Case Number 1')
 , (1003, 'Case 1003')

DECLARE @SQL nvarchar(4000)
 , @ParamDef nvarchar(500)
 , @CaseId varchar(255) = 'Case 1003'
 , @TableName varchar(128) = 'ORG_CASE'

SET @SQL = 'SELECT * FROM ' + @TableName + ' WHERE id = @CaseId'
SET @ParamDef = '@CaseId int'

SELECT error_number
 , error_message
FROM sys.dm_exec_describe_first_result_set(@SQL, null, 0)
WHERE error_message IS NOT NULL
 AND column_ordinal = 0

DROP TABLE ORG_CASE

 

  In the following example, ORG_CASE is an existing table where "id" is an int field.  Notice that


Friday, August 04, 2017 - 8:55:04 AM - Bob Feldsien Back To Top

I work in an environment where there is always a LOT of dynamic sql.  For debugging and dev I usually get the resulting sql with a print statement and then execute it in another SSMS window.  Definitely going to try this.


Friday, August 04, 2017 - 4:13:02 AM - RS Back To Top

Good tip!

Thanks  

 


Learn more about SQL Server tools