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

    Question

  • Hi,

    When i executed the following query i am getting following error "Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."

    I have to store value in @EndDate variable.

    Please advise

    Declare @SQL varchar(max)
    Declare @Prd varchar(2)
    Declare @Year varchar(4)
    Declare @Wk varchar(2)
    Declare @EndDate  varchar(100)
    Declare @EndDate1  varchar(100)

    select  @Prd = RIGHT('0' + cast(AS_OF_PRD AS varchar(2)),2),
            @Year = AS_OF_YR,
            @Wk = RIGHT('0' + cast(AS_OF_WK AS varchar(2)),2)
      from SCP_HEADER

     
      set @SQL = 'SELECT @EndDate = WK ' + @Wk +'_END_DATE  from dbo.SCP_CAL_YEAR where CAL_YR =' + @Year
     
    execute SP_EXECUTESQL @SQL,N'@EndDate varchar(100) OUTPUT',@EndDate OUTPUT
     
      print @EndDate

     

     

    Regards,

    Amit

    Tuesday, August 30, 2011 4:18 PM

Answers

  • This is quite simple once you read carefully the text of an error. Your @SQL variable should be declared as NVARCHAR(max) and not as VARCHAR(max).

    Also, why do you want to concatenate @Year instead of also passing it as a parameter?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 30, 2011 4:23 PM
    Moderator
  • Hallo Amit,

    the bug is here

    Declare @SQL nvarchar(4000)

    ...

    set @SQL = N'SELECT @EndDate = WK ' + @Wk +'_END_DATE  from dbo.SCP_CAL_YEAR where CAL_YR =' + @Year

    BTW: I'm wondering why you concatenate the SQL String but didn't put them as variables to sp_executeSQL
    Exactly that's one of the essentials of sp_executeSQL

    http://msdn.microsoft.com/en-us/library/aa933299(v=sql.80).aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Tuesday, August 30, 2011 4:24 PM
  • N' means unicode. When we add N' to the constant we indicate that the value is going to be of nvarchar type. If we already declared the variable as nvarchar it's unnecessary, but still helpful.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 01, 2011 3:38 AM
    Moderator

All replies

  • This is quite simple once you read carefully the text of an error. Your @SQL variable should be declared as NVARCHAR(max) and not as VARCHAR(max).

    Also, why do you want to concatenate @Year instead of also passing it as a parameter?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, August 30, 2011 4:23 PM
    Moderator
  • Hallo Amit,

    the bug is here

    Declare @SQL nvarchar(4000)

    ...

    set @SQL = N'SELECT @EndDate = WK ' + @Wk +'_END_DATE  from dbo.SCP_CAL_YEAR where CAL_YR =' + @Year

    BTW: I'm wondering why you concatenate the SQL String but didn't put them as variables to sp_executeSQL
    Exactly that's one of the essentials of sp_executeSQL

    http://msdn.microsoft.com/en-us/library/aa933299(v=sql.80).aspx


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Tuesday, August 30, 2011 4:24 PM
  • Hi Uwe Ricken,

     

    Thanks for your valuable response !!

     

    What is significance of adding  'N' before SELECT Keyword in below query ?

    set @SQL = N'SELECT @EndDate = WK ' + @Wk +'_END_DATE  from dbo.SCP_CAL_YEAR where CAL_YR =' + @Year

    Please explain.

     

     

    Regards,

    Amit

    Thursday, September 01, 2011 3:35 AM
  • N' means unicode. When we add N' to the constant we indicate that the value is going to be of nvarchar type. If we already declared the variable as nvarchar it's unnecessary, but still helpful.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 01, 2011 3:38 AM
    Moderator