成熟sql 分页存储过程(UP_General_SelectByWhere)

View Code

 1 GO
 2 /****** Object:  StoredProcedure [dbo].[UP_General_SelectByWhere]    Script Date: 03/17/2011 10:49:23 ******/
 3 SET ANSI_NULLS ON
 4 GO
 5 SET QUOTED_IDENTIFIER ON
 6 GO
 7 -- =============================================
 8 -- Author:        <Author,,Name>
 9 -- Create date: <Create Date,,>
10 -- Description:    <Description,,>
11 -- =============================================
12 ALTER PROCEDURE [dbo].[UP_General_SelectByWhere]    
13     (
14     @strSelect NVarChar (1000),
15     @strWhere NVarChar (4000),
16     @strTable NVarChar (50),
17     @strOrderfld NVarChar (100),
18     @OrderType bit = 0
19     )
20 AS
21     BEGIN
22     DECLARE @strSQL varchar(8000) -- 主语句 
23     DECLARE @strOrder varchar(400) -- 排序类型         
24             IF(@strOrderfld!='')
25                 BEGIN
26                     if @OrderType!= 0 -- 降序(desc) 
27                         BEGIN 
28                         set @strOrder = '  order by  ' + @strOrderfld +'   desc ' --如果@OrderType不是0，就执行降序，这句很重要!                     
29                         END 
30                     ELSE -- 升序(asc) 
31                     BEGIN 
32                         set @strOrder = ' order by  ' + @strOrderfld +'   asc ' 
33                     END 
34                    SET @strSQL=  ' select  '+@strSelect+'  from '+ @strTable+'     where 1=1  ' + @strWhere+@strOrder
35                 END
36             ELSE
37             BEGIN
38                    SET @strSQL=  ' select  '+@strSelect+'  from  '+ @strTable+'     where   1=1   ' + @strWhere
39             END
40         EXECUTE(@strSQL)
41     END

转载于:https://www.cnblogs.com/yeminglong/archive/2011/10/15/2212921.html