Compile Dynamic Sql before Running

Question

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?

Answer 1

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'.

Answer 2

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