Why does sp_executesql REQUIRE ntext/nchar/nvarchar parameters and statements as opposed to text/char/varchar?
I understand how to work around this issue (by having all statements/parameters be declared as nvarchar), but why does Microsoft impose this? What are the benefits?
I'm not sure that this is enforced for any particular reason specific to that procedure. Another example of a system stored procedure that is fussy about datatypes is sp_trace_create.
Not only does this demand an nvarchar for the @tracefile parameter but also the parameter @maxfilesize must be passed as 'bigint' and it doesn't accept a literal integer which would always be able to cast fine.
This Fails
DECLARE @TraceID int
EXEC sp_trace_create @TraceID OUTPUT, 0, N'X:\Foo', 1, NULL
This Succeeds
DECLARE @TraceID int
DECLARE @x bigint = 1
EXEC sp_trace_create @TraceID OUTPUT, 0, N'X:\Foo', @x, NULL
Both of these show up as System Extended Stored Procedures in the master database. I assume that the calling mechanism for these extended stored procedures is different from that used for regular stored procedures such that SQL Server doesn't implicitly cast the parameters.
It isn't necessarily a bad thing that sp_executesql enforces nvarchar like that though. One potential problem that can occur with dynamic SQL is that if a string is supplied as nvarchar then sanitised then coerced to varchar the conversion can open up SQL injection opportunities. An example of that is in my answer here.
