-1

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'.

SysDragon
  • 9,193
  • 15
  • 53
  • 86
Moutasim Momani
  • 129
  • 1
  • 3
  • 6

3 Answers3

1

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.

Bridge
  • 27,772
  • 8
  • 56
  • 78
  • I construct the @SQL as varchar parameter then pass it to sp_executesql. – Moutasim Momani Feb 12 '13 at 08:54
  • Then I've told you how to fix it - declare it as an `nvarchar` parameter of appropriate length (if greater than 4000 characters needed, use `nvarchar(max)`). – Bridge Feb 12 '13 at 09:00
  • i did this and the same, the @SQL trimmed and can't contain all the dynamic query i built. – Moutasim Momani Feb 12 '13 at 09:10
  • Does it work with `Exec` directly rather than using `sp_executesql` (should be easy to test if you aren't using parameters). Also, why not split your script into batches? I doubt it _needs_ to be that long. – Bridge Feb 12 '13 at 09:13
0

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
gbn
  • 394,550
  • 75
  • 549
  • 647
0

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
Eray Balkanli
  • 6,960
  • 9
  • 39
  • 65
user2001117
  • 3,329
  • 15
  • 18
  • When i used Nvarchar the @SQL trimmed and became not completed i have to use Varchar instead of. – Moutasim Momani Feb 12 '13 at 08:51
  • 1
    It gets truncated because of this: http://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql/1371584#1371584 – gbn Feb 12 '13 at 09:16