Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

getting in error in using sp_executesql like Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar' Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2008 11:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:39 PM
Points: 53, Visits: 86
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
Posted Wednesday, March 26, 2008 3:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, January 9, 2014 3:33 AM
Points: 1,559, Visits: 672
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
Posted Wednesday, March 26, 2008 8:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:39 PM
Points: 53, Visits: 86
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 »

Add to briefcase

Permissions Expand / Collapse