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?