关闭

关闭

关闭

封号提示

内容

首页 SQL之通用查询分页存储过程

SQL之通用查询分页存储过程.doc

SQL之通用查询分页存储过程

激动着要理由_
2018-02-04 0人阅读 0 0 0 暂无简介 举报

简介:本文档为《SQL之通用查询分页存储过程doc》,可适用于IT/计算机领域

SQL之通用查询分页存储过程ÍÓÃéÑÖÒæýÌâÊÔíifobjectid('person')isnotdroptablepersoncreatetableperson(idintprimarykeyidentity(,),usernamevarchar(),ageint,remarkvarchar())åÈëâÊÔÊýÝdeclareiintseti=whilei<begininsertintopersonvalues('zdw'convert(varchar(),i),i,convert(varchar(),i))seti=iendgoselect*fromperson****ÖÒæýÌ*ÄÜÃèÊöºÔÖíÖÐÂú×ãÌõþµÄÇÂÖÁÐøÐÐÖÒéÑÖÒÉÒÔËÐòµÐò*éÑÉÒÔÖÒóÐÖéÑÈÎÒâÒÖÊäö×ÖÎÁÐí*CREATEPROCEDUREsppagetbvarchar(),íÃûcolvarchar(),ÃÁÐÀøÐÐÖÒcoltypeint,colÁеÄÀàÐÍ,Êý×ÖÀàÐÍ,×ÖûÀàÐÍ,ÈÕÆÚÊäÀàÐÍorderbybit,ÅÅÐò,ËÐò,µÐòcollistvarchar(),ÒªéÑöµÄ×ÖÎÁÐí,*íÊÈ×ÖÎselecttypeint,éÑÀàÐÍ,ÇÒ,ºóÒ,Ê×Ò,ÄÒ,ÖÒpagesizeint,ÃÒÇÂÊýpageint,ÖÒminidvarchar(),µÇÒ×îкÅmaxidvarchar(),µÇÒ×îóºÅconditionvarchar()éÑÌõþASDECLAREsqlnvarchar(),wherevarchar(),wherevarchar()DECLAREiint,idvarchar()IFcoltype=orcoltype=×ÖÎÀàÐÍΪ×ÖûòÈÕÆÚÊäÒªÓÉÏÒýºÅÒÔ×ÈÏÓÃBEGINSETminid=''''minid''''SETmaxid=''''maxid''''ENDIFconditionisorrtrim(condition)=''ÃÓÐéÑÌõþBEGINSETwhere='WHERE'SETwhere=''ENDELSEÓÐéÑÌõþBEGINSETwhere='WHERE('condition')AND'ÀÓÐÌõþÔÙÓÉÏËÌõþSETwhere='WHERE('condition')'Ô,ÃÓÐÌõþøÓÉÏËÌõþENDSETsql=CASEselecttypeWHENÇÒTHEN'SELECT*FROM(SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwherecolCASEorderbyWHENTHEN'<'minidELSE'>'maxidEND'ORDERBY'colCASEorderbyWHENTHEN'DESC'ELSE''END')tORDERBY'colCASEorderbyWHENTHEN''ELSE'DESC'ENDWHENºóÒTHEN'SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwherecolCASEorderbyWHENTHEN'>'maxidELSE'<'minidEND'ORDERBY'colCASEorderbyWHENTHEN''ELSE'DESC'ENDWHENÊ×ÒTHEN'SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwhere'ORDERBY'colCASEorderbyWHENTHEN''ELSE'DESC'ENDWHENÄÒTHEN'SELECT*FROM(SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwhere'ORDERBY'colCASEorderbyWHENTHEN'DESC'ELSE''END')tORDERBY'colCASEorderbyWHENTHEN''ELSE'DESC'ENDENDIFselecttype>=andselecttype<=BEGINEXEC(sql)RETURNENDELSEBEGINÖÒIFcoltype=IForderby=SETid=''''''ELSESETid=''''CHAR()''''ELSEIFcoltype=IForderby=SETid=''''''ELSESETid=''''''ELSEIForderby=SETid=''ELSESETid=''SETi=ΪõÉÙÖºóSELECTTOPµÄÊýÝÁ,ËÃÌõÑ,ÒÎ,ÒÔÉÄÜÓüËùéÑÒWHILEi<pagesize*pageBEGINIFi<pagesize*pageBEGINIForderby=SETsql='SELECTid=CASE'CAST(coltypeASvarchar)'WHENTHEN''''''''CAST(MAX('col')ASvarchar())''''''''''''WHENTHEN''''''''CONVERT(char(),MAX('col'),)''''''''''''ELSECAST(MAX('col')ASvarchar)ENDFROM(SELECTTOP'col'FROM'tbwherecol'>'id'ORDERBY'col')t'ELSESETsql='SELECTid=CASE'CAST(coltypeASvarchar)'WHENTHEN''''''''CAST(MIN('col')ASvarchar())''''''''''''WHENTHEN''''''''CONVERT(char(),MIN('col'),)''''''''''''ELSECAST(MIN('col')ASvarchar)ENDFROM(SELECTTOP'col'FROM'tbwherecol'<'id'ORDERBY'col'DESC)t'EXECspexecutesqlsql,N'idvarchar()OUTPUT',idOUTPUTSETi=iIFi>=pagesize*pageBREAKENDELSEBREAKENDÉÏÃæµÄÑ,ÖÏÂÃæµÄ×ÓéÑ×îàÖÓÐÌõÊýÝIForderby=SETsql='SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwherecol'>'id'AND'col'NOTIN''(SELECTTOP'CAST(pagesize*(page)iASvarchar)''col'FROM'tbwherecol'>'id'ORDERBY'col')ORDERBY'colELSESETsql='SELECTTOP'CAST(pagesizeASvarchar)''collist'FROM'tbwherecol'<'id'AND'col'NOTIN''(SELECTTOP'CAST(pagesize*(page)iASvarchar)''col'FROM'tbwherecol'<'id'ORDERBY'col'DESC)ORDERBY'col'DESC'EXEC(sql)ENDGOâÊÔexecsppage'person','id',,,'*',,,,'','','id!='ÔÓÚÈÏòµµÄÖÒÐèÇóÒäsqlãµÚÒöíÊpagesize,µÚþöÓÃpagesize*(pagenum)ÀËãÈÈçȵÚÒ*(),µÚÒ*()selecttop*frompersonwhereidnotin(selecttopidfromperson)

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

评分:

/7

意见
反馈

立即扫码关注

爱问共享资料微信公众号

返回
顶部

举报
资料