存储过程中的变量 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呢
------解决方案--------------------
------解决方案--------------------
为什么要用变更。直接用
- 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 = @strSql + ' and plans.planID =' + str(@PlanID); else if(@PlanID = 0) set @strSql = @strSql + ' AND (OrderSubjectState = 0 or OrderState = 0) and plans.EndTime>getdate() ';exec('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 IsZJfrom 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.StudentIDleft join dbo.BASStudent stu on stu.StudentID = orders.StudentID LEFT JOIN BASChannel CH ON CH.ChannelID=stu.ChannelIDLEFT JOIN BASChannel PCH ON PCH.ChannelID=CH.ParantIDleft 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 ' [email protected]+ ' )Ord where rownum between 0 and 1 ');