Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_executesql : Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Expand / Collapse
Author
Message
Posted Thursday, May 22, 2008 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2008 3:20 AM
Points: 1, Visits: 2
Hi,

I am using dynamic query in stored procedure. while i am trying to execute the SQL Query in the Procedure, i am getting the error 'Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Because i have passed the parameter as Varchar in sp_executesql. The parms must be nVarchar. But my issues is, the length of Dynamic SQL Query is more than 4000. nVarchar maximum lenght is 4000.

Please let me know the solution.

my Stored Procedure as,

CREATE procedure Search
(
@SearchKeyword Varchar(2000),
@Productid varchar(2000),
@Type varchar(200),
@SQLPerms varchar(5000)

)
as
Declare @SQL varchar(7000)
set @SQL=' This Statement length goes more than 4000.'
exec sp_executesql @SQL
GO

Appriciate your response asap.

Regards,
Vijay
Post #505092
Posted Thursday, May 22, 2008 5:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 44,769, Visits: 42,656
you can use exec, since you're not passing parameters in or out of the dynamic SQL

CREATE  procedure Search
(
@SearchKeyword Varchar(2000),
@Productid varchar(2000),
@Type varchar(200),
@SQLPerms varchar(5000)

)
as
Declare @SQL varchar(7000)
set @SQL=' This Statement length goes more than 4000.'
exec (@SQL)
GO

Make sure you're checking for SQL injection attempts.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #505098
Posted Wednesday, June 10, 2009 5:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 22, 2009 12:37 AM
Points: 1, Visits: 12
Sql string should be always either ntext/nvarchar/nchar.
So ,please check type of sql query variable as nvarchar()
Post #732134
Posted Wednesday, June 10, 2009 8:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 44,769, Visits: 42,656
mayank.and.friends (6/10/2009)
Sql string should be always either ntext/nvarchar/nchar.


Only necessary when using sp_executesql (which takes nvarchar parameters). Not important when using EXEC. Also, you cannot define a variable of type ntext.



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #732317
Posted Wednesday, November 9, 2011 5:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 539, Visits: 1,001
this explains it why mine did not work also.. I had bigint and table data types..


Cheers,
John Esraelo
Post #1203220
Posted Thursday, November 10, 2011 7:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:46 PM
Points: 1,908, Visits: 7,997
You can also use nvarchar(max) which replaces ntext.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"]<your code here>[/code]. You can find the IFCode tags on the left when you are writing a post.
How to Post Performance Problems
Post #1203541
Posted Sunday, November 13, 2011 2:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 539, Visits: 1,001
..with possibilities of truncation?

Cheers,
John Esraelo
Post #1204729
Posted Wednesday, April 27, 2016 1:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 27, 2016 1:15 PM
Points: 41, Visits: 95
mayank.and.friends (6/10/2009)
Sql string should be always either ntext/nvarchar/nchar.
So ,please check type of sql query variable as nvarchar()


Thank you! That solved my problem.



Post #1781453
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse