执行select_pagesize存储过程时
因为查询条件字符很长有4027
查询字符"1 = 1 and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000) '"
提示"不允许从数据类型 ntext 到 varchar 的隐性转换。请使用 CONVERT 函数来运行此查询。"
但是存储过程中的参数长度都增加到8000。为什么不是出错，请教如何修改下面的存储过程CREATE procedure select_pagesize
(
@select_list varchar(1000),--不需要select
@table_name varchar(1000),
@where varchar(8000),--不需要where
@primary_key varchar(1000),--当是表联合时，加表名前缀.
@order_by varchar(1000),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by*/declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(8000),@newin_where varchar(8000)
if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
endif @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO
因为查询条件字符很长有4027
查询字符"1 = 1 and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000) '"
提示"不允许从数据类型 ntext 到 varchar 的隐性转换。请使用 CONVERT 函数来运行此查询。"
但是存储过程中的参数长度都增加到8000。为什么不是出错，请教如何修改下面的存储过程CREATE procedure select_pagesize
(
@select_list varchar(1000),--不需要select
@table_name varchar(1000),
@where varchar(8000),--不需要where
@primary_key varchar(1000),--当是表联合时，加表名前缀.
@order_by varchar(1000),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by*/declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(8000),@newin_where varchar(8000)
if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
endif @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO
因为查询条件字符很长有4027
提示"不允许从数据类型 ntext 到 varchar 的隐性转换。请使用 CONVERT 函数来运行此查询。"
但是存储过程中的参数长度都增加到8000。为什么不是出错，请教如何修改下面的存储过程
由于你没有描述清楚你是怎么调用的(代码怎样),所以不太清楚你的ntext类型从哪来的
CREATE procedure select_pagesize
(
@select_list varchar(1000),--不需要select
@table_name varchar(1000),
@where varchar(8000),--不需要where
@primary_key varchar(1000),--当是表联合时，加表名前缀.
@order_by varchar(1000),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by*/declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(8000),@newin_where varchar(8000)
if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
endif @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO
传入的参数分别是：
@select_list ='*'
@table_name ='news'
@where =' 1 = 1 and Sort = 15 and right('000'+cast(Sort as varchar(3)),3)+right('000'+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right('000'+cast(Sort as varchar(3)),3)+right('000'+cast(Small_Sort as varchar(3)),3)+right('000'+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right('000'+cast(Sort as varchar(3)),3)+right('000'+cast(small_sort as varchar(3)),3)+right('000'+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000) '
@primary_key ='id'
@order_by ='order by id desc'
@page_size ='20'
@page_index ='1'
@do_count ='1'
" .. and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) .." 以及相似的地方。
内容 "in (..)"应该表现为:
" in (''015005'',''015010'',''015015'',''015020'',''015025'',''015030'',''015035'',''015040'',''015045'',''015050'') "
把@where,@sql_str的类型改为nvarchar(max)
不行呢。。检查语句都通不过
只要是执行正面的查询条件就会出现同样的提示
我觉得是查询条件太长的原因吧
因为只取一段做查询条件，也就是放大了查询结果范围都是正常的查询字符1 = 1 and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000)
建议把字符串类型全部改为nvarchar型，因为它可以根据实际值分配存储空间,而varchar则占用你定义时的空间。
在sql2000（2005不知道是多少。。）中，单行存储最多8060字节，超过的话就自动转化为ntext型了。
然后在执行你的查询语句时，又需要将ntext转回到varchar类型，而sql不能帮你做这个隐形转换，所以报上面的错。
请那位兄台帮忙修改一下
另外单行存储最多8060字节，超过的话就自动转化为ntext型了。
但是实际查询字符多4000字节
select len('1 = 1 and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000)')
的结果最多是4000CREATE procedure select_pagesize
(
@select_list varchar(1000),--不需要select
@table_name varchar(1000),
@where varchar(8000),--不需要where
@primary_key varchar(1000),--当是表联合时，加表名前缀.
@order_by varchar(1000),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name','tb_login',' login_name like ''%%''','login_id',' order by login_dt desc',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name','tb_login a,tb_code_province b',' a.pro_id=b.pro_id and a.login_name like ''%%''','a.login_id',' order by a.login_dt desc',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by*/declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(8000),@newin_where varchar(8000)
if @where=''--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where=''
select @newin_where=''
end
else
begin
select @new_where=' and '+@where
select @newin_where=' where '+@where
endif @do_count=1
select @sql_str='select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where=''
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' '+@order_by
else
select @sql_str='select top '+convert(varchar,@page_size)+ ' '+@select_list+' from '+@table_name+' where '+@where+' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str='select top '+convert(varchar,@page_size)+' '+@select_list+' from '+@table_name+' where '+@primary_key+' not in (select '+stuff(@primary_key,1,charindex('.',@primary_key),'')
select @sql_str=@sql_str+' from (select top '+convert(varchar,@record_min)+' '+@primary_key+' from '+@table_name+@newin_where+' '+@order_by+') temptable0000)'
select @sql_str=@sql_str+@new_where+' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO
是查询字符过找引起的问题，把查询字符减少到4000以内就行了
可能就是szx1999 兄台所说的换行 问题吧
请教如何修改存储过程
,@table_name ='tb'
,@where =' 1 = 1 and Sort = 15 and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3) in (015005,015010,015015,015020,015025,015030,015035,015040,015045,015050) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(Small_Sort as varchar(3)),3)+right(''000''+cast(Three_Sort as varchar(3)),3) in (015005005,015005010,015005015,015005020,015005025,015005030,015010005,015010010,015010015,015010020,015010025,015010030,015015005,015015010,015015015,015015020,015015025,015015030,015020005,015020010,015020015,015020020,015020025,015020030,015025005,015025010,015025015,015025025,015025025,015025030,015030005,015030010,015030015,015030020,015030025,015030030,015035005,015035010,015035015,015035020,015035025,015035030,015040005,015040010,015040015,015040020,015040025,015040030,015045005,015045010,015045015,015045020,015045025,015045030,015050005,015050010,015050015,015050020,015050025,015050030) and right(''000''+cast(Sort as varchar(3)),3)+right(''000''+cast(small_sort as varchar(3)),3)+right(''000''+cast(three_sort as varchar(3)),3) in (005000000,010000000,015000000,020000000,025000000,030000000,035000000,040000000,045000000,055000000,060000000,065000000,070000000,075000000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015010000,015015000,015020000,015025000,015030000,015035000,015040000,015045000,015050000,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065010000,065015000,065020000,065025000,065030000,065035000,065040000,065045000,065050000,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000,005000000,005005000,005010000,005015000,005020000,005025000,010000000,010005000,010010000,015000000,015005000,015005005,015005010,015005015,015005020,015005025,015005030,015010000,015010005,015010010,015010015,015010020,015010025,015010030,015015000,015015005,015015010,015015015,015015020,015015025,015015030,015020000,015020005,015020010,015020015,015020020,015020025,015020030,015025000,015025005,015025010,015025015,015025025,015025025,015025030,015030000,015030005,015030010,015030015,015030020,015030025,015030030,015035000,015035005,015035010,015035015,015035020,015035025,015035030,015040000,015040005,015040010,015040015,015040020,015040025,015040030,015045000,015045005,015045010,015045015,015045020,015045025,015045030,015050000,015050005,015050010,015050015,015050020,015050025,015050030,020000000,020005000,020010000,020015000,020020000,020025000,020030000,025000000,025005000,025010000,030000000,030005000,030010000,030015000,030020000,035000000,035005000,035010000,035015000,040000000,040005000,040010000,040015000,040020000,045000000,055000000,055005000,055010000,055015000,055020000,055025000,060000000,060005000,060010000,065000000,065005000,065005005,065005010,065005015,065005020,065005025,065005030,065010000,065010005,065010010,065010015,065010020,065010025,065010030,065015000,065015005,065015010,065015015,065015020,065015025,065015030,065020000,065020005,065020010,065020015,065020020,065020025,065020030,065025000,065025005,065025010,065025015,065025020,065025025,065025030,065030000,065030005,065030010,065030015,065030020,065030025,065030030,065035000,065035005,065035010,065035015,065035020,065035025,065035030,065040000,065040005,065040010,065040015,065040020,065040025,065040030,065045000,065045005,065045010,065045015,065045020,065045025,065045030,065050000,065050005,065050010,065050015,065050020,065050025,065050030,070000000,070005000,070010000,070015000,070020000,070025000,070030000,075000000,075005000,075010000,085000000,090000000,095000000,100000000) '
,@primary_key ='id'
,@order_by ='order by id desc'
,@page_size ='20'
,@page_index ='1'
,@do_count ='1' /*
result:
--------
0
*/
但是查询分析器中执行得到正常的结果
可程序里却还是老错误
快晕了。。
请看
http://topic.csdn.net/u/20080910/16/1afd2e93-ed85-4682-a511-3bfa4ff1ff27.html
在存储过程中，将@sql_str存入其中，程序出错后将tmp_debug表中的内容贴出来看看