如题,例如sql语句为
sql="SELECT DISTINCT WFBL.Program AS [Program], WFPR.PRG_DES AS [Program Description], WFBL.DOC_CODE AS [Doc Code], WFBL.WF_CODE AS [Work Flow Code], WFBL.WF_STEP_Previous AS [Previous Step], WFBL.WF_STEP AS [Work Step], WFBL.wf_step_next AS [Next Step], WFBL.STATUS AS [Status], WFBL.REMARK AS [Re], WFBL.WF_CONTENT AS [Content], WFBL.Commit_User AS [Commit User], HM.USERNAME AS [Commit User Name], HM.UserTel AS [User Tel], WFBL.Commit_Date AS [Commit Date], DATEDIFF(Hour, WFBL.Approve_Time,GETDATE()) AS [Residence Time(Hour)] FROM WFBILLM WFBL(NOLOCK) LEFT JOIN WFPRGFLM WFPR(nolock) ON WFBL.Program=WFPR.PRG_CODE LEFT JOIN OPPHANM HM(nolock) ON WFBL.commit_user=HM.USERCODE INNER JOIN DBO.fun_WF_TODO_TASK('xfsds') TODO ON WFBL.DOC_CODE = TODO.DOC_CODE AND WFBL.PROGRAM = TODO.PROGRAM AND (1=1) And (1=1) And (1=1) And (1=1) ORDER BY WFBL.Program"
页码为第10页,每页20条记录,最后我想返回json格式的记录集.还有sql语句是任意的,可以包含order by字句
请问能不能实现啊?
sql="SELECT DISTINCT WFBL.Program AS [Program], WFPR.PRG_DES AS [Program Description], WFBL.DOC_CODE AS [Doc Code], WFBL.WF_CODE AS [Work Flow Code], WFBL.WF_STEP_Previous AS [Previous Step], WFBL.WF_STEP AS [Work Step], WFBL.wf_step_next AS [Next Step], WFBL.STATUS AS [Status], WFBL.REMARK AS [Re], WFBL.WF_CONTENT AS [Content], WFBL.Commit_User AS [Commit User], HM.USERNAME AS [Commit User Name], HM.UserTel AS [User Tel], WFBL.Commit_Date AS [Commit Date], DATEDIFF(Hour, WFBL.Approve_Time,GETDATE()) AS [Residence Time(Hour)] FROM WFBILLM WFBL(NOLOCK) LEFT JOIN WFPRGFLM WFPR(nolock) ON WFBL.Program=WFPR.PRG_CODE LEFT JOIN OPPHANM HM(nolock) ON WFBL.commit_user=HM.USERCODE INNER JOIN DBO.fun_WF_TODO_TASK('xfsds') TODO ON WFBL.DOC_CODE = TODO.DOC_CODE AND WFBL.PROGRAM = TODO.PROGRAM AND (1=1) And (1=1) And (1=1) And (1=1) ORDER BY WFBL.Program"
页码为第10页,每页20条记录,最后我想返回json格式的记录集.还有sql语句是任意的,可以包含order by字句
请问能不能实现啊?
CREATE PROCEDURE [dbo].[P_Sys_PagesList]
@strSQL nvarchar(4000)=NULL,--查询SQL语句
@OrderBy nvarchar(100)=NULL,--排序字段
@OrderByStatus varchar(20),-- desc asc
@PageIndex int ,--当前页码
@PageSize int--每页多少条AS
BEGIN
DECLARE
@beginnumstr VARCHAR(10),
@endnumstr VARCHAR(10)
set @beginnumstr=Convert(varchar(10),(@PageIndex-1)*@PageSize+1);
set @endnumstr= Convert(varchar(10),@PageIndex*@PageSize);
if @OrderBy=''
begin
set @OrderBy='(select 0)'
end;
exec(
'with list as(
SELECT ROW_NUMBER() OVER(order by '+@OrderBy+' '+@OrderByStatus+') AS num,*
FROM ('+@strSQL+') T
)
select (select COUNT(1) from list) as Count,* from list
where num between '+@beginnumstr+' and '+@endnumstr
)
END
这是sql语句
转换json可以把返回的table用Newtonsoft.Json转化下
或者转xml sql 有个for xml的转换
因为数据库转换,需要使用字符拼接操作,效率太低.如果实在需要在数据库端出json,那你在数据库上集成clr函数(其实还是程序)
用clr函数处理输出json