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


ADVERTISEMENT

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

Paging With Temporary Tables

Jun 8, 2005

I am searching for information on paging large datasets, and have found
some that involve creating temporary tables in the database. 
Before I head off and implement something, I have a number of issues
I'd like to bounce around here. 

1. An example I found on MSDN involves creating a temporary table,
copying relevant columns to the row in the temp table.  Why do
this, rather add the source tables primary keys into the temp table,
and do a join? Example;  browsing Products Catalog which is
categorised into hierarchies.  The MSDN version would have a temp
table created with a incrementing field which is used for the paging,
and then a number of fields are also copied from the products table to
the temp table - my question is why not simply copy the product primary
key into the temp table, and then join?

2. In real life, do people allow each user to create their own
temporary tables? If I have 1000 concurrent users, all wishing to
perform a page-based browse, I would be creating 1000 new temporary
tables.  Do people consider default temp tables, that is, creating
a default temporary table for browsing each category in the products
table, for example?

3. Do you have any advice/tips for this type of problem?

Thanks!

JR.

View 17 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 And Sorting

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

Paging And Sorting With RowNum()

Aug 13, 2007

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:

INSERT INTO #RESULTS

SELECT PY.PaymentId

, PY.PayeeId

, PY.PartyAddressId

, PY.DistributionId

, PY.EntitlementId

, PY.DeliveryTypeEnumItemId

, PY.AccountPaymentId

, PY.ParentPaymentId

, PY.PaymentAmount

, PY.PaymentDate

, PY.PaymentStatusEnumItemId

, PY.PaymentStatusDate

, PY.ReleaseRunId

, PY.ReleaseDate

, PY.AccountTransactionLogId

, PY.AccountStatusEnumItemId

, PY.AccountStatusDate

, PY.AccountPaidAmount

, PY.ReconciledInd

, PY.UndeliverableInd

, PY.ReissueNote

, PY.CreateDate

, PY.CreateId

, PY.ModifiedDate

, PY.ModifiedId

, DS.Description

, AC.Description

, AC.AccountProvider

, AC.AccountId

, PT.Name

, PA.AddressLine1

, PA.AddressLine2

, PA.City

, PA.State

, PA.Zip5

, PA.Zip4

, PE.clm_no

, CM.clmnt_idno

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.AccountPaymentId,

ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.AccountPaymentId = PY.AccountPaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize

AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))

AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))

AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))

AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time

AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))

AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))

AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))

AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))

AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))

AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))

ORDER BY AccountPaymentID

--GET A COUNT OF THE ROWS SELECTED

SELECT @TotalRows = Count(*)

FROM Payment PY (NOLOCK)

JOIN (SELECT DISTINCT

PY.PaymentId,

ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum

FROM Payment PY (NOLOCK)) AS SQ

ON (SQ.PaymentId = PY.PaymentId)

JOIN Distribution DS (NOLOCK)

ON (DS.DistributionId = PY.DistributionId)

JOIN Account AC (NOLOCK)

ON (AC.AccountId = DS.AccountId)

JOIN PartyAddress PA (NOLOCK)

ON (PA.PartyAddressId = PY.PartyAddressId)

JOIN Party PT (NOLOCK)

ON (PT.PartyId = PA.PartyId)

JOIN Payee PE (NOLOCK)

ON (PE.PayeeId = PY.PayeeId)

JOIN clm CM (NOLOCK)

ON (CM.clm_no = PE.clm_no)

WHERE

((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))

AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))

AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))

AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time

AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))

AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))

AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))

AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))

AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))

AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))

SET @ORDERBY = ' ORDER BY ' + @SORT --END

--CASE WHEN @Sort IS NULL THEN '' ELSE

EXEC('SELECT * FROM #RESULTS ' + @ORDERBY)

--SET @TOPSQL = 'SELECT TOP ' + Convert(VarChar,@PageSize) + '* FROM #RESULTS ' + @ORDERBY

--SELECT @TOPSQL

--EXEC (@TOPSQL)

SET @PAGES = Round(@totalRows / @PageSize,0,1) + CASE WHEN @TotalRows % @PageSize = 0 THEN 0 ELSE 1 END

