Sql Server - SQL分页存储过程自动生成

2653885

　　dim field_info,theexceptfield,tablename
　　function createsqlstr
　　dim str : str=""
　　str=str & "@num int" & ""
　　str=str & "@page int" & ""
　　for each thefieldname in field_info
　　str=str & "@" & field_info(thefieldname) & ""
　　next
　　str=str & "@" & theexceptfield & ""
　　str=str & "declare @thepage int" & ""
　　str=str & "set @thepage=(@page-1) * @num" & ""
　　str=str & "set @sql='select top '+cast(@num as nvarchar(30))+' * from " & tablename
　　i=1
　　for each thefieldname in field_info
　　if i=1 then str=str & " where "
　　str=str & field_info(thefieldname) & "='+cast(@" & field_info(thefieldname) & " as nvarchar(30))+' and "
　　i=i+1
　　next
　　str=str & theexceptfield & " not in(select top '+cast(@thepage as nvarchar(30))+'" & " " & theexceptfield
　　str=str & " from " & tablename &    " where "
　　i=1
　　for each thefieldname in field_info
　　if i1 then str=str & " and "
　　str=str & field_info(thefieldname) & "='+cast(@" & field_info(thefieldname) & " as nvarchar(30))+'"
　　i=i+1
　　next
　　str=str & "order by " & theexceptfield &") order by " & theexceptfield & "'"
　　createsqlstr=str
　　end function
　　function setfieldnum
　　set thefieldname=document.getElementById("fieldname")
　　set thesetnum=document.getElementById("setnum")
　　thenum=thesetnum.value
　　if isnumeric(thenum) then
　　thenum=Cint(thenum)
　　str="请输入您所要查询的字段名：" & ""
　　for i=0 to thenum-1

　　str=str & "字段" & i+1 & "：　　next

　　str=str & "请输入您所用的标识字段名：" & "
　　str=str & "请输入表明：" & "　　str=str & "显示sql"
　　thefieldname.innerHTML=str
　　end if
　　end function
　　function saveinfo()
　　set theshowsql=document.getElementById("showsql")
　　set thefieldnames=document.getElementsByName("fieldnames")
　　set the_exceptfield=document.getElementById("exceptfield")
　　set field_info=createobject("scripting.dictionary")
　　set thetablename=document.getElementById("tablenamed")
　　theexceptfield=the_exceptfield.value
　　tablename=thetablename.value
　　for i=0 to thefieldnames.length-1
　　thevalue=thefieldnames(i).value
　　field_info.add thevalue,thevalue
　　next
　　str=createsqlstr
　　theshowsql.innerhtml=str
　　end function
　　
　　
　　
　　
　　
　　

　　设定：