从存储过程中获取每个表的10个记录 [英] Get 10 Records Each Table from Stored Procedure

问题描述

我有一张叫做注册的桌子。如果从该表中选择记录，我将根据条件获得记录。这里我需要的是假设我有25条记录，这里有10条记录，我必须保存在一张表中，第二条10条记录保存另一张表，最后剩下的5条记录保存在第三张表中。这意味着我已动态创建每个表，然后存储每个表10,10个记录。最后，我必须调用这三个表，然后我将获得数据集中的表，所以我将继续。



对不起语法错误。请帮帮我们。等待您的回复。



提前谢谢。

Hi,
I have one table called registration. If select records from that table I will get records based on condition. Here what I need is suppose I got 25 records here 10 records I have to save in one table and second 10 records save another table and finally remaining 5 records save in third table. That means each table I have create dynamically and then store 10, 10 records each table. Finally I have to call these three tables then I will get tables in dataset so I will continue further.

Sorry for grammar mistakes. Please help me guys. Awaiting your reply.

Thanks in advance.

推荐答案

你可以使用下面的sp。@ ROWCOUNT变量值被使用在asp.net cs文件中，用于在指定数据源之前指定网格的VirtualItemCount属性。指定此属性将自动生成网格中的页码



CREATE PROCEDURE [dbo]。[spGetDepartments]

@DepartmentID INT = NULL

，@ PageNo INT = 1

，@ PageSize INT = 1

，@ IsAdmin BIT = 0

AS

BEGIN

DECLARE @ROWCOUNT INT





设置NOCOUNT ON;



SELECT @ ROWCOUNT =（SELECT

COUNT（*）

来自DepartmentMaster WITH（NOLOCK）

WHERE（DepartmentID = @ DepartmentID或@DepartmentID为空）

）;



与CTE

AS

（

SELECT

ROW_NUMBER（）OVER（按部门名称订购）ROWNUMBER

，部门ID

，DepartmentName



，@ ROWCOUNT AS TotalRows

来自DepartmentMaster WITH（NOLOCK）

WHERE（DepartmentID = @ DepartmentID或@Depart mentID是空的）

）



SELECT *来自CTE

之间的ROWNUMBER（（@（PageNo *） @PageSize） - @ PageSize）+1）AND（（（@ PageNo * @ PageSize） - @ PageSize）+ @ PageSize）



END

You can use sp given below.@ROWCOUNT variable value is used in the asp.net cs file for specifying VirtualItemCount property of the grid before data source is specified. Specifying this property will automatically generate the page numbers in the grid

CREATE PROCEDURE [dbo].[spGetDepartments]
@DepartmentID INT=NULL
,@PageNo INT=1
,@PageSize INT=1
,@IsAdmin BIT=0
AS
BEGIN
DECLARE @ROWCOUNT INT


SET NOCOUNT ON;

SELECT @ROWCOUNT=( SELECT
COUNT(*)
FROM DepartmentMaster WITH(NOLOCK)
WHERE (DepartmentID=@DepartmentID OR @DepartmentID IS NULL)
);

WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY DepartmentName)ROWNUMBER
,DepartmentID
,DepartmentName

,@ROWCOUNT AS TotalRows
FROM DepartmentMaster WITH(NOLOCK)
WHERE (DepartmentID=@DepartmentID OR @DepartmentID IS NULL)
)

SELECT * FROM CTE
WHERE ROWNUMBER between (((@PageNo*@PageSize)-@PageSize)+1) AND (((@PageNo*@PageSize)-@PageSize)+@PageSize)

END

这篇关于从存储过程中获取每个表的10个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！