-- Return Total number of pages and Total number of Rows

SELECT @PAGES AS PageCount,

@TOTALROWS AS TotalRecords

I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.



I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)



What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,

2.) The record set is resorted in the proper order

3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!

View 2 Replies View Related

Paging And Sorting Using ROWNUM()

Aug 14, 2007

I apologize in advance, but this post might get somewhat lengthy.

I'm new to the whole pagiong and sorting in SQL Server 2005, and I'm trying to get my SQL to perform in a certain way but can't seem to nail it just down. Hopefully someone can provide some insight or direction. Here's the scoop:

The gui sorts on any column chosen. For example, there's USER, ADDRESS, CITY, STATE, ZIP. The gui allows you to choose how many rows you wish to display per page. If there are 500 rows that meet the search criteria and you choose five pages, there should be 100 records per page. Here's the code:




Code Snippet
INSERT INTO #RESULTS
SELECT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
, AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId,
ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.AccountPaymentId = PY.AccountPaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
ORDER BY AccountPaymentID
--GET A COUNT OF THE ROWS SELECTED
SELECT @TotalRows = Count(*)
FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.PaymentId,
ROW_NUMBER() OVER(ORDER BY PY.PaymentId) AS RowNum
FROM Payment PY (NOLOCK)) AS SQ
ON (SQ.PaymentId = PY.PaymentId)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no)
WHERE
((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%'))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
SET @ORDERBY = ' ORDER BY ' + @SORT --END
--CASE WHEN @Sort IS NULL THEN '' ELSE
EXEC('SELECT * FROM #RESULTS ' + @ORDERBY)
--SET @TOPSQL = 'SELECT TOP ' + Convert(VarChar,@PageSize) + '* FROM #RESULTS ' + @ORDERBY
--SELECT @TOPSQL
--EXEC (@TOPSQL)
SET @PAGES = Round(@totalRows / @PageSize,0,1) + CASE WHEN @TotalRows % @PageSize = 0 THEN 0 ELSE 1 END
-- Return Total number of pages and Total number of Rows
SELECT @PAGES AS PageCount,
@TOTALROWS AS TotalRecords




I get back the rows I'm expecting and it looks fine. What I can't get to happen is the proper sort.

I get back records 1014 records - 1001 through 2014. I choose to display 400 records per page, so ther will be 3 pages total (1001 through 1400 on page 1, 1401 through 1800 on page 2, and 1801 through 2014 on page 3. All the records are sorted by RECORD NUMBER (1000, 1001, etc.)

What I would like to do is when I choose to sort on the column (ASC or DESC),

1.) The entire record set is esssentially retrieved again,
2.) The record set is resorted in the proper order
3.) The record set is redisplayed.

For example, if I'm on Page 2, and I choose to sort in DESCending order, Page 1 would then have records 2014 through 1615, Page 2 would display 1614 through 1215, and page 3 would have 1214 through 1001. Since I was already on Page 2, I would be seeing Page 2 with the new sort. Now when I resort , it just sorts the records on the individual pages, not the entire result set.

Hopefully this all made sense...!

If anyone has any advice or insight, please don't hesitate!

Thank You!!

View 11 Replies View Related

Sorting && Paging With Recursive Queries

Jun 14, 2007

Hi, can anyone tell me how I can get implement sorting and paging using a recursive query?
I have a created a stored procedure (bit like the simple example below), but would like to add in sorting and paging using order by and row_number().
DECLARE @CategoryID intSET @CategoryID = 12;WITH CTE_Example (CategoryID, CategoryName, ParentID, Depth, RowNum)AS(SELECT CategoryID, CategoryName, ParentID, 0 AS Depth, Row_Number() OVER (ORDER BY CategoryName) AS RowNumFROM Categories WHERE CategoryID = @CategoryIDUNION ALLSELECT Categories.CategoryID, Categories.CategoryName, Categories.ParentID, CTE_Example.Depth + 1 AS Depth, Row_Number() OVER (ORDER BY Categories.CategoryName) AS RowNumFROM CategoriesJOIN CTE_Example ON Categories.ParentID = CTE_Example.CategoryID)SELECT * FROM CTE_Example WHERE RowNum BETWEEN @Start AND @End ORDER BY RowNum
I think the problem comes down to the Union, appreciate if someone can help.
Many thanks,
Matt

