Orthodromy Calcul With Custom Paging In Sql

May 16, 2005

***the sql-instruction has been modified a lot, so whas was written here is now useless***

View 8 Replies


ADVERTISEMENT

Custom Paging

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

SP Works But I Need Help In Custom Paging

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

Select Query For Custom Paging

May 23, 2007

 Hi,I
am working on this select query for a report on website users. The
resulting rows will be displayed in a datagrid with custom paging. I
want to fetch 100 rows each time. This is the simplified query,
@currpage is passed as a parameter. ________________________________________________________________________________DECLARE @table TABLE (rowid INT IDENTITY(1,1), userid INT)INSERT INTO @table (userid) SELECT userid FROM UsersSELECT T.rowid, T.userid, ISNULL(O.userid, 0)FROM @table TLEFT OUTER JOIN ( SELECT DISTINCT(userid) FROM orders)AS OON O.userid = T.useridAND T.rowid > ((@currpage-1) * 100) AND T.rowid <= (@currpage * 100)ORDER BY T.rowid________________________________________________________________________________If
I run this query it returns all the rows, not just the 100 rows
corresponding to the @currpage value. What am I doing wrong? (The
second table with left outer join is there as I need one field to
indicate whether the user has placed an order with us or not. If the
value is 0, the user has not placed any orders) Thanks. 

View 2 Replies View Related

Custom Paging On Stored Procedure

Oct 12, 2007

Hello,       I receive this error "Incorrect syntax near 'GetGalleryPaged'." I'm trying to use custom paging on a stored procedure. .......       Dim mySqlConn As New SqlConnection(ConnStr)        Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)        objDA.SelectCommand.Parameters.Add("@startRowIndex", SqlDbType.Int, 1)        objDA.SelectCommand.Parameters.Add("@@maximumRows", SqlDbType.Int, 9)        Dim objDS As New DataSet()        Dim objPds As PagedDataSource = New PagedDataSource        objDA.Fill(objDS, "Gallery") <<----error here        mySqlConn.Close()        objPds.DataSource = objDS.Tables(0).DefaultView        objPds.AllowPaging = True....... ALTER PROCEDURE dbo.GetGalleryPaged (     @startRowIndex int,      @maximumRows int)AS    SELECT     idgallery, g_picpath    FROM             (        SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank            FROM Gallery    ) AS GalleryWithRowNumber    WHERE     RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)    ORDER BY idgallery DESC  cheers,imperialx 

View 5 Replies View Related

Custom Paging - Getting A Subset From 2 Tables By UserID

Apr 7, 2008

I am trying to implement custom paging. I want to get a subset from my Threads and Post tables by userID. But I can't make the stored proc work. Could somebody have a look at this and tell me what I am doing wrong or if there is a better way of doing this?

ALTER PROCEDURE [dbo].[syl_ThreadPost_GetSubsetSortedByUserID2]

@UserID uniqueidentifier,

@sortExpression nvarchar(64),

@startRowIndex int,

@maximumRows int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

BEGIN TRY

IF LEN(@sortExpression) = 0

SET @sortExpression = 'PostID'

-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment

SET @startRowIndex = @startRowIndex + 1

-- Issue query

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT t.[ThreadName],

p.PostID,

p.[PostTypeID],

p.[LanguageID],

p.[PostAccessID],

p.[UserID],

p.[ThreadID],

p.[PostParentID],

p.[VoteSummaryID],

p.[Subject],

p.[Body],

p.[PostAuthor],

p.[PostDate],

p.[IsApproved],

p.[TotalViews],

p.[FormattedBody],

p.[IPAddress],

p.[PostCount],

p.[ArticleCount],

p.[TrackbackCount],

p.[IsSticky],

p.[StickyDate]

FROM

