邹建老师存储过程的问题，查询语句长度到一定长度时出错？

Question

CREATE Proc p_Show
@QueryStr nvarchar(4000),--表名、视图名、查询语句
@PageCurrent int=1,--要显示的页
@PageSize int=10,--每页的大小(行数)
@FdOrder nvarchar (1000)='',--排序字段列表
@FdType nvarchar (10)='',--排序字段列表
@FdShow nvarchar (4000)=''--要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
as
set nocount on
declare @FdName nvarchar(250)--表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20)--开始和结束的记录号
,@Obj_ID int--对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000)--复合主键列表
,@strjoin nvarchar(4000)--连接字段
,@strwhere nvarchar(2000)--查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder+' '+@FdType end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0--如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp--如果表中无主键,则用临时表处理select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1--检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+' 
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from(select top 100 percent * from'+@QueryStr+@FdOrder+')a
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO

Answer 1

SELECT *　FROM YiFaXianLu a INNER JOIN　XianLu_FBJQ b ON a.发布编号 = b.发布编号　WHERE (a.ID = 8)
这个明显有重复的列名嘛,看存储就知道对于sql语句采用了临时表,而临时表当然要求有唯一的列名

Answer 2

--例如,下面的语句就可以通过(sql语句长度为1427):declare @s nvarchar(4000)
select @s=''
select @s=@s+',a.'+quotename(name)+' '+quotename('a_'+name)
from syscolumns where object_id('sysobjects')=id order by colid
select @s
select @s=@s+',b.'+quotename(name)+' '+quotename('b_'+name)
from syscolumns where object_id('syscolumns')=id order by colid
set @s='select '+stuff(@s,1,1,'')+' from sysobjects a,syscolumns b where a.id=b.id'
select len(@s)
print(@s)
exec p_show @s

Answer 3

报错说SELECT查询语句长度不能超过128位个人觉得属于你的调用问题,比如字符串边界符指定不正确之类

Answer 4

谢谢邹建老师指教：
sql="SELECT * FROM XianLu WHERE (线路类型 = '国内游')  and 线路方向='四川方向'  and 线路名称 like '%成都%'  and 进出标志 like '%成都%' and 启用状态='y'"
存储过程调用：
Search_Auto_Page ["&sql&"],[1], [10], [XL_ID], [Desc], 
[XL_ID,线路名称,线路编号,线路方向,出发城市,线路性质,进出标志,启用状态]
这样是正常的；（存储过程我增加了一个升降序条件）
现在只有在sql语句中再加一个条件，就会报告说长度超过128位，不能执行存储过程！还有你提到的sql长度可以到1427，那么你上面写的那段过程如何放到你的通用存储过程中！

Answer 5

exec p_show @s  --@s中保存了我要调用的sql语句,这样不就传过去了么? 难道你只会直接传参数的调用,如果是的话,你可以事先print(@s),把结果复制到exec p_show的参数中

Answer 6

sql语句最长可以到4000,1427是我测试时使用的sql语句长度(而不是只可以到这个长度)你的调用方法很有问题,不知道你是否仔细去了解过存储过程参数传递方法?(如果没有多看联机帮助)
sql="SELECT * FROM XianLu WHERE (线路类型 = '国内游')  and 线路方向='四川方向'  and 线路名称 like '%成都%'  and 进出标志 like '%成都%' and 启用状态='y'"sql="exec p_show '" & replace(sql,"'","''") &"',1, 10, 'XL_ID', 'Desc', 
'XL_ID,线路名称,线路编号,线路方向,出发城市,线路性质,进出标志,启用状态'"

Answer 7

个人觉得你加入的升降序条件没有什么必要,因为你完全可以在指定排序字段列表的时候指定升降序,这样的好处是排序字段列表可以使用多个字段,每个字段可以有自己的升降序
例如:
exec p_show 'sysobjects',1,10,'id desc,xtype,name desc'

Answer 8

exec p_show @s  --@s中保存了我要调用的sql语句,这样不就传过去了么? 难道你只会直接传参数的调用,如果是的话,你可以事先print(@s),把结果复制到exec p_show的参数中将print(@s)的结果复制到exec p_show的参数中同样会报告长度超过128位的错！

Answer 9

而且我只要SQL语句长度没有到128时是正常的呀，这不表示调用方式正确吗？

Answer 10

晕,不知道该怎么说?你在那调用的,已经说过了你在程序中的调用是不正确的,下面是我在查询分析器中的调用,你自己数sql语句有多长吧
exec p_show 'select a.[name] [a_name],a.[id] [a_id],a.[xtype] [a_xtype],a.[uid] [a_uid],a.[info] [a_info],a.[status] [a_status],a.[base_schema_ver] [a_base_schema_ver],a.[replinfo] [a_replinfo],a.[parent_obj] [a_parent_obj],a.[crdate] [a_crdate],a.[ftcatid] [a_ftcatid],a.[schema_ver] [a_schema_ver],a.[stats_schema_ver] [a_stats_schema_ver],a.[type] [a_type],a.[userstat] [a_userstat],a.[sysstat] [a_sysstat],a.[indexdel] [a_indexdel],a.[refdate] [a_refdate],a.[version] [a_version],a.[deltrig] [a_deltrig],a.[instrig] [a_instrig],a.[updtrig] [a_updtrig],a.[seltrig] [a_seltrig],a.[category] [a_category],a.[cache] [a_cache],b.[name] [b_name],b.[id] [b_id],b.[xtype] [b_xtype],b.[typestat] [b_typestat],b.[xusertype] [b_xusertype],b.[length] [b_length],b.[xprec] [b_xprec],b.[xscale] [b_xscale],b.[colid] [b_colid],b.[xoffset] [b_xoffset],b.[bitpos] [b_bitpos],b.[reserved] [b_reserved],b.[colstat] [b_colstat],b.[cdefault] [b_cdefault],b.[domain] [b_domain],b.[number] [b_number],b.[colorder] [b_colorder],b.[autoval] [b_autoval],b.[offset] [b_offset],b.[collationid] [b_collationid],b.[language] [b_language],b.[status] [b_status],b.[type] [b_type],b.[usertype] [b_usertype],b.[printfmt] [b_printfmt],b.[prec] [b_prec],b.[scale] [b_scale],b.[iscomputed] [b_iscomputed],b.[isoutparam] [b_isoutparam],b.[isnullable] [b_isnullable],b.[collation] [b_collation],b.[tdscollation] [b_tdscollation] from sysobjects a,syscolumns b where a.id=b.id'

Answer 11

我大概知道了问题的所在，的确是我的调用存在问题，
exec p_show 'SELECT YiFaXianLu.线路名称 FROM YiFaXianLu INNER JOIN XianLu_FBJQ ON YiFaXianLu.发布编号 = XianLu_FBJQ.发布编号 WHERE (XianLu_FBJQ.出团日期  BETWEEN ''2005/04/01'' and ''2005/06/01'') and (YiFaXianLu.发布状态=''y'')'
可以执行了！！！
但我真的不明白这个变量为什么要''y''这样？也就是上面您说到的replace(sql,"'","''")这个是为何？？