View 1 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.



Any suggestions would help.


Thanks

View 1 Replies View Related

Sorting + Paging A Large Table In Stored Procedure

May 6, 2007

As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post,   http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.

View 3 Replies View Related

Evaluate My Stored Procedure For Paging, Filtering, And Sorting

Apr 28, 2008

This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.

This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).

CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,

-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',

-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN

DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)


-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'



-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'




-- build the WHERE search condition used to control filtering throughout this procedure

SET @searchCondition = '(1 = 1)'

IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'




-- build the ORDER BY expression used to control the sorting throughout this procedure

SET @orderByExpression = @sortColumn + ' ' + @sortDirection

-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection





-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order

SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'

DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant

EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT



-- Get the filtered subset of results

-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '

-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'

-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression

PRINT @sql

SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'

EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id


-- Reset the rowcount for others
SET ROWCOUNT 0

END;
GO

View 14 Replies View Related

(SOS!) Logic Paging Use Group, But Meet Sorting Problem

Jan 16, 2008

User's requirement:
use the SP get the dataset from DB at once.
Want to make an accurate count of paging ( 200 rows /page) at the SSRS side.
Need to provide sorting, user just need to click the according column header's caption.

The design is:
we add group to devide the data into 200 per unit. Choice 'page break at end'.
add 2 Report Parameters, SD & SF, means sorting direction and Field.
In the Table Parameters add:

=IIF(Parameters!SD.Value="Ascending",Fields(Parameters!SF.Value).Value,"")

=IIF(Parameters!SD.Value="Descending",Fields(Parameters!SF.Value).Value,"")

everything seems OK at this time, and the rpt is very quick.

The Bug is:
Test team found out the sorting was broken by group, because we Choice 'page break at end'.
Noe the sorting scope is just the first page (first group)

Help wants:
query DB once , Accurate paging, full scope sorting.




View 5 Replies View Related

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

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

Custom Sorting

Aug 15, 2007

I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:

Function SortTypeValue() As String
If Report.Parameters!SortType.Value = "1" Then
Return "=Fields!StockDesc.Value"
else Return "=Fields!StockCode.Value"
End If
End Function

Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().

However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?

Thanks in advance.

View 6 Replies View Related

Problem Sorting Using Custom Code As The SortExpression

Jul 25, 2007

I have a report that contains a Chart called "SummaryChart" and a table called "DetailsTable"
the data for the summary chart comes from a data set called dataSourceSummary and the data for the details table comes from a data set called dataSourceDetails.

The summary chart presents counts of items broken down by a category and the details table shows the details of the items that make up the counts in summary chart.

I have a situation where I need to sort the data in the table in a non alpha-numeric way. So I have added a user sort item to the table column header and set the SortExpression to something like the following

SortProvider.GetUserSortOrder("TableName",5,Fields!myColumn.Value)

where SortProvider is a C# class and GetUserSortOrder is a static method
the method essentially finds a row in a database, get the display order for that row and returns it.

I have tested this in another report and it works as expected.

Now finally to the problem:

What happens is when I click on the table heading, values from fields in the dataset for the chart are being passed into my method, followed by values from fields in the dataset for the table and then an exception is thrown.

The only difference between this report and ones that I have tested this on and it works is that this report has two dataregions (Chart and Table) and two datasets.

I have tried setting the ExpressionScope to DetailsTable and the SortTarget to dataSourceDetails but that results in the sort not working at all.

I would have thought that it should only be passed values from the data that it is sorting, especially considering that the data that is being passed in from the chart is a completely different field.

Has anyone else experienced this?
Has anyone got any ideas?

View 1 Replies View Related

Sorting Out Data In A Field

Apr 19, 2004

Hi there,

I got one prob and i need help to solve it.
I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
Example:

ASP_Tin No
----------------

ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........

and i need to sort it out into like this

ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl

i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx

View 2 Replies View Related

Field Not Sorting In Ascending Order

Dec 30, 2005

Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of  items, but why it happens to this particular record? Can anyone help? Thanks in advance

