Get 10 Records Each Table from Stored Procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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.

Posted 18-Sep-13 20:55pm

Aravind Garre

Add a Solution

Comments

Love 2 code 19-Sep-13 3:51am

Maybe you want to try >paging< - this technology gives you the data in defined blocks.

Aravind Garre 19-Sep-13 4:01am

Dear, i am implementing in listview is it possible paging in list View in Windows Application. Thanks

Love 2 code 19-Sep-13 4:22am

Hi, sure, there is an example with a DataGridView at cp:
http://www.codeproject.com/Tips/581869/Paging-DataGridView-in-Csharp-using-LINQ
hope that helps.

Faisalabadians 19-Sep-13 5:40am

better way is to implement paging on server side. use row_number function "http://technet.microsoft.com/en-us/library/ms186734.aspx"

here is a little example

DECLARE @RNumber int
SET @RNumber = 5;
WITH customers AS
(
SELECT ROW_NUMBER() OVER (ORDER BY customerID) AS RNumber,
FirstName

FROM Customer
)

SELECT * FROM customers
WHERE RNumber > @RNumber

also, paging can be implement by skip and take records. for example if you want to get records where row number is between 5- 10

DECLARE @Skip int
DECLARE @Take int
SET @Skip = 5 -- leave first five recrods
SET @Take =10; -- take next 10 records

WITH customers AS
(
SELECT ROW_NUMBER() OVER (ORDER BY customerID) AS RNumber,
FirstName

FROM Customer
)

SELECT * FROM customers
WHERE ((RNumber > @Skip) AND (RNumber <= @Take))

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Madhu Nair · Answer 1 · 2013-09-19T21:05:00

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