stored procedure for paging, with sorting

stored procedure for paging, with sorting

Post by neil » Thu, 23 Jan 2003 09:29:58



where can i find some info on creating a sproc so that i can get a "page" of
data from a table, but also be able to select the sort field and direction?

i want an interface something like this:


thx

neil m

 
 
 

stored procedure for paging, with sorting

Post by Anith Se » Thu, 23 Jan 2003 09:42:10


There are many ways you can do this. Here is one:

SELECT *
  FROM Orders
 WHERE (SELECT COUNT(*)
    FROM Orders t1
   WHERE OrderId <= Orders.OrderId)


You can also use SET ROWCOUNT or TOP, for which you can find tons of
examples if you search this Newsgroup. As for the dynamic ORDER BY clause
you can do:





        ....

You can always build your query from your client side and call it ad hoc
from your web page as well, which will make it much more manageable.

--
- Anith
(Please respond only to newsgroups)

 
 
 

1. Problem creating a counter for page views via stored procedures called from an asp page

Hello there

There is probably a simple solution to this is you are at all clued up
on SQL  - however I'm not - so here goes.

I have a table within the database  - a list of venues. I want to be
able to record the number of times a row within this table is
accessed.

I have created a column HitCount in dbo.VenueData with a numeric
datatype.

Each time IUSR_MYBOX views the data in a row via an ASP page I want
recCount to increment by 1.

I would then be able to display to the venue owner (who is paying for
a
listing on the site) how many times their info has been displayed.
However I do not want recCount to increment if I view the record
during
development.

At the moment i have this code on the asp listing page.

<----snip---->
Dim rsHitCount__ID
rsHitCount__ID = '0'
if (Request.QueryString("VenueNum") <> "") then rsHitCount__ID =
Request.QueryString("VenueNum")
Dim rsHitCount__CurrentHits
rsHitCount__CurrentHits = "0"
set rsHitCount = Server.CreateObject("ADODB.Recordset")
rsHitCount.ActiveConnection = MM_Shropshire_STRING
rsHitCount.Source = "{call dbo.sp_Increment_HitCount(" +
Replace(rsHitCount__ID, "'", "''") + "," +
Replace(rsHitCount__CurrentHits, "'", "''") + ")}"
rsHitCount.CursorType = 0
rsHitCount.CursorLocation = 2
rsHitCount.LockType = 3
rsHitCount.Open()
rsHitCount_numRows = 0

<----snip---->

and this as my stored procedure 'sp_Increment_HitCount'

<----snip---->


 AS





GO

<----snip---->

This works fine when VenueNum is a numeric value eg '123' - however
when the data was originally entered some venues where given a value
such as '123X' I get an error "Invalid character value for cast
specification" - I cannot remove the alphabetical characters from the
database because the characters mean something.

(I am developing using Ultradev 4, Interdev 6.0, ASP 2.0, SQL 2000,
IIS 5)

Thanks in advance

Andrew Mottershead

2. Question on Formating: displaying $ (dollar) within the query results.

3. Calling a Java Stored Procedure from another Java Stored Stored Procedure

4. Creating MS Access Table

5. Sorting Stored procedure using a parameter

6. Problems with psql-general list

7. Stored Procedure: Specifying ASC or DESC sort order through a parameter

8. What database field to store money amounts in??

9. dynamic sorting in stored procedure

10. Passing Sort Parameter to Stored Procedure

11. Sorting by Variable in Stored Procedure

12. Help with Stored Procedure - dynamic sort of temp table

13. Using a Stored Procedure to sort and update a column