存储过程中的变量 nvarchar(4000) 不够大怎么办？
存储过程中的变量 nvarchar(4000) 不够大怎么办？

代码如下：
create proc Select_LoadServiceOrderByPlan(@PlanID int,@SubjectID int,@StartIndex int,@EndIndex int)
as
  declare @strSql ntext
  set @strSql='select * from (
select row_number() over(order by orders.OrderSubmitTime ) rownum, orders.OrderID,orders.OrderCode,orders.IsDelete,orders.IsTuiBao,plans.PlanName,
  orders.StudentType,orders.OrderSubjectState, case orders.SubjectCheckedTime when ''1/1/1753 12:00:00'' then null else orders.SubjectCheckedTime end SubjectCheckedTime,orders.ContractNo,
orders.RepeatOrderPrice,CASE orders.FXPayType WHEN 0 THEN ''按标准收费'' WHEN 1 THEN ''按级收费'' ELSE ''无'' END FXPayType,
  orders.OrderState,orders.OrderSubmitTime,(orders.CheckedUserID)UserID,Orders.ChannelID,
ISNULL(Orders.IsRepeatPay,0) IsRepeatPay,
  orders.StudentID,
CASE CH.LEVEL WHEN 1 THEN CH.SampleName ELSE PCH.SampleName END ParantName,
CASE CH.LEVEL WHEN 2 THEN CH.SampleName ELSE NULL END ChannelName, 
orders.PlanID,users.UserName,stu.IDCardName,sub.SubjectName,
case orders.CheckedTime when ''1/1/1753 12:00:00'' then null else orders.CheckedTime end CheckedTime,sub.SubjectID ,sbj.RetTime,
CASE WHEN (SELECT COUNT(1) FROM dbo.BASSbjForStudent AS yn WHERE yn.StudentID = orders.StudentID AND yn.SubjectID =5 AND yn.IsReapted=1) <> 0 THEN 1 ELSE 0 END IsSX,
CASE WHEN (SELECT COUNT(1) FROM dbo.BASSbjForStudent AS zj WHERE zj.StudentID = orders.StudentID AND zj.SubjectID =1 AND zj.IsReapted=1) <> 0 THEN 1 ELSE 0 END IsZJ
from dbo.BASOrders orders 
left join dbo.BASPlan plans on plans.PlanID = orders.PlanID 
left join BASSubject sub on sub.SubjectID = plans.SubjectID 
left join BASSbjForStudent sbj on sbj.SubjectID=plans.SubjectID and sbj.studentID=Orders.StudentID
left join dbo.BASStudent stu on stu.StudentID = orders.StudentID 
LEFT JOIN BASChannel CH ON CH.ChannelID=stu.ChannelID
LEFT JOIN BASChannel PCH ON PCH.ChannelID=CH.ParantID
left join Sys_User users on users.UserID = orders.CheckedUserID 
where orders.IsDelete = 0 
 and orders.OrderSubmitTime is not null and ISNULL(orders.TuiBaoCheckState,0)=0 and Orders.StudentType=0 AND ISNULL(Orders.IsReject,0)=0 ';
  if(@SubjectID != 0)
  set @strSql = @strSql + ' and plans.SubjectID =' + str(@SubjectID);
  if(@PlanID != 0)
  set @strSql = @strSql + ' and plans.planID =' + str(@PlanID);
  else if(@PlanID = 0)
  set @strSql = @strSql + ' AND (OrderSubjectState = 0 or OrderState = 0) and plans.EndTime>getdate() ';
  set @strSql = @strSql + ' )Ord where rownum between {0} and {1} ';
exec sp_executesql @strSql

------解决方案--------------------
拼两个字符串！然后 exec(@sql_1 + @sql_2) 即可
------解决方案--------------------
nvarchar(max)
------解决方案--------------------
搞8K
------解决方案--------------------

SQL code

----这样的就足够用了
nvarchar(max)

------解决方案--------------------
用text呢

------解决方案--------------------
探讨

用text呢


------解决方案--------------------
为什么要用变更。直接用
SQL code
EXE('')

------解决方案--------------------
SQL code
create proc Select_LoadServiceOrderByPlan(@PlanID int,@SubjectID int,@StartIndex int,@EndIndex int)
as
  declare @strSql nvarchar(4000)
  if(@SubjectID != 0)
     set @strSql = @strSql + ' and plans.SubjectID =' + str(@SubjectID);
  if(@PlanID != 0)
    set @strSql = @strS