sql server 分页生成json字符串

最新推荐文章于 2024-03-12 17:44:05 发布

liuzhenyu_

最新推荐文章于 2024-03-12 17:44:05 发布

阅读量416

点赞数

文章标签： sql server 分页 json

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[proc_paged_with_Midvar]    Script Date: 12/17/2015 09:14:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[proc_paged_with_Midvar]  --利用ID>最大ID值和中间变量    
 
(    
 
@pageIndex int,
 
@pageSize int
)
 
as   
 
declare @count int   
 
declare @ID int   
 
declare @timediff datetime    
 
declare @ParameterSQL nvarchar(500)
declare @testTable nvarchar(500)
 
begin    
 
set nocount on;    

select @count=0,@ID=0,@timediff=getdate()    
 
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then testID else @ID end from testTable order by testId    
 
set @ParameterSQL='select top '+str(@pageSize)+' * from testTable where testID>'+str(@ID)    
 --return @sql
 --print @sql
 --execute(@sql)    
 --select @sql
--select datediff(ms,@timediff,getdate()) as 耗时
 
set nocount off;    
 
end 
BEGIN
  
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString)'
SET @SQL = @SQL + 'AS('
SET @SQL = @SQL + @ParameterSQL+ 'FOR XML RAW,TYPE,ELEMENTS'
SET @SQL = @SQL + ')'
SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString=CAST(@XML AS VARCHAR(MAX))
  
DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
  
DECLARE @StartRoot VARCHAR(100);SET @StartRoot='<row>'
DECLARE @EndRoot VARCHAR(100);SET @EndRoot='</row>'
DECLARE @StartField VARCHAR(100);SET @StartField='<'
DECLARE @EndField VARCHAR(100);SET @EndField='>'
  
SET @RowStart=CharIndex(@StartRoot,@XMLString,0)
SET @JSON=''
WHILE @RowStart>0
BEGIN
    SET @RowStart=@RowStart+Len(@StartRoot)
    SET @RowEnd=CharIndex(@EndRoot,@XMLString,@RowStart)
    SET @Row=SubString(@XMLString,@RowStart,@RowEnd-@RowStart)
    SET @JSON=@JSON+'{'
  
    -- for each row
    SET @FieldStart=CharIndex(@StartField,@Row,0)
    WHILE @FieldStart>0
    BEGIN
        -- parse node key
        SET @FieldStart=@FieldStart+Len(@StartField)
        SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
        SET @KEY=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
        SET @JSON=@JSON+'"'+@KEY+'":'
        -- parse node value
        SET @FieldStart=@FieldEnd+1
        SET @FieldEnd=CharIndex('</',@Row,@FieldStart)
        SET @Value=SubString(@Row,@FieldStart,@FieldEnd-@FieldStart)
        SET @JSON=@JSON+'"'+@Value+'",'
  
        SET @FieldStart=@FieldStart+Len(@StartField)
        SET @FieldEnd=CharIndex(@EndField,@Row,@FieldStart)
        SET @FieldStart=CharIndex(@StartField,@Row,@FieldEnd)
    END    
    IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
    SET @JSON=@JSON+'},'
    --/ for each row
  
    SET @RowStart=CharIndex(@StartRoot,@XMLString,@RowEnd)
END
IF LEN(@JSON)>0SET @JSON=SubString(@JSON,0,LEN(@JSON))
SET @JSON='['+@JSON+']'
SELECT @JSON
end
/**
使用说明：
	使用事例：exec [proc_paged_with_Midvar] 1,1
	可能需要修改一下存储过程进行表的适配
**/

总体上来说是使用动态构造sql语句和 SerializeJSON 存储过程结合了一下，用以备忘