View 3 Replies View Related

Sorting Stored Procedure By Text Field

Mar 19, 2001

I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:

sp_select_thing 10, 'thing_name', 'asc'

It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible

Thanks!

View 1 Replies View Related

Selecting From Two Tables, And Sorting By Common Field.

Feb 10, 2002

This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.

PLEASE HELP ME!!!
Pending deadline doom :(

Thanks,
kaskenasy@publishingconcepts.com

View 1 Replies View Related

Sorting Address Field By Its Component Parts

Nov 14, 2007

The file of about 30,000 records contains an address field where addresses are entered in the following form:

128 NW 8 ST
3444 SW 19 AVE
32 SE 122 TERR
12604 SW 148 PL

Then there are anomalies such as
123 E BLVD
(I can ignore the anomalies until the main challenge is overcome.)

The challenge is that I have been asked to sort the addresses in the following order:

quadrant (e.g., NW, SW),
street number (e.g., 122),
street name (e.g., TERR)
house number (e.g., 128)

It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing.

How do I get these part into a four-column view? Or, is there another way to do this?


Esther

View 2 Replies View Related

Error: Method, Property Or Field Is Not Static

Feb 12, 2007

I've got a CLR stored proc I'm trying to deploy to SQL2005, when I try to debug it I get the error message:
Method, property or field 'usp_Evaluate_eTranscript' of class 'ACS_eTranscripts.StoredProcedures' in assembly 'ACS_eTranscripts' is not static.

The class & procedure are declared:
Partial Public Class StoredProcedures Private Structure CalcGPA Friend intGrdTot As Integer Friend intCrsKnt As Integer End Structure <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Sub usp_Evaluate_eTranscript(ByVal SSN As String)

When I change the sub's declaration to:
Static Sub usp_Evaluate_eTranscript(ByVal SSN As String)

I get an error message that:
Methods cannot be static.

So, is this a Catch 22 or what am I missing?

Any help would be greatly appreciated as this is a major project and I am woefully behind because of emergency surgery.

View 8 Replies View Related

Can I Add Query Field With Custom Calculation

Nov 20, 2007

i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?


thanks

View 2 Replies View Related

Can I Add Query Field With Custom Calculation

Nov 20, 2007

i have 3 question :
1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ?
2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do?
3. is that any wizard to do no 1 and 2?


thanks

View 1 Replies View Related

Reporting Services :: Horizontal Axis Show Last Value In First And Last Space When Sorting A-z But Shows Correctly When Sorting Z-a

Jul 10, 2015

SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int.  When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end.  The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order.  The sorting is implemented using the Category Group sorting option.

View 6 Replies View Related

Accessing Datasets / Field Collections Through Custom Code

Jun 12, 2007

Hi,

My report has multiple datasets, and I want to access the fields of a particular dataset from custom code. The problem is that the name of the dataset to use is only calculated during the execution of the custom code.

Allow me to illustrate:

public function Test(data as DataSets, fieldName as string)dim datasetName as string = CalculateDatasetName()dim ds as Dataset = data(datasetName)' now here I want to get the fieldName out of the dataset.' in the report, I would do something like
' First(Fields!fieldName.Value, datasetName)' but in custom code, this obviously doesn't work.end function

I've been looking for a way to accomplish this, but it seems you cannot get data from a dataset through custom code (there is only the commandtext property). Also it is not possible to pass the Fields collection as a parameter, as I do not know the dataset name when in the report designer.

I hope my problem description is clear.
Does anyone know how to solve this issue?

Thanks,
Phil

View 2 Replies View Related

SQL 2012 :: Accurate Sorting Data Each Time With Millions Of Records Without Time Field?

Apr 25, 2014

Sample Table

USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ....

It seems to work fine with one million records.

Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.

View 7 Replies View Related

Custom Parameter For Select Paramters - How To Use The Value Of Another Field As The Paramter Default.

Mar 5, 2008

SELECT     ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM         UserArticlesWHERE     (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing.  Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
 Thanks for any help!
 

View 1 Replies View Related

Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method

May 10, 2007

Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString)    {      //call stored procedure  } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.  

View 3 Replies View Related







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