Pioneer of creative programs

ViewPost stored procedure page USE [BeyondDB] GO ****** Object: StoredProcedure [dbo]. [Y_Paging] ScriptDate: 0222201314: 53: 26 ****** SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOALTERproc [dbo]. [Y_Paging] (@ TableNameVARCHAR (max) null, -- table name @ Fi

View Post stored procedure page USE [BeyondDB] GO/****** Object: StoredProcedure [dbo]. [Y_Paging] Script Date: 02/22/2013 14:53:26 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo]. [Y_Paging] (@ TableName VARCHAR (max) = null, -- table name @ Fi

View Post

Stored Procedure page

USE [BeyondDB] GO/****** Object: StoredProcedure [dbo]. [Y_Paging] Script Date: 02/22/2013 14:53:26 *****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo]. [Y_Paging] (@ TableName VARCHAR (max) = null, -- table name @ FieldList VARCHAR (max) = null, -- display column name, for all fields, it is * @ PrimaryKey VARCHAR (max) = null, -- single primary key or unique value Key @ Where NVARCHAR (max) = null, -- the query condition does not contain the 'where' character, for example, id> 10 and len (userid)> 9 @ Order VARCHAR (max) = null, -- sorting does not contain 'ORDER BY' character, Hong Kong server, Hong Kong virtual host, such as id asc, userid desc, Hong Kong Space, must specify asc or desc @ SortType INT = null, -- sorting rule 1: positive asc 2: reverse desc 3: Multi-column sorting method @ RecorderCount INT = null, -- total records 0: Total records @ PageSize INT = null will be returned, -- number of records output per page @ PageIndex INT = null, -- current page number @ Keyword varchar (max) = null, -- Keyword @ FieldOne varchar (max) = null, -- field 1 @ FieldTwo varchar (max) = null, -- field 2 @ TotalCount int output, -- Record total returned records @ TotalPageCount int output -- total number of returned pages) as beginDECLARE @ SQL NVARCHA R (max); DECLARE @ totalSql NVARCHAR (max); if (@ Keyword is not null and @ Keyword! = '') Beginif ISNULL (@ FieldOne ,'')! = ''Set @ Order = @ Order + ', (case when charindex (''' + replace (@ Keyword ,'',''', '+ @ FieldOne +')> 0 then 1 else 0 end) + (case when charindex (''') + ''', '+ @ FieldOne +')> 0 then 1 else 0 end) 'if ISNULL (@ FieldOne ,'')! = ''Set @ Order = @ Order + ', (case when charindex (''' + replace (@ Keyword ,'',''', '+ @ FieldTwo +')> 0 then 1 else 0 end) + (case when charindex (''') + ''', '+ @ FieldTwo +')> 0 then 1 else 0 end) 'endif (@ SortType is not null and @ SortType = 1) set @ Order = @ Order + 'asc 'if (@ SortType is not null and @ SortType = 2) set @ Order = @ Order + 'desc' SET @ SQL = 'WITH LIST AS (SELECT' + @ FieldList + ', ROW_NUMBER () OVER (order by '+ @ Order +') as RowNumberFROM '+ @ TableName + 'where 1 = 1' + @ WHERE + ') SELECT * from list where RowNumber BETWEEN '+ STR (@ PageIndex + 1) + 'and' + STR (@ PageIndex + @ PageSize) set @ totalSql = 'select @ TOTALCOUNT = COUNT (*) FROM '+ @ TableName + 'where 1 = 1' + @ Whereprint (@ SQL) EXEC (@ SQL) -- EXEC sp_executesql @ totalSql, n' -- @ ID uniqueidentifier, -- @ StatusList varchar (max), -- @ BeginTime datetime, -- @ EndTime datetime, -- @ TitleOrNo varchar (max ), -- @ Excutor uniqueidentifier, -- @ Assignor uniqueidentifier, -- @ TotalCount int output -- ', -- @ ID, -- @ StatusList, -- @ BeginTime, -- @ EndTime, -- @ TitleOrNo, -- @ Excutor, -- @ Assignor, -- @ TotalCount outputend -- call the instance USE [BeyondDB] GODECLARE @ return_value int, @ TotalCount int, @ TotalPageCount intEXEC @ return_value = [dbo]. [Y_Paging] @ TableName = n'account', @ FieldList = n' * ', @ PrimaryKey = n'id', @ Where = n' and 1 = 1 ', @ Order = n'createtime', @ SortType = 2, @ PageSize = 5, @ PageIndex = 0, @ RecorderCount = null, @ Keyword = n'1 ', @ FieldOne = N 'accountname', @ FieldTwo = N 'accountid', @ TotalCount = @ TotalCount OUTPUT, @ TotalPageCount = @ TotalPageCount OUTPUTSELECT @ TotalCount as n' @ TotalCount ', @ TotalPageCount as n' @ TotalPageCount 'select 'Return value' = @ return_valueGO

　　

Posted on