Why I get "The procedure expects a parameter" @statement "of type" ntext / nchar / nvarchar "." when i try to use sp_executesql?

Question

Why am I getting this error

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

when i try to use sp_executesql?

Answer 1

It looks like you are calling sp_executesql with the VARCHAR statement when it should be NVARCHAR.

eg. This will give an error because @SQL must be NVARCHAR

 DECLARE @SQL VARCHAR(100) SET @SQL = 'SELECT TOP 1 * FROM sys.tables' EXECUTE sp_executesql @SQL 

So:

 DECLARE @SQL NVARCHAR(100) SET @SQL = 'SELECT TOP 1 * FROM sys.tables' EXECUTE sp_executesql @SQL 

Answer 2

The solution is to put N in front of its type and SQL string to indicate that it is a double-byte character string:

 DECLARE @SQL NVARCHAR(100) SET @SQL = N'SELECT TOP 1 * FROM sys.tables' EXECUTE sp_executesql @SQL