ALTER PROCEDURE [dbo].[sp_ProductGetList]
------------------------------------------------------------------------
--目的:常规返回产品列表(以分页形式)
--
--维护日志
--
--维护人 维护时间 描述
------------------- ------------------- ------------------------
--xuyiwei 2012-7-24 创建
------------------------------------------------------------------------
--分类Id
@Category INT,
--搜索关键字
@Key NVARCHAR(50),
--当前页码
@PageNumber INT,
--每页多少
@PageSize INT,
--返回总共多少行
@HowMany INT OUTPUTAS
-- 声明新的TABLE变量
DECLARE @Product TABLE
(
RowNumber INT,
Id INT,
Nick NVARCHAR(50),
NumIid bigint,
TrackIiid NVARCHAR(50),
ContactPhone NVARCHAR(50),
CategoryId INT,
CreateTime DATETIME,
ModifiedTime DATETIME,
Sorting INT,
EffectiveIntegral INT,
ShareIntegral INT
)-- 用完整的产品列表填充该TABLE变量
INSERT INTO @Product
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product
--WHERE Id > 0想在这里判断参数不空的话就查询相关字段,可好象放这里不行
--IF @Category > 0
--AND CategoryId = @Category
--IF @Key <> ''
--AND Nick = @Key
ORDER BY Id DESC-- 使用OUTPUT变量返回总共多少行
SELECT @HowMany = COUNT(Id) FROM @Product-- 获取请求的商品页面
SELECT Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM @Product
WHERE RowNumber > (@PageNumber - 1) * @PageSize
AND RowNumber <= @PageNumber * @PageSize
------------------------------------------------------------------------
--目的:常规返回产品列表(以分页形式)
--
--维护日志
--
--维护人 维护时间 描述
------------------- ------------------- ------------------------
--xuyiwei 2012-7-24 创建
------------------------------------------------------------------------
--分类Id
@Category INT,
--搜索关键字
@Key NVARCHAR(50),
--当前页码
@PageNumber INT,
--每页多少
@PageSize INT,
--返回总共多少行
@HowMany INT OUTPUTAS
-- 声明新的TABLE变量
DECLARE @Product TABLE
(
RowNumber INT,
Id INT,
Nick NVARCHAR(50),
NumIid bigint,
TrackIiid NVARCHAR(50),
ContactPhone NVARCHAR(50),
CategoryId INT,
CreateTime DATETIME,
ModifiedTime DATETIME,
Sorting INT,
EffectiveIntegral INT,
ShareIntegral INT
)-- 用完整的产品列表填充该TABLE变量
INSERT INTO @Product
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product
WHERE Id > 0
想在这里判断参数不空的话就查询相关字段,可好象放这里不行
AND @Category > 0
AND CategoryId = @Category
AND @Key <> ''
AND Nick = @Key
ORDER BY Id DESC-- 使用OUTPUT变量返回总共多少行
SELECT @HowMany = COUNT(Id) FROM @Product-- 获取请求的商品页面
SELECT Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM @Product
WHERE RowNumber > (@PageNumber - 1) * @PageSize
AND RowNumber <= @PageNumber * @PageSizewhere后面怎么可以用IF语句,楼主仔细分析一下自己的思路,到底应该是先判断在执行对应操作还是把数据筛选出来了,在判断条件看该不该查询某些东西。从逻辑上分析一下
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product
--WHERE Id > 0想在这里判断参数不空的话就查询相关字段,可好象放这里不行
--IF @Category > 0
--AND CategoryId = @Category
--IF @Key <> ''
--AND Nick = @Key
ORDER BY Id DESC
用拼接不行么?
declare @sql varchar(max)
set @sql='INSERT INTO @Product
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product
WHERE Id > 0'IF @Category > 0
exec sp_executesql N'@sql+'AND CategoryId = @Category'+'ORDER BY Id DESC'','@Category的值'
END
IF @Key <> ''
exec sp_executesql N'@sql+'AND Nick = @Key '+'ORDER BY Id DESC'','@Key的值'
declare @sql varchar(max)
set @sql='INSERT INTO @Product
SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product
WHERE Id > 0'IF @Category > 0
exec sp_executesql N'@sql+'AND CategoryId = @Category'+'ORDER BY Id DESC'','@Category的值'
END
IF @Key <> ''
exec sp_executesql N'@sql+'AND Nick = @Key '+'ORDER BY Id DESC'','@Key的值'
END
WHERE Id > 0
or (@Category > 0 AND CategoryId = @Category)
or (@Key <> ''AND Nick = @Key)
AND CategoryId = @Category
AND @Key <> ''
AND Nick = @Key
这样的话要是没参数就出不来数据了?
and (@Category = 0 or CategoryId = @Category) -- 假如@Category = 0, CategoryId = @Category这个条件就会忽略
and (@Key = '' or Nick = @Key) --同上
ORDER BY Id DESC
ALTER PROCEDURE [dbo].[sp_ProductGetList]
------------------------------------------------------------------------
--目的:常规返回产品列表(以分页形式)
--
--维护日志
--
--维护人 维护时间 描述
------------------- ------------------- ------------------------
--xuyiwei 2012-7-24 创建
------------------------------------------------------------------------
--分类Id
@Category INT,
--搜索关键字
@Key NVARCHAR(50),
--当前页码
@PageNumber INT,
--每页多少
@PageSize INT,
--返回总共多少行
@HowMany INT OUTPUTAS
-- 声明新的TABLE变量
create TABLE #Product
(
RowNumber INT,
Id INT,
Nick NVARCHAR(50),
NumIid bigint,
TrackIiid NVARCHAR(50),
ContactPhone NVARCHAR(50),
CategoryId INT,
CreateTime DATETIME,
ModifiedTime DATETIME,
Sorting INT,
EffectiveIntegral INT,
ShareIntegral INT
)
DECLARE @sql nvarchar(max)SET @sql='INSERT INTO #Product SELECT ROW_NUMBER() OVER (ORDER BY Product.Id),
Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM Product WHERE Id > 0 '
IF @Category > 0
begin
SET @sql=@sql+' AND CategoryId ='+convert(varchar(128),@Category)
endIF @Key <> ''
begin
SET @sql=@sql+' AND Nick ='''+ @Key+''' '
ENDSET @sql=@sql+' ORDER BY Id DESC '
print @sqlEXEC (@sql)-- 使用OUTPUT变量返回总共多少行
SELECT @HowMany = COUNT(Id) FROM #Product-- 获取请求的商品页面
SELECT Id, Nick,NumIid, TrackIiid,ContactPhone,CategoryId,CreateTime,ModifiedTime,Sorting,EffectiveIntegral,ShareIntegral
FROM #Product
WHERE RowNumber > (@PageNumber - 1) * @PageSize
AND RowNumber <= @PageNumber * @PageSize