Question

我的sql存储过程是执行分页查询的 返回当前页的数据datatable 和output输出参数总条数pagecount能一条sql

CREATE PROCEDURE p_FindImages
@nikeName nvarchar(20)
,@imageType int
,@imageTitle nvarchar(150)
,@bTime varchar(30)
,@eTime varchar(30)
,@state int
,@bATime varchar(30)
,@eATime varchar(30)
,@loop bit
,@page int
,@pageSize int
,@pages int output
WITH ENCRYPTION
AS
SET NOCOUNT ON
BEGIN TRY
SELECT @pages=COUNT(*) FROM v_GWPlayerImage AS A
WHERE (@nikeName IS NULL OR A.NikeName like '%'+@nikeName+'%')
AND (@imageType IS NULL OR A.ImageType=@imageType)
AND (@imageTitle IS NULL OR A.ImageTitle like '%'+@imageTitle+'%')
AND (@bTime IS NULL OR A.UploadDate>=DBO.f_FormatFromDate(@bTime))
9 AND (@eTime IS NULL OR A.UploadDate<=DBO.f_FormatEndDate(@eTime))
AND (@state is null OR A.[Status]=@state)
AND (@bATime IS NULL OR A.AuditDate>=DBO.f_FormatFromDate(@bATime))
AND (@eATime IS NULL OR A.AuditDate<=DBO.f_FormatEndDate(@eATime))
AND (@loop IS NULL OR A.AddToLoopPlay=@loop)

SELECT * FROM (
SELECT A.*,ROW_NUMBER() over (order by A.ImageID desc) ROWNUMBER
FROM v_GWPlayerImage AS A
WHERE (@nikeName IS NULL OR A.NikeName like '%'+@nikeName+'%')
AND (@imageType IS NULL OR A.ImageType=@imageType)
AND (@imageTitle IS NULL OR A.ImageTitle like '%'+@imageTitle+'%')
AND (@bTime IS NULL OR A.UploadDate>=DBO.f_FormatFromDate(@bTime))
AND (@eTime IS NULL OR A.UploadDate<=DBO.f_FormatEndDate(@eTime))
AND (@state is null OR A.[Status]=@state)
AND (@bATime IS NULL OR A.AuditDate>=DBO.f_FormatFromDate(@bATime))
AND (@eATime IS NULL OR A.AuditDate<=DBO.f_FormatEndDate(@eATime))
AND (@loop IS NULL OR A.AddToLoopPlay=@loop)

) AS TEMP
WHERE ROWNUMBER>(@page-1)*@PageSize AND ROWNUMBER<=@page*@PageSize;
能一条sql即返回datatable 又有输出参数pagecount么 就是用一条sql了 不用存储过程了可以么求大神帮忙

Answer 1

给你个我在用的分页存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[global.pagebreak]
@pFields varchar(4000)='*',--查询的字段
@pSortField varchar(2000)='',--排序的字段
@pTable varchar(3000)='',--所查的表
@pWhere varchar(4000)='',--条件
@pOnePageRows int = 25,--每页显示的条数
@pIndex int = 1
as
declare @s varchar(5000)
if (@pWhere='' or @pWhere=NULL)
begin
set @s ='
DECLARE @totalRows int
DECLARE @totalPages int
SELECT @totalRows = COUNT(*) From '+@pTable+'
SET @totalPages = @totalRows / '+cast(@pOnePageRows as varchar)+'
IF( @totalPages * '+cast(@pOnePageRows as varchar) + '< @totalRows)
SELECT @totalPages = @totalPages + 1
SELECT '+@pFields+' FROM(
SELECT ROW_NUMBER() OVER(order by '+@pSortField+') as ROWID, '+@pFields+' From '+@pTable+')
AS TSP
WHERE ROWID BETWEEN '+cast((((@pIndex-1)*@pOnePageRows)+1) as varchar)+'
AND '+cast((@pIndex*@pOnePageRows) as varchar)+'
SELECT @totalRows AS TOTALROWS,
@totalPages AS TOTALPAGES,
'+cast(@pIndex as varchar)+' AS CURRENTPAGE,
'+cast(@pOnePageRows as varchar)+' AS ONEPAGEROWS '
end
else
begin
set @s ='
DECLARE @totalRows int
DECLARE @totalPages int
SELECT @totalRows = COUNT(*) From '+@pTable+' where '+@pWhere+'
SELECT @totalPages = @totalRows / '+cast(@pOnePageRows as varchar)+'
IF( @totalPages * '+cast(@pOnePageRows as varchar) + '< @totalRows)
SELECT @totalPages = @totalPages + 1
SELECT '+@pFields+' FROM(
SELECT ROW_NUMBER() OVER(order by '+@pSortField+') as ROWID, '+@pFields+' From '+@pTable+' where '+@pWhere+')
AS TSP
WHERE ROWID BETWEEN '+cast((((@pIndex-1)*@pOnePageRows)+1) as varchar)+'
AND '+cast((@pIndex*@pOnePageRows) as varchar)+'
SELECT @totalRows AS TOTALROWS,
@totalPages AS TOTALPAGES,
'+cast(@pIndex as varchar)+' AS CURRENTPAGE,
'+cast(@pOnePageRows as varchar)+' AS ONEPAGEROWS '
end
print @s
exec(@s)

Answer 2

你这个存储过程已经够烂了，还要用一句来实现，你这个思路反了，既麻烦又没效率。

追问

谢谢 我只是想问 一条sql能实现上面我的需求么 即返回数据集 又能获取到输出参数

追答

你这样的逻辑，神仙也迷路了，肯定实现不了了。

追问

谢了 实在是我也觉得不可能才找人帮忙的 我本来就是写的存储过程 但是上面非得要求 不用存储过程 只用一条sql实现 我不会啦 才来问么 谢了 采纳了

追答

晕，你遇到古董主管了，asp出来的母师傅吧，sql写的看起来很玄妙，就是网站效率太低了，太垃圾了，没人用了。
现在用存储过程，尽可能的要用短语句，尽可能的少关联条件，像你这个存储过程，起码要分解成10几条语句才能及格。