getting in error in using sp_executesql like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

• suhailquadri

  SSC Eights!

  Points: 829

  More actions

  March 25, 2008 at 11:56 pm

  #187713

  I have a stored procedure of this type:

  ALTER PROCEDURE INSERT_FLAT_DETAILS

  @TABLENAME VARCHAR(50),

  @VALUE VARCHAR(1) OUT

  AS

  BEGIN

  DECLARE @sql VARCHAR(1000)

  DECLARE @VALUE1 VARCHAR(10)

  SET @sql = N'SELECT @VALUE1 = COUNT(*) FROM '+ @TABLENAME

  exec sp_executesql @sql,N'@VALUE1 VARCHAR(10) OUTPUT',@VALUE1 OUTPUT

  print @sql

  print @value1

  END

  when ever i am executing this with this values like 'tablename','0' ,

  it is giving error like

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

  can any body help me in solving this. thanks in advance.

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  March 26, 2008 at 3:48 am

  #793913

  You're using the wrong datatype...

  From BOL:

  [ @stmt = ] stmt

  Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.

  -- end BOL --

  Change this row:

  DECLARE @sql VARCHAR(1000)

  to

  DECLARE @sql NVARCHAR(1000)

  /Kenneth

• suhailquadri

  SSC Eights!

  Points: 829

  More actions

  March 26, 2008 at 8:28 am

  #794069

  thanks a lot for giving help so early.

  i am very kindfull to you.

  it is executing properly with out any issues.

  thnx dude..