May 22, 2008 at 5:42 am
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
May 22, 2008 at 5:50 am
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
June 10, 2009 at 5:03 am
Sql string should be always either ntext/nvarchar/nchar.
So ,please check type of sql query variable as nvarchar():w00t:
June 10, 2009 at 8:06 am
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
November 9, 2011 at 5:14 pm
this explains it why mine did not work also.. I had bigint and table data types..
Cheers,
John Esraelo
November 10, 2011 at 7:44 am
You can also use nvarchar(max) which replaces ntext.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 13, 2011 at 2:54 am
..with possibilities of truncation?
Cheers,
John Esraelo
April 27, 2016 at 1:15 pm
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():w00t:
Thank you! That solved my problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply