Call sp_executesql with varchar parameter

Question

I faced problem when I called sp_executesql and I passed varchar parameter.
I got this error:

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

Answer 1

This means the @statement parameter of sp_executesql expects nvarchar.

This does not mean that your parameter has to be nvarchar.
Of course, if you don't have parameters, why are you using sp_executesql?

CREATE TABLE #foo (bar varchar(100) NOT NULL);
DECLARE @filter varchar(100) = 'bob';
EXEC sys.sp_executesql
      N'SELECT * FROM #foo WHERE bar = @p1', --NVARCHAR because of N prefix
      N'@p1 varchar(100)', --NVARCHAR because of N prefix
      @filter --VARCHAR
DROP TABLE #foo

Answer 2

Psychic debugger says you either are passing to SP_ExecuteSQL a variable of type varchar (make it nvarchar), or you've got a string you haven't used the unicode prefix on:

E.g.

Exec sp_executesql 'select * from something'

To fix it use:

Exec sp_executesql N'select * from something'

Notice the N prefix on the string.

Answer 3

Try this :

e.g. This will give the error because @SQL needs to be NVARCHAR

Below give error:

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

So: Use NVARCHAR(100)

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