我的存储过程是这样的 拖拽后系统自动生成的返回值类型为int 我是刚刚接触linq 请求各位高手指点,用存储过程如何分页
create PROCEDURE [dbo].[common_GetPagingDataSet]
(
@TableName NVARCHAR(64),
@OrderBy NVARCHAR(256),
@Columns NVARCHAR(256) = '*',
@Where NVARCHAR(1024) = '',
@PageSize INT = 10,
@PageIndex INT = 1,
@RecordAmount INT = 0 OUTPUT
)
AS
DECLARE @Sql NVARCHAR(2048)
DECLARE @Filter NVARCHAR(2048)
-- Initialize -------------------------------------------------------------------------------------------
SET @Filter = '' IF (@Columns IS NULL OR LEN(@Columns) = 0) BEGIN
SET @Columns = '*'
END
----------------------------------------------------
IF (LEN(@Where) > 0) BEGIN
SET @Filter = ' WHERE ' + @Where
END
-- Get record amount ------------------------------------------------------------------------------------
SET @Sql = 'SELECT @Count = COUNT(0) FROM ' + @TableName
SET @Sql = @Sql + @Filter
EXEC SP_EXECUTESQL @Sql, N'@Count INT OUTPUT',@RecordAmount OUTPUT
IF (@RecordAmount = 0) RETURN
-- Get bound --------------------------------------------------------------------------------------------
SET @PageIndex = @PageIndex - 1
DECLARE @TopBound INT
SET @TopBound = @PageIndex * @PageSize + 1
DECLARE @BottomBound INT
SET @BottomBound = @TopBound + @PageSize - 1
-- Get data ---------------------------------------------------------------------------------------------
SET @Sql = 'SELECT * FROM (
SELECT ' + @Columns + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS [RowNumber]
FROM ' + @TableName + @Filter + '
) AS [TempTable] WHERE [RowNumber] BETWEEN ' + CAST(@TopBound AS NVARCHAR) + ' AND ' + CAST(@BottomBound AS NVARCHAR)
EXEC SP_EXECUTESQL @Sql
create PROCEDURE [dbo].[common_GetPagingDataSet]
(
@TableName NVARCHAR(64),
@OrderBy NVARCHAR(256),
@Columns NVARCHAR(256) = '*',
@Where NVARCHAR(1024) = '',
@PageSize INT = 10,
@PageIndex INT = 1,
@RecordAmount INT = 0 OUTPUT
)
AS
DECLARE @Sql NVARCHAR(2048)
DECLARE @Filter NVARCHAR(2048)
-- Initialize -------------------------------------------------------------------------------------------
SET @Filter = '' IF (@Columns IS NULL OR LEN(@Columns) = 0) BEGIN
SET @Columns = '*'
END
----------------------------------------------------
IF (LEN(@Where) > 0) BEGIN
SET @Filter = ' WHERE ' + @Where
END
-- Get record amount ------------------------------------------------------------------------------------
SET @Sql = 'SELECT @Count = COUNT(0) FROM ' + @TableName
SET @Sql = @Sql + @Filter
EXEC SP_EXECUTESQL @Sql, N'@Count INT OUTPUT',@RecordAmount OUTPUT
IF (@RecordAmount = 0) RETURN
-- Get bound --------------------------------------------------------------------------------------------
SET @PageIndex = @PageIndex - 1
DECLARE @TopBound INT
SET @TopBound = @PageIndex * @PageSize + 1
DECLARE @BottomBound INT
SET @BottomBound = @TopBound + @PageSize - 1
-- Get data ---------------------------------------------------------------------------------------------
SET @Sql = 'SELECT * FROM (
SELECT ' + @Columns + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS [RowNumber]
FROM ' + @TableName + @Filter + '
) AS [TempTable] WHERE [RowNumber] BETWEEN ' + CAST(@TopBound AS NVARCHAR) + ' AND ' + CAST(@BottomBound AS NVARCHAR)
EXEC SP_EXECUTESQL @Sql
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货