USE [BeyondDB]
GO
/***** Object: StoredProcedure [dbo]. [Y_Paging] Script Date: 02/22/2013 14:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]. [Y_Paging]
(
@ TableName VARCHAR (max) = null, -- table name
@ FieldList VARCHAR (max) = null, -- displays the column name. If it is 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 the 'ORDER BY' character, such as id asc, userid desc. asc or desc must be specified.
@ SortType INT = null, -- sorting Rule 1: Positive asc 2: reverse desc 3: Multi-column sorting method
@ RecorderCount INT = null, -- total number of records 0: the total number of records is returned.
@ PageSize INT = null, -- 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
Begin
DECLARE @ SQL NVARCHAR (max );
DECLARE @ totalSql NVARCHAR (max );
If (@ Keyword is not null and @ Keyword! = '')
Begin
If 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 )'
End
If (@ 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
(
SELECT '+ @ FieldList +', ROW_NUMBER () OVER (order by '+ @ Order +') as RowNumber
FROM '+ @ 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' + @ WHERE
Print (@ 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 output
End
-- Call an instance
USE [BeyondDB]
GO
DECLARE @ return_value int,
@ TotalCount int,
@ TotalPageCount int
EXEC @ 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 OUTPUT
SELECT @ TotalCount as n' @ totalcount ',
@ TotalPageCount as n' @ TotalPageCount'
SELECT 'Return value' = @ return_value
GO |
