Why we should not use varchar in "sp_executesql"?

Question

Whenever I try to use varchar in sp_executesql it's always throwing the following error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Is there any reason we should use nvarchar?

If "exec" allows us to use varchar, why not sp_executesql?

Sample Code:

DECLARE @IntVariable int;
DECLARE @SQLString **varchar**(500);--**we need to use nvarchar!! but why?**

DECLARE @ParmDefinition nvarchar(500);


SET @SQLString =
     'SELECT nationalidnumber, NationalIDNumber, Title, LoginID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE nationalidnumber = @BusinessEntityID';
SET @ParmDefinition = '@BusinessEntityID int';

SET @IntVariable = 14417807;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

http://stackoverflow.com/questions/2743890/why-do-i-get-procedure-expects-parameter-statement-of-type-ntext-nchar-nvar — MusicLovingIndianGirl, Apr 04 '16 at 10:13
Because `sp_executesql` is written to *expect* a SQL statement as a type `nvarchar` - that's just a design decision the guys made when writing that stored procedure. That's the way it's designed - so you need to comply with this. — marc_s, Apr 04 '16 at 10:14
@MusicLovingIndianGirl He asked about the reason. The link which you have given is not the reason — Debaprasad, Apr 04 '16 at 10:15
My guess would be that specifying unicode rather than non-unicode is simply more modern practice, for many good reasons (it might even help to mitigate against malicious attacks on dynamic SQL based on bad character encoding.) The reason exec supports non-Unicode is for backwards compatibility -- exec is as old as the hills and changing it would break lots of existing code, whereas sp_executesql was introduced (with unicode parameters from the start) in SQL Server 2008. But to know for sure, you'd have to ask Microsoft. — Matt Gibson, Apr 04 '16 at 10:28
Thanks for the valuable comments, So to use "unicode" they designed "sp_executesql" to accept only "nvarchar" — Dinesh, Apr 04 '16 at 10:32
sp_executesql is an extended procedure, those are picky about parameter datatypes, and there's no automatic coercion unlike other places — siggemannen, Mar 25 '23 at 15:20

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Msdn0

EXECUTE

Executes a command string or character string within a Transact-SQL batch

@string_variable

Is the name of a local variable. @string_variable can be any char, varchar, nchar, or nvarchar data type. These include the (max) data

types. [N] 'tsql_string'

Is a constant string. tsql_string can be any nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type.