CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
Manoj Wadhwani
Manoj Wadhwani

Reputation: 1547

Why do I get "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'." when I try to use sp_executesql?

Why do I get this error

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

when I try to use sp_executesql?

Upvotes: 101

Views: 136874

Answers (3)

simaglei
simaglei

Reputation: 1248

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

Upvotes: 0

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

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

Upvotes: 24

AdaTheDev
AdaTheDev

Reputation: 147224

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

Upvotes: 228

Related Questions