Use Stored Procedure Code collection in Asp

1 CREATE procedure p_splitpage
2 @ SQL nvarchar (4000 ),
3 @ page int = 1,
4 @ pagesize int,
5 @ pageCount int = 0 output,
6 @ recordCount int = 0 output
7as
8 set nocount on
9 declare @ p1 int
10
11 exec sp_cursoropen @ p1 output, @ SQL, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ pagecount output
12 set @ recordCount = @ pageCount
13 select @ pagecount = ceiling (1.0 * @ pagecount/@ pagesize)
14, @ page = (@ page-1) * @ pagesize + 1
15 exec sp_cursorfetch @ p1, 16, @ page, @ pagesize
16 exec sp_cursorclose @ p1
17GO
18
19
20' call database data through stored procedures
21 'SQL is a query statement, m_pagesize is the page display row, rst is the record set, totalpages is the number of pages, num is the number of records, curpage is the current page number
22Sub SelectDB (SQL, m_pageSize, rst, totalpages, num, curpage)
23if curpage = "" then
24 curpage = Trim (Request. Form ("pageno "))
25if curpage = "" then
26 curpage = Trim (Request. QueryString ("pageno "))
27end if
28if curpage = "" then
29 curpage = 1
30end if
31end if
32
33 Set cmd = Server. CreateObject ("ADODB. Command ")
34cmd. ActiveConnection = conn
35cmd. CommandType = adw.storedproc
36cmd. CommandText = "p_SplitPage"
37
38cmd. Parameters. Append cmd. CreateParameter ("@ SQL", adVarWChar, adParamInput, 4000, SQL)
39cmd. Parameters. Append cmd. CreateParameter ("@ page", adInteger, adParamInput, 4, curpage)
40cmd. Parameters. Append cmd. CreateParameter ("@ pageSize", adInteger, adParamInput, 4, m_pageSize)
41cmd. Parameters. Append cmd. CreateParameter ("@ pageCount", adInteger, adParamOutput, 4, totalpages)
42cmd. Parameters. Append cmd. CreateParameter ("@ recordCount", adInteger, adParamOutput, 4, num)
43
44 'response. Write SQL & "<br/>"
45 set rst = cmd. Execute
46 set rst = rst. NextRecordSet
47
48 totalpages = cmd. Parameters ("@ pageCount"). value
49num = cmd. Parameters ("@ recordCount"). value
50
51if totalpages = 0 then totalpages = 1
52End Sub
53