USE [cc197]
GO
/****** Object: StoredProcedure [dbo].[PROC_KeyValue_SelectBySearch] Script Date: 01/11/2013 16:38:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------------------------------------------------------------
/* 根据条件检索, 支持分页 */
ALTER PROCEDURE [dbo].[PROC_KeyValue_SelectBySearch]
@pageIndex INT = 1, --页码索引
@pageSize INT = 1, --页面大小
@doCount BIT = 0, --是否进行数量统计
@typeId INT = NULL,
@keyName NVARCHAR(100) = NULL,
@val NVARCHAR(100) = NULL,
@orderField VARCHAR(50) = NULL, --排序字段
@orderType BIT = 1 --排序方式,1升序/0降序
AS
SET NOCOUNT ON
DECLARE @SqlResult NVARCHAR(2000) --最终SQL语句
DECLARE @SqlWhere NVARCHAR(1000) --SQL查询条件
DECLARE @SqlOrder NVARCHAR(100) --SQL排序方式
SET @SqlWhere = N' 1=1'
--组合查询条件
IF @typeId IS NOT NULL
SET @SqlWhere = @SqlWhere + N' AND typeId = @typeId '
IF @keyName IS NOT NULL
SET @SqlWhere = @SqlWhere + N' AND keyName = @keyName '
IF @val IS NOT NULL
SET @SqlWhere = @SqlWhere + N' AND val = @val '
--组合排序方式
SET @SqlOrder = N' pkid ASC'
IF(@OrderField IS NOT NULL)
BEGIN
IF(@OrderType = 1)
SET @SqlOrder = @OrderField + N' ASC'
ELSE
SET @SqlOrder = @OrderField + N' DESC'
END
IF(@DoCount = 1)
BEGIN
SET @SqlResult =
N'SELECT COUNT(pkid) AS TotalCount FROM KeyValue WHERE' + @SqlWhere
--执行带参数的SQL语句(根据条件进行数据统计)
EXEC SP_EXECUTESQL @SqlResult,
N'@typeId INT, @keyName NVARCHAR(100), @val NVARCHAR(100)',
@typeId, @keyName, @val
END
ELSE
BEGIN
--创建临时的排序索引表
CREATE TABLE #IndexTable(TempId INT PRIMARY KEY IDENTITY(1,1), FKID INT)
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
SET @PageLowerBound = (@PageIndex - 1) * @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
SET @SqlResult =
N'SET ROWCOUNT @PageUpperBound
INSERT INTO #IndexTable(FKID)
SELECT pkid FROM KeyValue WHERE' + @SqlWhere +
N' ORDER BY ' + @SqlOrder
--执行带参数的SQL语句(插入临时表)
EXEC SP_EXECUTESQL @SqlResult,
N'@PageUpperBound INT, @typeId INT, @keyName NVARCHAR(100), @val NVARCHAR(100)',
@PageUpperBound, @typeId, @keyName, @val
SET @SqlResult =
N'SELECT pkid, typeId, keyName, val
FROM KeyValue kv, #IndexTable t
WHERE kv.pkid = t.FKID AND t.TempId > @PageLowerBound AND t.TempId < @PageUpperBound AND ' + @SqlWhere +
N' ORDER BY ' + @SqlOrder
--执行带参数的SQL语句(查询符合条件的记录)
EXEC SP_EXECUTESQL @SqlResult,
N'@typeId INT, @keyName NVARCHAR(100), @val NVARCHAR(100), @PageLowerBound INT, @PageUpperBound INT',
@typeId, @keyName, @val, @PageLowerBound, @PageUpperBound
END