Compile Dynamic Sql before Running 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?
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'.