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 ?

You can't specify column names with a parameter. You need to execute dynamic SQL for that. The same is true for table names, and any SQL object name. — TT., Dec 23 '16 at 11:16

3N1GM4 · Accepted Answer · 2016-12-23T11:34:39.980

4

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.

edited Dec 23 '16 at 11:34

answered Dec 23 '16 at 11:17

3N1GM4

3,372
3
19
40

Best use QUOTENAME for the column name. – TT. Dec 23 '16 at 11:23
what is the key difference between your example and my example ? because i tried it this way with same error. – Muflix Dec 23 '16 at 11:28
1

@Muflix apologies, looks like you can't concatenate the strings directly within the call to `sp_executesql` - I have edited my answer to reflect this and tested the result this time. – 3N1GM4 Dec 23 '16 at 11:35
@TT. how exactly do you mean? When setting the `@column` value? Like `DECLARE @column NVARCHAR(MAX) = QUOTENAME('Id') + ' INT'`? – 3N1GM4 Dec 23 '16 at 11:39
@3N1Gm4 thank you for your answer, it works now but in this approach is there any benefit against `exec()` function when it omits variable parameter ? I think it may allow sql injection because it does not know what has to be in @SQLString variable. – Muflix Dec 23 '16 at 12:11
Have a quick Google: [EXEC vs. sp_executeSQL](https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/), [Stored procedure EXEC vs sp_executesql difference?](http://stackoverflow.com/questions/14722201/stored-procedure-exec-vs-sp-executesql-difference) and [EXEC and sp_executesql – how are they different?](http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/) were the first 3 results I got and all look useful. – 3N1GM4 Dec 23 '16 at 12:14
`SET @sql = 'CREATE TYPE dbo.MyDataType AS TABLE ( ' + QUOTENAME(@column) + ')'`. Cf [QUOTENAME](https://msdn.microsoft.com/nl-be/library/ms176114.aspx) docu, if you can have arbitrary column names as a parameter eg names with spaces in it, or rectangular brackets, or special symbols, you need to use that function to properly escape the name. – TT. Dec 23 '16 at 12:14
@TT. but `SELECT QUOTENAME(@column)` returns `[Id INT]` - is it valid to wrap the column name **and** type in square brackets? I thought this was just for column names alone, which might contain spaces or other similar characters which cannot be used in an identified without wrapping it in the square brackets? – 3N1GM4 Dec 23 '16 at 12:17
@3N1GM4 yes, but the main advantage is the security check for parameters which we are leaving, therefore i need to still check sql injection characters, therefore it looks same as the exec() function in terms of security – Muflix Dec 23 '16 at 12:21
@Muflix if you have a further question on the specific benefits of one approach versus the other, or how they each work, I would suggest you post it as a separate question, or review the questions which already exist on SO dealing with that subject. – 3N1GM4 Dec 23 '16 at 12:22
@3N1GM4 Oh right. Not a good idea of the OP to pass column name and type in one parameter. The quotename is just for the name, not the type. – TT. Dec 23 '16 at 12:30
@TT. ok, that's what I thought - agree it's not good to pass both together, just wanted to make my example as analogous to OP's question as possible. – 3N1GM4 Dec 23 '16 at 12:36

sp_executesql throws error but simple EXEC() works

1 Answers1