sp_executesql throws error but simple EXEC() works

Question

I need to create a user defined type in dynamic way but

The exec() way:

-- This works
DECLARE @column NVARCHAR(MAX) = 'Id INT'
EXEC ('CREATE TYPE dbo.MyDataType AS TABLE ('+ @column +')')

The sp_executesql way:

-- This does not work
DECLARE @column NVARCHAR(MAX) = 'Id INT'
EXECUTE sp_executesql N'CREATE TYPE dbo.MyDataType AS TABLE ( @column )', @column;  

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Id'.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@column'.

What am I missing ?

Answer 1

You need to use dynamic SQL even when taking the sp_executesql approach:

DECLARE @column NVARCHAR(MAX) = N'Id INT'
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'CREATE TYPE dbo.MyDataType AS TABLE ( ' + @column + ')'
EXECUTE sp_executesql @sql

You can pass parameter definitions and values into sp_executesql, like this:

DECLARE @IntVariable int;  
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  

/* Build the SQL string one time.*/  
SET @SQLString =  
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
       FROM AdventureWorks2012.HumanResources.Employee   
       WHERE BusinessEntityID = @BusinessEntityID';  
SET @ParmDefinition = N'@BusinessEntityID tinyint';  

/* Execute the string with the first parameter value. */  
SET @IntVariable = 197;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable;  

/* Execute the same string with the second parameter value. */  
SET @IntVariable = 109;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable; 

(from the MSDN page on sp_executesql)

But I don't think this will fit your use case.