文件名称:
ASP.NET分页二 storeprocedure
开发工具:
文件大小: 2kb
下载次数: 0
上传时间: 2008-12-05
详细说明： 其中的存储过程 i f e x i s t s ( s e l e c t * f r o m d b o . s y s o b j e c t s w h e r e i d = o b j e c t _ i d ( N ' [ d b o ] . [ P a g i n g _ A s c _ D e s c ] ' ) a n d O B J E C T P R O P E R T Y ( i d , N ' I s P r o c e d u r e ' ) = 1 ) d r o p p r o c e d u r e [ d b o ] . [ P a g i n g _ A s c _ D e s c ] G O i f e x i s t s ( s e l e c t * f r o m d b o . s y s o b j e c t s w h e r e i d = o b j e c t _ i d ( N ' [ d b o ] . [ P a g i n g _ C u r s o r ] ' ) a n d O B J E C T P R O P E R T Y ( i d , N ' I s P r o c e d u r e ' ) = 1 ) d r o p p r o c e d u r e [ d b o ] . [ P a g i n g _ C u r s o r ] G O i f e x i s t s ( s e l e c t * f r o m d b o . s y s o b j e c t s w h e r e i d = o b j e c t _ i d ( N ' [ d b o ] . [ P a g i n g _ R o w C o u n t ] ' ) a n d O B J E C T P R O P E R T Y ( i d , N ' I s P r o c e d u r e ' ) = 1 ) d r o p p r o c e d u r e [ d b o ] . [ P a g i n g _ R o w C o u n t ] G O i f e x i s t s ( s e l e c t * f r o m d b o . s y s o b j e c t s w h e r e i d = o b j e c t _ i d ( N ' [ d b o ] . [ P a g i n g _ S u b Q u e r y ] ' ) a n d O B J E C T P R O P E R T Y ( i d , N ' I s P r o c e d u r e ' ) = 1 ) d r o p p r o c e d u r e [ d b o ] . [ P a g i n g _ S u b Q u e r y ] G O S E T Q U O T E D _ I D E N T I F I E R O N G O S E T A N S I _ N U L L S O N G O C R E A T E P R O C E D U R E P a g i n g _ A s c _ D e s c ( @ T a b l e s v a r c h a r ( 1 0 0 0 ) , @ P K v a r c h a r ( 1 0 0 ) , @ S o r t v a r c h a r ( 2 0 0 ) = N U L L , @ P a g e N u m b e r i n t = 1 , @ P a g e S i z e i n t = 1 0 , @ F i e l d s v a r c h a r ( 1 0 0 0 ) = ' * ' , @ F i l t e r v a r c h a r ( 1 0 0 0 ) = N U L L , @ G r o u p v a r c h a r ( 1 0 0 0 ) = N U L L ) A S / * F i n d t h e @ P K t y p e * / D E C L A R E @ P K T a b l e v a r c h a r ( 1 0 0 ) D E C L A R E @ P K N a m e v a r c h a r ( 1 0 0 ) D E C L A R E @ t y p e v a r c h a r ( 1 0 0 ) D E C L A R E @ p r e c i n t I F C H A R I N D E X ( ' . ' , @ P K ) > 0 B E G I N S E T @ P K T a b l e = S U B S T R I N G ( @ P K , 0 , C H A R I N D E X ( ' . ' , @ P K ) ) S E T @ P K N a m e = S U B S T R I N G ( @ P K , C H A R I N D E X ( ' . ' , @ P K ) + 1 , L E N ( @ P K ) ) E N D E L S E B E G I N S E T @ P K T a b l e = @ T a b l e s S E T @ P K N a m e = @ P K E N D S E L E C T @ t y p e = t . n a m e , @ p r e c = c . p r e c F R O M s y s o b j e c t s o J O I N s y s c o l u m n s c o n o . i d = c . i d J O I N s y s t y p e s t o n c . x u s e r t y p e = t . x u s e r t y p e W H E R E o . n a m e = @ P K T a b l e A N D c . n a m e = @ P K N a m e I F C H A R I N D E X ( ' c h a r ' , @ t y p e ) > 0 S E T @ t y p e = @ t y p e + ' ( ' + C A S T ( @ p r e c A S v a r c h a r ) + ' ) ' D E C L A R E @ s t r P a g e S i z e v a r c h a r ( 5 0 ) D E C L A R E @ s t r R o w s v a r c h a r ( 5 0 ) D E C L A R E @ s t r F i l t e r v a r c h a r ( 8 0 0 0 ) D E C L A R E @ s t r G r o u p v a r c h a r ( 8 0 0 0 ) D E C L A R E @ s t r S o r t C o l u m n v a r c h a r ( 4 0 0 0 ) D E C L A R E @ s t r S o r t D e s c v a r c h a r ( 4 0 0 0 ) / * D e f a u l t S o r t i n g * / I F @ S o r t I S N U L L S E T @ S o r t = @ P K / * S e t s o r t i n g v a r i a b l e s . * / I F C H A R I N D E X ( ' D E S C ' , @ S o r t ) > 0 B E G I N S E T @ s t r S o r t D e s c = R E P L A C E ( @ S o r t , ' D E S C ' , ' A S C ' ) S E T @ s t r S o r t C o l u m n = ' , ' + R E P L A C E ( @ S o r t , ' D E S C ' , ' ' ) E N D E L S E B E G I N I F C H A R I N D E X ( ' A S C ' , @ S o r t ) = 0 B E G I N S E T @ s t r S o r t D e s c = @ S o r t + ' D E S C ' S E T @ s t r S o r t C o l u m n = ' , ' + @ S o r t E N D E L S E B E G I N S E T @ s t r S o r t D e s c = R E P L A C E ( @ S o r t , ' A S C ' , ' D E S C ' ) S E T @ s t r S o r t C o l u m n = ' , ' + R E P L A C E ( @ S o r t , ' A S C ' , ' ' ) E N D E N D I F @ S o r t = @ P K S E T @ s t r S o r t C o l u m n = ' ' / * D e f a u l t P a g e N u m b e r * / I F @ P a g e N u m b e r < 1 S E T @ P a g e N u m b e r = 1 / * S e t p a g i n g v a r i a b l e s . * / S E T @ s t r P a g e S i z e = C O N V E R T ( v a r c h a r ( 5 0 ) , @ P a g e S i z e ) S E T @ s t r R o w s = C O N V E R T ( v a r c h a r ( 5 0 ) , ( @ P a g e S i z e * ( @ P a g e N u m b e r - 1 ) + @ P a g e S i z e ) ) / * S e t f i l t e r & g r o u p v a r i a b l e s . * / I F @ F i l t e r I S N O T N U L L A N D @ F i l t e r ! = ' ' B E G I N S E T @ s t r F i l t e r = ' W H E R E ' + @ F i l t e r + ' ' E N D E L S E B E G I N S E T @ s t r F i l t e r = ' ' E N D I F @ G r o u p I S N O T N U L L A N D @ G r o u p ! = ' ' S E T @ s t r G r o u p = ' G R O U P B Y ' + @ G r o u p + ' ' E L S E S E T @ s t r G r o u p = ' ' / * E x e c u t e d y n a m i c q u e r y * / E X E C ( ' D E C L A R E @ t b l P K T A B L E ( P K ' + @ t y p e + ' N O T N U L L P R I M A R Y K E Y ) I N S E R T I N T O @ t b l P K S E L E C T T O P ' + @ s t r P a g e S i z e + ' ' + @ P K + ' F R O M ( S E L E C T T O P ' + @ s t r R o w s + ' ' + @ P K + @ s t r S o r t C o l u m n + ' F R O M ' + @ T a b l e s + @ s t r F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t + ' ) A S ' + @ P K T a b l e + ' O R D E R B Y ' + @ s t r S o r t D e s c + ' S E L E C T ' + @ F i e l d s + ' F R O M ' + @ T a b l e s + ' J O I N @ t b l P K t b l P K O N ' + @ P K + ' = t b l P K . P K ' + @ s t r F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t ) G O S E T Q U O T E D _ I D E N T I F I E R O F F G O S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O S E T A N S I _ N U L L S O N G O C R E A T E P R O C E D U R E P a g i n g _ C u r s o r ( @ T a b l e s v a r c h a r ( 1 0 0 0 ) , @ P K v a r c h a r ( 1 0 0 ) , @ S o r t v a r c h a r ( 2 0 0 ) = N U L L , @ P a g e N u m b e r i n t = 1 , @ P a g e S i z e i n t = 1 0 , @ F i e l d s v a r c h a r ( 1 0 0 0 ) = ' * ' , @ F i l t e r v a r c h a r ( 1 0 0 0 ) = N U L L , @ G r o u p v a r c h a r ( 1 0 0 0 ) = N U L L ) A S / * F i n d t h e @ P K t y p e * / D E C L A R E @ P K T a b l e v a r c h a r ( 1 0 0 ) D E C L A R E @ P K N a m e v a r c h a r ( 1 0 0 ) D E C L A R E @ t y p e v a r c h a r ( 1 0 0 ) D E C L A R E @ p r e c i n t I F C H A R I N D E X ( ' . ' , @ P K ) > 0 B E G I N S E T @ P K T a b l e = S U B S T R I N G ( @ P K , 0 , C H A R I N D E X ( ' . ' , @ P K ) ) S E T @ P K N a m e = S U B S T R I N G ( @ P K , C H A R I N D E X ( ' . ' , @ P K ) + 1 , L E N ( @ P K ) ) E N D E L S E B E G I N S E T @ P K T a b l e = @ T a b l e s S E T @ P K N a m e = @ P K E N D S E L E C T @ t y p e = t . n a m e , @ p r e c = c . p r e c F R O M s y s o b j e c t s o J O I N s y s c o l u m n s c o n o . i d = c . i d J O I N s y s t y p e s t o n c . x u s e r t y p e = t . x u s e r t y p e W H E R E o . n a m e = @ P K T a b l e A N D c . n a m e = @ P K N a m e I F C H A R I N D E X ( ' c h a r ' , @ t y p e ) > 0 S E T @ t y p e = @ t y p e + ' ( ' + C A S T ( @ p r e c A S v a r c h a r ) + ' ) ' D E C L A R E @ s t r P a g e S i z e v a r c h a r ( 5 0 ) D E C L A R E @ s t r S t a r t R o w v a r c h a r ( 5 0 ) D E C L A R E @ s t r F i l t e r v a r c h a r ( 1 0 0 0 ) D E C L A R E @ s t r G r o u p v a r c h a r ( 1 0 0 0 ) / * D e f a u l t S o r t i n g * / I F @ S o r t I S N U L L O R @ S o r t = ' ' S E T @ S o r t = @ P K / * D e f a u l t P a g e N u m b e r * / I F @ P a g e N u m b e r < 1 S E T @ P a g e N u m b e r = 1 / * S e t p a g i n g v a r i a b l e s . * / S E T @ s t r P a g e S i z e = C A S T ( @ P a g e S i z e A S v a r c h a r ( 5 0 ) ) S E T @ s t r S t a r t R o w = C A S T ( ( ( @ P a g e N u m b e r - 1 ) * @ P a g e S i z e + 1 ) A S v a r c h a r ( 5 0 ) ) / * S e t f i l t e r & g r o u p v a r i a b l e s . * / I F @ F i l t e r I S N O T N U L L A N D @ F i l t e r ! = ' ' S E T @ s t r F i l t e r = ' W H E R E ' + @ F i l t e r + ' ' E L S E S E T @ s t r F i l t e r = ' ' I F @ G r o u p I S N O T N U L L A N D @ G r o u p ! = ' ' S E T @ s t r G r o u p = ' G R O U P B Y ' + @ G r o u p + ' ' E L S E S E T @ s t r G r o u p = ' ' / * E x e c u t e d y n a m i c q u e r y * / E X E C ( ' D E C L A R E @ P a g e S i z e i n t S E T @ P a g e S i z e = ' + @ s t r P a g e S i z e + ' D E C L A R E @ P K ' + @ t y p e + ' D E C L A R E @ t b l P K T A B L E ( P K ' + @ t y p e + ' N O T N U L L P R I M A R Y K E Y ) D E C L A R E P a g i n g C u r s o r C U R S O R D Y N A M I C R E A D _ O N L Y F O R S E L E C T ' + @ P K + ' F R O M ' + @ T a b l e s + @ s t r F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t + ' O P E N P a g i n g C u r s o r F E T C H R E L A T I V E ' + @ s t r S t a r t R o w + ' F R O M P a g i n g C u r s o r I N T O @ P K S E T N O C O U N T O N W H I L E @ P a g e S i z e > 0 A N D @ @ F E T C H _ S T A T U S = 0 B E G I N I N S E R T @ t b l P K ( P K ) V A L U E S ( @ P K ) F E T C H N E X T F R O M P a g i n g C u r s o r I N T O @ P K S E T @ P a g e S i z e = @ P a g e S i z e - 1 E N D C L O S E P a g i n g C u r s o r D E A L L O C A T E P a g i n g C u r s o r S E L E C T ' + @ F i e l d s + ' F R O M ' + @ T a b l e s + ' J O I N @ t b l P K t b l P K O N ' + @ P K + ' = t b l P K . P K ' + @ s t r F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t ) G O S E T Q U O T E D _ I D E N T I F I E R O F F G O S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O S E T A N S I _ N U L L S O N G O C R E A T E P R O C E D U R E P a g i n g _ R o w C o u n t ( @ T a b l e s v a r c h a r ( 1 0 0 0 ) , @ P K v a r c h a r ( 1 0 0 ) , @ S o r t v a r c h a r ( 2 0 0 ) = N U L L , @ P a g e N u m b e r i n t = 1 , @ P a g e S i z e i n t = 1 0 , @ F i e l d s v a r c h a r ( 1 0 0 0 ) = ' * ' , @ F i l t e r v a r c h a r ( 1 0 0 0 ) = N U L L , @ G r o u p v a r c h a r ( 1 0 0 0 ) = N U L L ) A S / * D e f a u l t S o r t i n g * / I F @ S o r t I S N U L L O R @ S o r t = ' ' S E T @ S o r t = @ P K / * F i n d t h e @ P K t y p e * / D E C L A R E @ S o r t T a b l e v a r c h a r ( 1 0 0 ) D E C L A R E @ S o r t N a m e v a r c h a r ( 1 0 0 ) D E C L A R E @ s t r S o r t C o l u m n v a r c h a r ( 2 0 0 ) D E C L A R E @ o p e r a t o r c h a r ( 2 ) D E C L A R E @ t y p e v a r c h a r ( 1 0 0 ) D E C L A R E @ p r e c i n t / * S e t s o r t i n g v a r i a b l e s . * / I F C H A R I N D E X ( ' D E S C ' , @ S o r t ) > 0 B E G I N S E T @ s t r S o r t C o l u m n = R E P L A C E ( @ S o r t , ' D E S C ' , ' ' ) S E T @ o p e r a t o r = ' < = ' E N D E L S E B E G I N I F C H A R I N D E X ( ' A S C ' , @ S o r t ) = 0 S E T @ s t r S o r t C o l u m n = R E P L A C E ( @ S o r t , ' A S C ' , ' ' ) S E T @ o p e r a t o r = ' > = ' E N D I F C H A R I N D E X ( ' . ' , @ s t r S o r t C o l u m n ) > 0 B E G I N S E T @ S o r t T a b l e = S U B S T R I N G ( @ s t r S o r t C o l u m n , 0 , C H A R I N D E X ( ' . ' , @ s t r S o r t C o l u m n ) ) S E T @ S o r t N a m e = S U B S T R I N G ( @ s t r S o r t C o l u m n , C H A R I N D E X ( ' . ' , @ s t r S o r t C o l u m n ) + 1 , L E N ( @ s t r S o r t C o l u m n ) ) E N D E L S E B E G I N S E T @ S o r t T a b l e = @ T a b l e s S E T @ S o r t N a m e = @ s t r S o r t C o l u m n E N D S E L E C T @ t y p e = t . n a m e , @ p r e c = c . p r e c F R O M s y s o b j e c t s o J O I N s y s c o l u m n s c o n o . i d = c . i d J O I N s y s t y p e s t o n c . x u s e r t y p e = t . x u s e r t y p e W H E R E o . n a m e = @ S o r t T a b l e A N D c . n a m e = @ S o r t N a m e I F C H A R I N D E X ( ' c h a r ' , @ t y p e ) > 0 S E T @ t y p e = @ t y p e + ' ( ' + C A S T ( @ p r e c A S v a r c h a r ) + ' ) ' D E C L A R E @ s t r P a g e S i z e v a r c h a r ( 5 0 ) D E C L A R E @ s t r S t a r t R o w v a r c h a r ( 5 0 ) D E C L A R E @ s t r F i l t e r v a r c h a r ( 1 0 0 0 ) D E C L A R E @ s t r S i m p l e F i l t e r v a r c h a r ( 1 0 0 0 ) D E C L A R E @ s t r G r o u p v a r c h a r ( 1 0 0 0 ) / * D e f a u l t P a g e N u m b e r * / I F @ P a g e N u m b e r < 1 S E T @ P a g e N u m b e r = 1 / * S e t p a g i n g v a r i a b l e s . * / S E T @ s t r P a g e S i z e = C A S T ( @ P a g e S i z e A S v a r c h a r ( 5 0 ) ) S E T @ s t r S t a r t R o w = C A S T ( ( ( @ P a g e N u m b e r - 1 ) * @ P a g e S i z e + 1 ) A S v a r c h a r ( 5 0 ) ) / * S e t f i l t e r & g r o u p v a r i a b l e s . * / I F @ F i l t e r I S N O T N U L L A N D @ F i l t e r ! = ' ' B E G I N S E T @ s t r F i l t e r = ' W H E R E ' + @ F i l t e r + ' ' S E T @ s t r S i m p l e F i l t e r = ' A N D ' + @ F i l t e r + ' ' E N D E L S E B E G I N S E T @ s t r S i m p l e F i l t e r = ' ' S E T @ s t r F i l t e r = ' ' E N D I F @ G r o u p I S N O T N U L L A N D @ G r o u p ! = ' ' S E T @ s t r G r o u p = ' G R O U P B Y ' + @ G r o u p + ' ' E L S E S E T @ s t r G r o u p = ' ' / * E x e c u t e d y n a m i c q u e r y * / E X E C ( ' D E C L A R E @ S o r t C o l u m n ' + @ t y p e + ' S E T R O W C O U N T ' + @ s t r S t a r t R o w + ' S E L E C T @ S o r t C o l u m n = ' + @ s t r S o r t C o l u m n + ' F R O M ' + @ T a b l e s + @ s t r F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t + ' S E T R O W C O U N T ' + @ s t r P a g e S i z e + ' S E L E C T ' + @ F i e l d s + ' F R O M ' + @ T a b l e s + ' W H E R E ' + @ s t r S o r t C o l u m n + @ o p e r a t o r + ' @ S o r t C o l u m n ' + @ s t r S i m p l e F i l t e r + ' ' + @ s t r G r o u p + ' O R D E R B Y ' + @ S o r t + ' ' ) G O S E T Q U O T E D _ I D E N T I F I E R O F F G O S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O S E T A N S I _ N U L L S O N G O C R E A T E P R O C E D U R E P a g i n g _ S u b Q u e r y ( @ T a b l e s v a r c h a r ( 1 0 0 0 ) , @ P K v a r c h a r ( 1 0 0 ) , @ S o r t v a r c h a r ( 2 0 0 ) = N U L L , @ P a g e N u m b e r i n t = 1 , @ P a g e S i z e i n t = 1 0 , @ F i e l d s v a r c h a r ( 1 0 0 0 ) = ' * ' , @ F i l t e r v a r c h a r ( 1 0 0 0 ) = N U L L , @ G r o u p v a r c h a r ( 1 0 0 0 ) = N U L L ) A S D E C L A R E @ s t r P a g e S i z e v a r c h a r ( 5 0 ) D E C L A R E @ s t r S k i p p e d R o w s v a r c h a r ( 5 0 ) D E C L A R E @ s t r F i l t e r v a r c h a r ( 1 0 0 0 ) D E C L A R E @ s t r S i m p l e F i l t e r v a r c h a r ( 1 0 0 0 ) D E C L A R E @ s t r G r o u p v a r c h a r ( 1 0 0 0 ) / * D e f a u l t S o r t i n g * / I F @ S o r t I S N U L L O R @ S o r t = ' ' S E T @ S o r t = @ P K S E T @ S o r t = ' O R D E R B Y ' + @ S o r t + ' ' / * D e f a u l t P a g e N u m b e r * / I F @ P a g e N u m b e r < 1 S E T @ P a g e N u m b e r = 1 / * S e t p a g i n g v a r i a b l e s . * / S E T @ s t r P a g e S i z e = C O N V E R T ( v a r c h a r ( 5 0 ) , @ P a g e S i z e ) S E T @ s t r S k i p p e d R o w s = C O N V E R T ( v a r c h a r ( 5 0 ) , @ P a g e S i z e * ( @ P a g e N u m b e r - 1 ) ) / * S e t f i l t e r & g r o u p v a r i a b l e s . * / I F @ F i l t e r I S N O T N U L L A N D @ F i l t e r ! = ' ' B E G I N S E T @ s t r F i l t e r = ' W H E R E ' + @ F i l t e r + ' ' S E T @ s t r S i m p l e F i l t e r = ' A N D ' + @ F i l t e r + ' ' E N D E L S E B E G I N S E T @ s t r S i m p l e F i l t e r = ' ' S E T @ s t r F i l t e r = ' ' E N D I F @ G r o u p I S N O T N U L L A N D @ G r o u p ! = ' ' S E T @ s t r G r o u p = ' G R O U P B Y ' + @ G r o u p + ' ' E L S E S E T @ s t r G r o u p = ' ' I F @ P a g e N u m b e r = 1 - - I n t h i s c a s e w e c a n e x e c u t e a m o r e e f f i c i e n t q u e r y w i t h n o s u b q u e r i e s . E X E C ( ' S E L E C T T O P ' + @ s t r P a g e S i z e + ' ' + @ F i e l d s + ' F R O M ' + @ T a b l e s + @ s t r F i l t e r + @ s t r G r o u p + @ S o r t ) E L S E - - E x e c u t e a s t r u c t u r e o f s u b q u e r i e s t h a t b r i n g s t h e c o r r e c t p a g e . E X E C ( ' S E L E C T ' + @ F i e l d s + ' F R O M ' + @ T a b l e s + ' W H E R E ' + @ P K + ' I N ' + ' ( S E L E C T T O P ' + @ s t r P a g e S i z e + ' ' + @ P K + ' F R O M ' + @ T a b l e s + ' W H E R E ' + @ P K + ' N O T I N ( S E L E C T T O P ' + @ s t r S k i p p e d R o w s + ' ' + @ P K + ' F R O M ' + @ T a b l e s + @ s t r F i l t e r + @ s t r G r o u p + @ S o r t + ' ) ' + @ s t r S i m p l e F i l t e r + @ s t r G r o u p + @ S o r t + ' ) ' + @ s t r G r o u p + @ S o r t ) G O S E T Q U O T E D _ I D E N T I F I E R O F F G O S E T A N S I _ N U L L S O N G O ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
