I have a table, and want to test if dynamic sql will compile. Example:

create Table dbo.Product 
(
     ProductId int,
     ProductName varchar(255)
)

This will succeed: Column Name exists

declare @sqltext varchar(4000) = 'select ProductName from dbo.Product'
exec(@sqltext)

This will fail, Column Name does not exist

declare @sqltext varchar(4000) = 'select ProductTestABCDName from dbo.Product'
exec(@sqltext)

How do I ensure dynamic sql will compile before running?

What I could do is create test stored procedure to compile, then drop the procedure afterwards, however seems inefficient. Is there a better way to compile code?

create procedure dbo.TestCompile as
drop procedure dbo.TestCompile

Is there like a sp_compilecode function in Sql server?

up vote 5 down vote accepted

I found the information in this post about SQL Server function to check dynamic SQL syntax quite helpful.

The post author created a UDF that accepts a dynamic SQL statement and the UDF uses sys.dm_exec_describe_first_result_set (available in SQL Server 2012) to validate the SQL.

From that post:

-- =============================================
-- 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

Declare @cmd varchar(1000) 
set @cmd = 'select * from sys.databases'
Select dbo.CheckDynaSQL (@cmd)
---
OK

Declare @cmd varchar(1000) 
set @cmd = 'select * from sys.databas'
Select dbo.CheckDynaSQL (@cmd)
---
Invalid object name 'sys.databas'.

If you wrap it in a try catch block you can handle the exception to prevent the error from bubbling up.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.