关闭

关闭

关闭

封号提示

内容

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

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

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

上传者: 激动着要理由_ 2018-02-04 评分 0 0 0 0 0 0 暂无简介 简介 举报

简介:本文档为《SQL之通用查询分页存储过程doc》,可适用于IT/计算机领域,主题内容包含SQL之通用查询分页存储过程ÍÓÃéÑÖÒæýÌâÊÔíifobjectid('person')isnotdroptablepersoncreate符等。

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)

0/200

精彩专题

上传我的资料

每篇奖励 +2积分

资料评价:

/7
0下载券 下载 加入VIP, 送下载券

意见
反馈

立即扫码关注

爱问共享资料微信公众号

返回
顶部