注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Computer Science

I Pressed My Words Here. Will You Read ?

 
 
 

日志

 
 

MS SQL Server 关于后台分页的函数  

2011-08-22 09:04:48|  分类: My Projects |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
函数f_getSQLStr_Get_Rows_From_Table_Between_And: 返回的是分页查询SQL语句字符串。用Exec()执行即可。
USE [databaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[f_getSQLStr_Get_Rows_From_Table_Between_And]    Script Date: 08/30/2011 17:03:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_getSQLStr_Get_Rows_From_Table_Between_And]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[f_getSQLStr_Get_Rows_From_Table_Between_And]
GO

USE [databaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[f_getSQLStr_Get_Rows_From_Table_Between_And]    Script Date: 08/30/2011 17:03:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:        Will
-- Create date: 08302011
-- Description:    Create SQL string for parting table
-- =============================================
CREATE FUNCTION [dbo].[f_getSQLStr_Get_Rows_From_Table_Between_And](
       @tableName NVARCHAR(MAX)
       ,@beginNum NVARCHAR(MAX)
       ,@endNum NVARCHAR(MAX)
       ,@idColName NVARCHAR(MAX)= NULL
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @tmpTableName NVARCHAR(50) = '#TEMP$TABLE'+REPLACE(SUBSTRING(CONVERT(NVARCHAR(MAX),SYSDATETIME()),18,10),'.','')--以#开头的局部临时表,接部分时间值
        ,@tmpIDColName NVARCHAR(50) = 'TEMP$ID'
        ,@str NVARCHAR(max)     

    SET @idColName = UPPER(LTRIM(RTRIM(@idColName)))
    SET @tableName = UPPER(LTRIM(RTRIM(@tableName)))
      /**当索引列未指定时,自动查找可能作为索引列的列。**/
    IF @idColName IS NULL OR @idColName = 'NULL' OR @idColName = ''
    BEGIN
        SET @idColName = (SELECT TOP 1 COLUMN_NAME FROM gxtg.INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = @tableName OR UPPER(TABLE_SCHEMA) +'.'+ TABLE_NAME = @tableName OR TABLE_CATALOG + '.' + UPPER(TABLE_SCHEMA) + '.' + TABLE_NAME = @tableName) AND LOWER(IS_NULLABLE) = 'no')
        IF @idColName IS NULL
            SET @idColName = (SELECT TOP 1 COLUMN_NAME FROM gxtg.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName OR UPPER(TABLE_SCHEMA) +'.'+ TABLE_NAME = @tableName OR TABLE_CATALOG + '.' + UPPER(TABLE_SCHEMA) + '.' + TABLE_NAME = @tableName)
    END
    /*如果索引列已经被指定*/
    IF @idColName IS NOT NULL AND @idColName <> ''
    BEGIN
        SET @str = N'SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(ORDER BY ' + @idColName + ') AS ''row_num''
            FROM ' + ISNULL(@tableName,'') + ' AS tmpa ) AS tmpb
                WHERE row_num BETWEEN '+ @beginNum +' AND '+ @endNum +''
    END
    ELSE
    BEGIN
        SET @str = N'
            BEGIN TRANSACTION
            BEGIN TRY
                IF  EXISTS (SELECT * FROM tempdb..sysobjects WHERE ID = OBJECT_ID(N''tempdb..' + @tmpTableName + '''))
                    DROP TABLE ' + @tmpTableName + '
                SELECT ' + @tmpIDColName + '=IDENTITY(numeric(38,0),1,1),* INTO ' + @tmpTableName + ' FROM '+ @tableName +' a
                SELECT * FROM ' + @tmpTableName + '
                    WHERE ' + @tmpIDColName + ' BETWEEN '+ @beginNum + ' AND ' + @endNum + '
                DROP TABLE ' + @tmpTableName + '
                IF @@TRANCOUNT > 0
                    COMMIT TRANSACTION
            END TRY
            BEGIN CATCH
                IF @@TRANCOUNT > 0
                    ROLLBACK TRANSACTION
            END CATCH
        '
    END
    RETURN @str
END

GO


调用方式:f_getSQLStr_Get_Rows_From_Table_Between_And ( <'tableName'>, <'beginIndex'>, <'endIndex'>, <'idColName'|DEFAULT>)
注:如果db.INFORMATION_SCHEMA.COLUMNS这个视图在数据库中不存在,那么就需要基于sys.object或sys.tables和 sys.columns试图来实现。
  评论这张
 
阅读(670)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017