Post reply

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

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

  • 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

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply