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

SqlSql ServerTsqlDynamic Sql

Sql Problem Overview

Why do I get this error

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

when I try to use sp_executesql?

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

Solution 3 - Sql

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

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions
Recommended Dynamic Sql Solutions

Previous Solution:

Is it possible to force ignore the :hover pseudoclass for iPhone/iPad users?

IpadMobile SafariIphonePseudo Class

Next Solution:

Simple way to reorder methods of a Java class in IntelliJ?

JavaRefactoringMethodsIntellij Idea

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionManoj WadhwaniView Question on Stackoverflow
Solution 1 - SqlAdaTheDevView Answer on Stackoverflow
Solution 2 - SqlDaniel RenshawView Answer on Stackoverflow
Solution 3 - SqlsimagleiView Answer on Stackoverflow