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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » getting in error in using sp_executesql like...

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

Rate Topic

Display Mode

Topic Options

Author

Message

suhailquadri

Posted Tuesday, March 25, 2008 11:56 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:08 AM
Points: 49, Visits: 81

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.

Post #474516

Kenneth Wilhelmsson

Posted Wednesday, March 26, 2008 3:48 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:45 AM
Points: 1,559, Visits: 656

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

Post #474585

suhailquadri

Posted Wednesday, March 26, 2008 8:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:08 AM
Points: 49, Visits: 81

thanks a lot for giving help so early.
i am very kindfull to you.
it is executing properly with out any issues.
thnx dude..

Post #474766

« Prev Topic | Next Topic »

Permissions