(SELECT t.[ThreadName],

p.PostID,

p.[PostTypeID],

p.[LanguageID],

p.[PostAccessID],

p.[UserID],

p.[ThreadID],

p.[PostParentID],

p.[VoteSummaryID],

p.[Subject],

p.[Body],

p.[PostAuthor],

p.[PostDate],

p.[IsApproved],

p.[TotalViews],

p.[FormattedBody],

p.[IPAddress],

p.[PostCount],

p.[ArticleCount],

p.[TrackbackCount],

p.[IsSticky],

p.[StickyDate],

ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') AS RowNum

FROM syl_Threads t RIGHT OUTER JOIN syl_Posts p

ON t.[ThreadID] = p.[ThreadID])

WHERE t.[UserID] = ' + CONVERT(nvarchar(16), @UserID) + ' )

AS syl_ThreadPostInfo

WHERE RowNum BETWEEN ' + CONVERT(nvarchar(16), @startRowIndex) + ' AND (' + CONVERT(nvarchar(16), @startRowIndex) + ' + ' + CONVERT(nvarchar(16), @maximumRows) + ') - 1'

-- Execute the SQL query

EXEC sp_executesql @sql

RETURN

END TRY

BEGIN CATCH

--Execute LogError_Insert SP

EXECUTE [dbo].[syl_LogError_Insert];

--Being in a Catch Block indicates failure.

--Force RETURN to -1 for consistency (other return values are generated, such as -6).

RETURN -1

END CATCH

END

View 4 Replies View Related

Sorting On A Temporary Field With A Custom Paging Method

May 18, 2005

I've made another topic before concerning this problem, but since  it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself  (which will receive a different value for each record.)  Now, I want to sort on this temporary field.  And since this is a custom paging method I can choose between many page.  Now, for the first page, it sorts fine.  But when I choose a page above the first one, the sorting is not right (the results all are wrong). 
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field).  I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK,  test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.

View 17 Replies View Related

Better Method To Count Records In Custom Paging For SQL Server 2005

Jul 24, 2006

heres my problem, since I migrated to SQL-Server 2005, I was able to use the Row_Number() Over Method to make my Custom Paging Stored Procedure better.  But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure.  What I want to know is:  Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction???  heres my stored procedure:
SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum, morerecords = (Select Count(Ad_Id) From Ads) FROM Ads)  as testWHERE RowNum Between 11 AND 20
The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me.  Thank for your time.
  

View 1 Replies View Related

Displaying Custom Properties For Custom Transformation In Custom UI

Mar 8, 2007

Hi,

I am creating a custom transformation component, and a custom user interface for that component.

In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.

I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.

How do I go about getting the properties for my transformation component listed in this property grid?

I am writing in C#.

View 5 Replies View Related

Expression Editor On Custom Properties On Custom Data Flow Component

Aug 14, 2007

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:





Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";






But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

View 6 Replies View Related

Expression Issue With Custom Data Flow Component And Custom Property

Apr 2, 2007

Hi,



I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:



public override void ProvideComponentProperties()

{


ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();



IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;



...

}



In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):



"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"



@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912189912.VER".



My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time



When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"



Any idea ?



View 5 Replies View Related

Adding Custom Property To Custom Component

Aug 17, 2005

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

View 10 Replies View Related

Custom Task - Custom Property Expression

Aug 16, 2006

I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.

I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?

This is what I did in the custom task

customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]

Any help on this will be very much appreciated.

Thanks

View 3 Replies View Related

Paging

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

SQL Paging

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

T-SQL Paging

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

Paging In Sql 2k

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

Paging In RS

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

Paging Query

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

Paging Problems

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

Paging Technique

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

Paging , Sql Select From To ?

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

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 View Related

Paging Question

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

Help Using Row_Number For Paging

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

Paging (Performance)

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

Paging Problem

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

Strategies For Paging

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

Paging In SQL Server

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

Paging - Sql Server CE

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

HELP: Paging And Sorting

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

Paging On SqlCeResultSet

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

How To Implement Paging

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







Copyrights 2005-15 www.BigResource.com, All rights reserved