至今为止,sql标签无法给类似下面语句的in里传多个参数
select top 10 id,title from ks_article where tid in({$Param(0)}) order by id desc
原因是,科汛的sql标签,如果传多个参数时,本身就是用逗号隔开的,如
{SQL_标签名称(参数1,参数2...,参数n)}
下面我给大家修改思路,就是我们如果要传带逗号的参数,那么我们用其它的字符替换,等到sql语句解释时,再替换回来,如下调用方式。
{SQL_标签名称('20097062465721'|'20097082279507',参数2,...,参数n)
接下来我们就动手操作,以9.0版本为例说明:
第一步:
打开ks_cls/kesion.label.sqlcls.asp,找到183行
UserParamArr = Split(Replace(Replace(Replace(Replace(SqlLabel,LabelName&"(",""),")}",""),"""",""),"'",""),",")
改为
UserParamArr = Split(Replace(Replace(Replace(SqlLabel,LabelName&"(",""),")}",""),"""",""),",")
这个原来过滤了单引号了,由于我们可能在使用sql in时,传的参数可能是字符型的,必须带有单引号,特别是科汛的主表的栏目ID都是字符型的,参数都必须带单引号的,如
select top 10 id,title from ks_article where tid in('20097062465721','20097082279507') order by id desc
第二步:
同样这个文件里,,我们找到214行
FunctionSQL= Replace(FunctionSQL,"{$Param("&I&")}",UserParamArr(I),1,-1,1)
改为
FunctionSQL= Replace(FunctionSQL,"{$Param("&I&")}",Replace(UserParamArr(I),"|",","),1,-1,1)
第三步:
这样前台的标签解释就修改完成了,接下来打开admin/indclue/LabelSQL.asp
找到1624行
function ClearParam(byval SqlStr)
Dim I
For I=0 To 100
SqlStr=Replace(SqlStr,"{$Param(" & I & ")}",1)
Next
SqlStr=Replace(SqlStr,"{$CurrClassChildID}","'1'")
SqlStr=Replace(SqlStr,"{$CurrChannelID}",1)
SqlStr=Replace(SqlStr,"{$CurrClassID}",1)
SqlStr=Replace(SqlStr,"{$CurrInfoID}",1)
SqlStr=Replace(SqlStr,"{$CurrSpecialID}",1)
SqlStr=Replace(SqlStr,"{$GetUserName}",1)
ClearParam=ReplaceRequest(SqlStr)
exit function
End function
直接将以上代码替换为
function ClearParam(byval SqlStr)
sqlstr=Lcase(sqlstr)
if instr(SqlStr,"where")<>0 then sqlstr=split(SqlStr,"where")(0)
Dim I
For I=0 To 100
SqlStr=Replace(SqlStr,"{$param(" & I & ")}",1)
Next
SqlStr=Replace(SqlStr,"{$currclasschildid}","'1'")
SqlStr=Replace(SqlStr,"{$currchannelid}",1)
SqlStr=Replace(SqlStr,"{$currclassid}",1)
SqlStr=Replace(SqlStr,"{$currinfoid}",1)
SqlStr=Replace(SqlStr,"{$currspecialid}",1)
SqlStr=Replace(SqlStr,"{$getusername}",1)
ClearParam=ReplaceRequest(SqlStr)
exit function
End function
经过上面三步修改,你的sql标签可以使用带逗号的参数的了。
以下应用实例。
我们可以根据sql标签调用指定几个栏目的最新文章,如sql语句
select top 10 id,title from ks_article where tid in({$Param(0)}) order by id desc
模板里调用
{SQL_标签名称('20097062465721'|'20097082279507'|'20093308484295')
再比如,调用指定几个会员的用户信息
sql语句
select userid,username,realname from ks_user where userid in({$Param(0)}) order by userid
模板里调用
{SQL_标签名称(2|3|4)}