Create Procedure getPage @ TBLNAME VARCHAR (255), - Table Name @StrGetfields VARCHAR (1000) = '*', - Need to return column @fldname varchar (255) = '', sorted field name @PageSize Int = 10, - Page size @PageIndex INT = 1, - Page @Docount bit = 0, - Return the total number of records, non-0 values returns to @Ordertype bit = 0, - Set the sort type, non-0 value, descending order @Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add where) AS
Declare @STRSQL VARCHAR (5000) - Professional Sentrief Declare @strtmp Varchar (110) - Temporary Variable Declare @strORDER VARCHAR (400) - Sort Type IF @docount! = 0 Begin if @Strwhere! = 'set @strsql = "SELECT Count (*) as Total from [" @TBLNAME "] Where" @ strwhere else set @strsql = "Select count (*) as total from [" @tblname "]" END - above The code means that if @Docount passes is not 0, the total statistics are executed. All of the following code is @docount is 0
Else Begin if @ORDERTYPE! = 0 Begin set @strtmp = "<(select min" set @strorder = "ot [" @fldname "] desc" - If @OlDertype is not 0, it will be designed, this sentence Very important! End
Else Begin Set @STRTMP = "> (Select Max" Set @strorder = "Order by [" @fldname "] ASC" end if @pageIndex = 1 begin if @Strwhere! = '' set @strsql = "SELECT TOP " Str (@Pagesize) " @ strGetfields "from [" @tblname "] where" @strwhere "" @strorder else set @strsql = "select top" str (@PageSize) "" @ strGetfields "from [" @tblname "]" @strorder - If it is the first page, execute the above code, this will speed up the execution END
Else Begin - The following code gives @STRSQL to really executed SQL code set @strsql = "select top" str (@PageSize) " @ strGetfields " from [" @tblname "] where [" @fldname "]" @strtmp "([" @fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "[" @fldname "] from [" @TBLNAME "] " @strorder ") as tbltmp) " @strorder if @Strwhere! = '' set @strsql =" select top " str (@Pagesize " @ strGetfields " From [" @TBLNAME "] Where [" @fldname "] " @strtmp " ([" @fldname "]) from (SELECT TOP " STR ((@ PageIndex-1) * @ Pagesize) "[ @fldname "] from [" @TBLNAME "] where " @Strwhere " " @strorder ") AS TBLTMP) and " @strwhere " @strorder end End exec (@strsql) gofunction getPagedata (TBLNAME, Getfields, FldName, Pagesize, Cur_page, ReadType, Ordertype, Where)
Set conn = server.createObject ("adoDb.connection")
Conn.open "driver = {SQL Server}; server = 127.0.0.1; uid = sa; pwd = 2; Database = Med"
DIM RS, CMD
SET cmd = server.createObject ("adodb.command")
Cmd.activeConnection = conn
Cmd.commandtype = 4 'AdcmdStoredProc
Cmd.prepared = true 'Save Compilation
Cmd.comMandtext = "PageAction" 'Stored Procedure Name
'Setting parameters
CMD.Parameters.Append Cmd.createParameter ("@ TBLNAME", 200, 1, 255, tblname) cmd.parameters.Append cmd.createParameter ("@ strgetfields", 200, 1, 1000, getfields)
Cmd.parameters.Append Cmd.createParameter ("@ fldname", 200, 1, 255, fldname)
Cmd.Parameters.Append Cmd.createParameter ("@ Pagesize", 3, 1, 4, PageSize
Cmd.parameters.Append Cmd.createParameter ("@ pageindex", 3, 1, 4, cur_page)
Cmd.parameters.Append Cmd.createParameter ("@ docount", 3, 1, ready)
Cmd.Parameters.Append Cmd.createParameter ("@ Ordertype", 3, 1, 4, ordetepe)
Cmd.parameters.Append Cmd.createParameter ("@ strwhere", 200, 1, 1500, where)
SET RS = Server.createObject ("AdoDb.Recordset")
Rs.cursortype = 3
Rs.lockType = 3
Rs.cursorlocation = 3
Set rs.source = cmd
RS.Open
SET getPageData = RS
END FUNCTION
