96

Why do I get this error

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

when I try to use sp_executesql?

d219
  • 2,707
  • 5
  • 31
  • 36
Manoj Wadhwani
  • 1,487
  • 4
  • 20
  • 23

3 Answers3

221

Sounds like you're calling sp_executesql with a VARCHAR statement, when it needs to be NVARCHAR.

e.g. This will give the error because @SQL needs to 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
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • This does work, but the other answer (from Daniel Renshaw) is WAY more useful, most of the time. (since it doesn't need the pointless variable declaration) – Brondahl Feb 28 '19 at 12:03
23

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

DECLARE @SQL NVARCHAR(100) 
SET @SQL = N'SELECT TOP 1 * FROM sys.tables' 
EXECUTE sp_executesql @SQL
Sнаđошƒаӽ
  • 16,753
  • 12
  • 73
  • 90
Daniel Renshaw
  • 33,729
  • 8
  • 75
  • 94
0

I had missed another tiny detail: I forgot the brackets "(100)" behind NVARCHAR.

simaglei
  • 1,118
  • 1
  • 14
  • 14