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

sql sql-server tsql dynamic-sql
Manoj Wadhwani picture Manoj Wadhwani · Apr 30, 2010 · Viewed 119.7k times · Source

Why do I get this error

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

when I try to use sp_executesql?

Answer

AdaTheDev picture AdaTheDev · Apr 30, 2010

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

Related questions

Declare Variable for a Query String

I was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, …

Read More
Getting result of dynamic SQL into a variable for sql-server

Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use …

Read More
Dynamic SQL to generate column names?

I have a query where I'm trying pivot row values into column names and currently I'm using SUM(Case...) As 'ColumnName' statements, like so: SELECT SKU1, SUM(Case When Sku2=157 Then Quantity Else 0 End) As '157', SUM(Case When …

Read More