Paging Performance
Feb 21, 2003
I have a paging dilema with an ADO/ASP web based application.
Currently I am using the temp table and inserted index method of paging which works well but the pages that use this paging have a variety of filters on them and a largish subset of data available. This means that every time the page is refreshed the code is creating that temporary table, inserting all the data, selecting the subset and then dropping it.
I was looking for a more efficent way of getting paged data to the client. One of the alternatives I came across was using a server side forward only cursor and the ado getrows() method. This sounds good in princible but I don't know if I am going to get a performance hit by using a server side cursor as opposed to sending the entire recorset to the client and letting it page the results.
Would it be any better to use a stored procedure and pass the full sql statement to it. I can't actually write the sql into the stored procedure becuase it is all totally dynamic.
So I guess I have three options, temp tables, server side cursor and small amounts of data sent to the client or client side cursor and large amounts of data sent to the client.
Any ideas or recomendations?
View 1 Replies
ADVERTISEMENT
Nov 28, 2006
Hello, I have incorporated a paging query in my software. I got the query from here:
http://rosca.net/writing/articles/serverside_paging.asp
My web software ususlly responded in .005 - .02 seconds with about a 100 rows of data. When I put simulated data on my database I added about 2 million rows. when I did this -- every page that did not execute the paging query responded lightning fast. But the webpages that executed the paging query took over 5 seconds. I dont understand why this paging query brought my web application to its knees.
Does anyone know of a more efficient way to do paging. I have SQL server 2000. If it may be easier I can upgrade to SQL 2005. PLZ Let me know. Thanks
View 1 Replies
View Related
Oct 12, 2004
Is there any way to implement a paging scheme such that only the required records are transferred from SQL Server to the asp.net app?
The only support I can find such as the DataAdaptor.Fill will bring all the records back from SQL Server and then create the page...
This obviously still takes time and memory based on the entire query, not the page size.
Any ideas?
View 2 Replies
View Related
Jul 26, 2005
I have a table with a lot of records. I want to make paging without passing all the data back to a dataset. I know how to select the top n rows but how do I select 10-20 for example.
View 3 Replies
View Related
Dec 9, 2005
Hello,
How can I do paging in my SQL Server stored procedure.
I have thought of a way, but I need to :
"SELECT TOP @Count..."
which is not allowed :S
What can I do to get around this?
View 1 Replies
View Related
Dec 11, 2003
I have noticed that the server i'm running SQL2k on is starting to page out of the norm. I can see that the regsvc and sqlservr svc are showing high page faults/sec. I have 3 gigs of ram and set the max that sql can use to 2 gigs. It is currently using only 168 MB and still will show high paging at random times. I know I can add more ram but that doesn't seem to be the problem. I have also stopped unnecessary services at the os level.
Any other suggestions to fix this?
Thanks in advance.
View 6 Replies
View Related
May 9, 2008
Hi there
I've managed to make it query that return a dataset that have 2 views utilising the "Filter" in RS. I treat this as a single record with multiple views.
Now let say if I have a stored precedure that pass 2 parameters one is called state and year and accepting 'ALL' for every possibility of State and Year and construct that into single dataset with 2 views similar like above.
How do I breakdown this in the reporting services so it will have paging?
This is a simple dataset:
RECORDID, ReportViewType, State, Year, VALUE
1, "VIEW1", "NSW", 1, null
1, "VIEW2", null, null, 10000
2, "VIEW1", "NSW", 2, null
2, "VIEW2", null, null, 11000
3, "VIEW1", "VIC", 1, null
3, "VIEW2", null, null, 11003
4, "VIEW1", "VIC", 2, null
4, "VIEW2", null, null, 11001
I would like to break down (paging) this per recordid. Each page obviosuly has 2 views using the same data set with different FILTER.
Do I need to put into a LIST then inside that list put 2 TABLES? Is this possible?!?!
Thanks
View 5 Replies
View Related
Aug 2, 2006
I have created a stored proc for paging on a datalist which uses a objectDataSource.
I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.
CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]( @categoryID int, @pageIndex INT, @numRows INT, @itemCount INT OUTPUT )AS
SELECT @itemCount= COUNT(*) FROM CMRC_Products where CMRC_Products.CategoryID=@categoryID Declare @startRowIndex INT; Declare @finishRowIndex INT; set @startRowIndex = ((@pageIndex -1) * @numRows) + 1; set @finishRowIndex = @pageIndex * @numRows
DECLARE @tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)
INSERT INTO @tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage, CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weightFROM CMRC_Products INNER JOIN CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryIDWHERE (CMRC_Products.CategoryID = @categoryID) AND (CMRC_Products.isActive = 1)
SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weightFROM @tCat WHERE TID >= @startRowIndex AND TID <= @finishRowIndexGO
View 12 Replies
View Related
Dec 13, 2006
I am using a stored procedure to page some objectsThe procedure looks like this:
CREATE PROCEDURE sw20aut.sw20_Kon( @sid_nr INT, @sid_stl INT = 35, @kid int )AS BEGIN SET NOCOUNT ON DECLARE @rader INT, @sid_antal INT, @ubound int, @lbound int SELECT @rader = COUNT(*), @sid_antal = COUNT(*) / @sid_stl FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0'
IF @rader % @sid_stl != 0 SET @sid_antal = @sid_antal + 1 IF @sid_nr < 1 SET @sid_nr = 1 IF @sid_nr > @sid_antal SET @sid_nr = @sid_antal SET @ubound = @sid_stl * @sid_nr IF(@sid_antal > 0) SET @lbound = @ubound - (@sid_stl - 1) ELSE SET @lbound = 0 SELECT CurrentPage = @sid_nr, TotalPages = @sid_antal, TotalRows = @rader
DECLARE @ename VARCHAR(64), @fname VARCHAR(64), @konid VARCHAR(64) SET ROWCOUNT @lbound SELECT @ename = enamn, @fname = fnamn, @konid = kon_id FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE kund_id = @kid AND del = '0' ORDER BY enamn, fnamn, kon_id SET ROWCOUNT @sid_stl SELECT kon_id, enamn, fnamn FROM sw20aut.sw_kontakter WITH (NOLOCK) WHERE enamn + fnamn + '~' + CAST(kon_id as VARCHAR(64)) >= @ename + @fname + '~' + @konid AND (kund_id = @kid AND del = '0') ORDER BY enamn, fnamn, kon_id SELECT startid = @konid SET ROWCOUNT 0END
The big problem is that i need to display objet with the same name. In my book the best identifier is the PK and therefor i have sorted as above by ordering after LastName, FirstName, ContactId
After som thinking ive reached the conclusion that this dont work if the idnumbers isnt of the same length. as long as they are(for example two people named John Smith, one with id = '23' and one with id = '87' it works. If there ids would have been '23' and '1203' it will not work correctly) of the same length it works fine.
What im wondering is if anyone have a good solution to this? Only thing i can think of is filling all idnumbers with zeros to equal length. Dont know how and if this will affect performance though. Anyone has a practical solution to this?
View 1 Replies
View Related
Aug 8, 2007
Questoin
I am using Sql Server 2000.
I have a table named Cities which has more than 2600000 records.
I have to display the records for a specific city page wise.
I don't want to compromise with performance.
Can anyone has the idea?
Waiting for your fruitful response.
Happy Day And Night For All
Muhammad Zeeshanuddin Khan
View 1 Replies
View Related
Feb 10, 2008
Hello To make pagination I would like to retrieve only the record from x to y ...I couldn't find how to do to in sql , I was thinking so if there is a way to do it with a sqldatasourceI make my request , put it in a sqldatasource and bind it to my datalistis there a way to "filter the sqldatasource ?" to make what I need ? Thx in advance ?
View 4 Replies
View Related
May 23, 2005
Im in the process of trying to teach myself SqlServer, comming from Oracle. How the heck do I get the equivlent of %ROWNUM pseudo-column in SqlServer? Top just isn't doing it for me.
Oracle Example wrote:
Select * from foo where foo%ROWNUM > 10 and foo%ROWNUM <20;
View 12 Replies
View Related
Jan 5, 2007
Is this a correct statement? When commit Charge total (k) is greater than Physical Memory total (k) then the server is paging badly, correct?
thanks.
View 3 Replies
View Related
Apr 25, 2008
Hi,
My application runs this query using a stored proc
SELECT empid1,name1,joindate from emp where empid2=3
union
select empid2,name2,joindate from emp where id1=3
Now I want to implement paging for the same using Row_Number so that I can display the results in pages.
Can someone please help me write a query for the same. I tried playing with Row_Number but no luck with it.Basically I am not good with SQL and I had programatically implemented paging in asp.net by looping through all records returned by the query.
Thanks,
Ganesh
View 4 Replies
View Related
Mar 14, 2006
Hello, I have a datagrid with paging allowed, but when i click on page number 2 or 3, it still displays the records from the first page. How do I solve this?
Thank you.
View 3 Replies
View Related
Sep 28, 2007
hello, what are the strategies when designing tables that needspaging?in the past i used to useselect top 200 * from tablewhere id not in (select top 100 id from table)with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?or any other advice?thanks
View 4 Replies
View Related
Aug 23, 2007
Hiii all
SQL Server 2000 or 2005 dose not support the LIMIT statement like mySQL. So plz can anyone tell me tht how to do paging in SQL Server?? Without using CLR Integration...
View 3 Replies
View Related
Mar 14, 2007
Since Row_Number() is not available to SQL Server 2005 CE, are there any other alternatives for paging when querying the database?
Thanks.
View 4 Replies
View Related
Apr 13, 2008
Hi guys
I know this topic has been gone overed a bit but it just seems that no one has a really good answer.
What i need it to be able to be able to pass in which index row i want to go from and to, as weel a a token which corresponds to how it should be sorted.
The problem with the methods that i have seen to do this is that they all use a case statement to handle the sorting like the below;
Code Snippet
;WITH TotalSales AS (
SELECT CASE @OrderBy
WHEN 'UnitPrice' THEN ROW_NUMBER() OVER (ORDER BY UnitPrice)
WHEN 'OrderQty' THEN ROW_NUMBER() OVER (ORDER BY OrderQty)
WHEN 'CarrierTrackingNumber' THEN (ROW_NUMBER() OVER (ORDER BY CarrierTrackingNumber))
END AS RowNumber,
CarrierTrackingNumber,
UnitPrice,
OrderQty
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber LIKE '%F%'
)
SELECT *
FROM TotalSales
WHERE RowNumber between @StartIndex and @StartIndex + 9
At the begging this looks really good but it turns out that this is really slow. In fact it is about twice as slow as using the below dynamic SQL:
Code Snippet
SET @SafeOrderBy = CASE @OrderBy
WHEN 'UnitPrice' THEN 'UnitPrice'
WHEN 'OrderQty' THEN 'OrderQty'
WHEN 'CarrierTrackingNumber' THEN 'CarrierTrackingNumber'
END
SET @temp = N'
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ' + @SafeOrderBy + ') AS RowNumber,
CarrierTrackingNumber,
UnitPrice,
OrderQty
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNumber LIKE ''%F%''
) SUB
WHERE SUB.RowNumber between ' + @StartIndexAlt + ' and ' + @StartIndexAlt + ' + 9'
EXEC sp_executesql @temp
Now for a whole heap of reasons I would like to avoid using dynamic SQL to do this but if the alternative means that my queries take twice as long i dont see i have much of a choice.
Does anyone have any ideas??
Thanks
Anthony
View 6 Replies
View Related
Aug 17, 2007
Hi
I am using SqlCeResult and i want to give paging on that and read in help that you can do paging on ResultSet so . I need code example so that i can understand how to implement this in resultSet.
Thanks & Regards
Vishal
View 4 Replies
View Related
Oct 27, 2006
My report is taking long time to display hundered of records.So we want to display 15 records per page and Next page link at the end of report.Please help me how to do this.
View 7 Replies
View Related
Apr 29, 2006
let's suppose that we have a table entitled "tab1" which has more than 1000 rows and about 10 columns
so in SQL 2000, if I do this query:
SELECT * FROM tab1
the result will be displaying all the rows from the begining.
and my teacher told me that there's a new option in SQL 2005 which is you can display the result of the query in a page mode.
so can anyone tell me how can I do so for this query:
SELECT * FROM tab1
so that I can see the results in pages.
thanks
View 7 Replies
View Related
Jan 20, 2008
Hi guys
I am wondering if anyone has any practical ways of sorting and paging within SQL server 2005. I have seen plenty of different example and there seems to numerous ways of doing it but i was wondering if anyone has a method that they have been using that they know works in enterprise level solutions and will work in the majority of cases as a standard. The method that has stood out thus far is Common table expressions but I am not sure if this is the best or optimal approach and whether it will work in with dynamic sorting.
Thanks
Anthony
View 6 Replies
View Related
Mar 14, 2008
How can i do paging in Reporting Services 2005.
Regards.
View 4 Replies
View Related
Oct 21, 2006
Hi, I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:<Error messages> These are on the first SELECT Line..Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.CODE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "mort.NAME.CODE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.LAST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.FIRST_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "NAMETYPE.TYPE" could not be bound.Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17The multi-part identifier "FUNERAL.NUMBER" could not be bound. </Error Messages><Sotred Procedure> CREATE PROCEDURE proc_NAMEGetPaged @startRowIndex int, @maximumRows intASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER FROM (SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER, ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum FROM Name n) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1ENDGO </Stored Procedure> Any assistance in resolving this would be greatly appreciated.. Regards..Peter.
View 1 Replies
View Related
Feb 3, 2007
Hello,
I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.
I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?
Any idea?
Thanks,
-Derek
View 3 Replies
View Related
Feb 14, 2007
Hello all,
I am having an intermitten paging problem and can't figure out why. It doesn't happen each time but quite often. I am using the default allow paging text box filing the data set with a simple query. And when you start at 1 and work your way up through the pages, it will throw this error page on the screen and will not go further. Usually happens in the mid 20'2 to 30's page. Any Ideas?
The resource cannot be found.
Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly. Requested URL: /CustomError.aspx
Once this happens you can go backward page numbers but you can not go forwrad any more.
This is the code to populate the dataset<asp:GridView ID="GridView1" runat="server"
DataSourceID="ObjectDataSource1"
AllowPaging="True"
PageSize="5"
AutoGenerateColumns="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
Imageurl='<%# "~/photos/" & Eval("[MLS Number]") & "_0.jpg" %>'
AlternateText="Main Photo"
CssClass="photo-float-left photo-border" Height="125px" Width="125px" />
<asp:Label ID="Label1" runat="server" Text='<%# Eval("[MLS Number]") %>'></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("[Street Number]") %>'></asp:Label>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("Direction") %>' Width="16px"></asp:Label>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("Address") %>'></asp:Label>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("[List Price]", "{0:C}") %>'></asp:Label>
<li>BR:
<asp:Label ID="Label6" runat="server" Text='<%# Eval("Bedrooms") %>'></asp:Label>
/ Full Baths: <asp:Label ID="Label7" runat="server" Text='<%# Eval("[Full Baths]") %>'></asp:Label>
/ Half Bath:
<asp:Label ID="Label8" runat="server" Text='<%# Eval("[Half Baths]") %>'></asp:Label></li>
<asp:TextBox ID="TextBox1" runat="server" Height="80px" Text='<%# Bind("[Public Remarks]") %>'
Width="344px" Rows="3" TextMode="MultiLine" Font-Size="X-Small"></asp:TextBox>
<hr />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Thanks,
Tony
View 1 Replies
View Related
Jun 19, 2007
I'm trying to effecinty page through many rows of data with the gridview and objectdatasource. I'm having trouble. I'm using a table adapter with predefined counting and select methods. I have tested all the methods and they all work properly. But when I configure the object datasource to use the table adapter, and set the gridviews datasrouce, the page doesn't load and I wind up getting "time out". Any help? <asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="objTopics"> <Columns> <asp:BoundField DataField="topic_title" /> </Columns> <EmptyDataTemplate> <p>NOTHING HERE</p> </EmptyDataTemplate> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" OldValuesParameterFormatString="original_{0}" SelectMethod="GetTopics" SelectCountMethod="GetTopicsRowCount" TypeName="TopicsTableAdapters.discussions_GetTopicsSubSetTableAdapter"> <SelectParameters> <asp:Parameter DefaultValue="1" Name="startRowIndex" Type="Int32" /> <asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" /> <asp:Parameter DefaultValue="1" Name="board_id" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource>
View 1 Replies
View Related
Jun 21, 2007
I have a problem with efficiently paging with gridview and objectdatasoruce. I have GetPosts1(startRowIndex, maximumRow, topic_id) and GetPostsCount(topic_id). I tested each procedure and each are working correctly. The problem is with the controls. Here is the code for the controls. <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames DataSourceID="ObjectDataSource2"> <Columns> <asp:BoundField DataField="RowNumber" HeaderText="RowNumber" SortExpression="RowNumber" /> <asp:BoundField DataField="post_id" HeaderText="post_id" SortExpression="post_id" /> <asp:BoundField DataField="post_subject" HeaderText="post_subject" SortExpression="post_subject" /> <asp:BoundField DataField="post_text" HeaderText="post_text" SortExpression="post_text" /> <asp:BoundField DataField="post_time" HeaderText="post_time" SortExpression="post_time" /> <asp:BoundField DataField="topic_id" HeaderText="topic_id" SortExpression="topic_id" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> <asp:BoundField DataField="UserID" HeaderText="UserID" SortExpression="UserID" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource2" runat="server" OldValuesParameterFormatString="original_{0}" EnablePaging="True" SelectMethod="GetPosts1" SelectCountMethod="GetPostsCount" TypeName="PostsTableAdapters.discussions_GetPostsTableAdapter"> <SelectParameters> <asp:QueryStringParameter DefaultValue="48" Name="topic_id" QueryStringField="t" Type="Int32" /> </SelectParameters> </asp:ObjectDataSource> When I run the page, I get "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll" and then "The thread '<No Name>' (0xbe0) has exited with code 0 (0x0)." Could the problem be with null or empty values in the returned data?
View 1 Replies
View Related
Aug 29, 2007
However it is not saving in visual srudio 2005. it is saying 'ambiguous column name ID' does anyone know why?CREATE PROCEDURE PagedResults_New
(@startRowIndex int,
@maximumRows int
)
AS
--Create a table variable
DECLARE @TempItems TABLE
(ID int IDENTITY,
ShortListId int
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO @TempItems (ShortListId)
SELECT Id
FROM Shortlist SWHERE Publish = 'True' order by date DESC
-- Now, return the set of paged records
SELECT S.*FROM @TempItems t
INNER JOIN ShortList S ON
t.ShortListId = S.Id
WHERE ID BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
View 3 Replies
View Related
Oct 6, 2007
Last night, I have this idea, but can not know if it can have a good performance and efficiency when the @CurrentPage is big.-------------------------------------------------------------------DATABASE:AdventureWorks DECLARE @CurrentPage intDECLARE @PageSize intDECLARE @OrderExpression nvarchar(100)DECLARE @Sql nvarchar(500)SET @CurrentPage = 1SET @PageSize = 10SET @OrderExpression = N' employeeid 'DECLARE @BigTop intDECLARE @SmallTop intSET @BigTop = @CurrentPage * @PageSizeSET @SmallTop = (@CurrentPage -1) * @PageSizeDECLARE @StartTime datetimeSET @StartTime = GETDATE()SET @Sql = N' SELECT TOP (' + CAST(@BigTop AS nvarchar(10)) + ') * FROM humanresources.Employee '+ ' EXCEPT '+ ' SELECT TOP (' + CAST(@SmallTop AS nvarchar(10)) + ') * FROM humanresources.Employee ORDER BY ' + @OrderExpressionEXEC sp_executesql @SqlDECLARE @EndTime datetimeSET @EndTime = GETDATE()SELECT DATEPART(s,@EndTime-@StartTime)SELECT DATEPART(ms,@EndTime-@StartTime)GO-----------------------------------------------------------------
View 6 Replies
View Related
Jan 17, 2005
I need to be able to specify which column to sort by, BUT SQL 2000 does not allow me to
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND
Job_no = @Job_no
ORDER BY @WHICHCOLUMN asc
You can see that @WHICHCOLUMN is can be Surname, Age ETC, I have tried to make it a variable but, it started complaining of @FIRSTREC not defined, what's going on pls help, However, how do you combine dynamic queries with parameters as the say
"Sql server does not accept variables as part of sql"
my yahoo is abujajob@yahoo.com
WORKING CODE without WHICHCOLUMN
CREATE PROCEDURE [GetApplicants]
@CurrentPage int,
@PageSize int,
@TotalRecords int output,
@EmployerID int,
@Job_no int,
@WhichColumn varchar,
@SortBy varchar
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
Job_no int,
EmployerID int,
JobseekersID int,
Email varchar (100)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable
(
Job_no, EmployerID,JobseekersID,Email
)
SELECT Job_no, EmployerID,JobseekersID,Email FROM ApplicantsManagement
--Create variable to identify the first and last record that should be selected
DECLARE @myStatement varchar(500)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND Job_no = @Job_no
ORDER BY surname asc
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM Customers
GO
View 1 Replies
View Related
Feb 15, 2005
I want to build some paging functionality into my repeater (b4 you ask, datagrid not providing flexibility required for presentation).
I will have no problem with the VB logic but I will need to execute SQL that only returns results from x to y (e.g. results 21 thru 40 for page 2). I know I can do something like 'SELECT TOP 20 * FROM...', or something like it, for page 1. But, I'm not sure if it's possible to build SQL for the pages greater than 1. Any suggestions.
Thanks
Martin
View 1 Replies
View Related
