sql 存储过程分页

Question

ALTER PROCEDURE [dbo].[SP_TSM_GetProductByPage]
(
@PageSize INT,
@CurrentPage INT,
@OrderBy VARCHAR(100),
@ObjType INT,
@ScoreType INT,
@IsPublish INT,
@KeyWords VARCHAR(100)
)
AS
SELECT TOP @PageSize [ProductGUID] ,[CategoryGUID] ,[Title] ,[ProductImage] ,[TSM_Member_Product].[CreateTime]
FROM 
(
SELECT [ProductGUID] ,[CategoryGUID] ,[Title] ,[ProductImage] ,[TSM_Member_Product].[CreateTime] ,row_number() OVER(ORDER BY @OrderBy ) POS 
FROM  [TSM_Member_Product]  ,[TSM_Member] ,[TSM_ScoreCollection]
WHERE [TSM_Member_Product].[MemberGUID] = [TSM_Member].[MemberGUID]
AND [TSM_Member_Product].[MemberGUID] = [TSM_ScoreCollection].[objID]
AND [TSM_ScoreCollection].[ObjType] = @ObjType
AND [TSM_ScoreCollection].[ScoreType] = @ScoreType
AND [TSM_Member_Product].[IsPublish] = @IsPublish
AND [TSM_Member_Product].[Title] LIKE %@KeyWords%
)  
[TSM_Member_Product] ,[TSM_Member] ,[TSM_ScoreCollection]
WHERE POS > ((@CurrentPage - 1) * @PageSize) 
AND POS <=  (@CurrentPage * @PageSize) 请问一下怎么写正确 谢谢

Answer 1

ALTER PROCEDURE [dbo].[SP_TSM_GetProductByPage]
(
    @PageSize INT, 
    @CurrentPage INT, 
    @OrderBy VARCHAR(100), 
    @ObjType INT, 
    @ScoreType INT, 
    @IsPublish INT, 
    @KeyWords VARCHAR(100)
)
AS
SELECT TOP (@PageSize) [ProductGUID], [CategoryGUID], [Title], [ProductImage], 
       [TSM_Member_Product].[CreateTime] 
FROM (
         SELECT [ProductGUID], [CategoryGUID], [Title], [ProductImage], [TSM_Member_Product].[CreateTime], 
                ROW_NUMBER() OVER(ORDER BY @OrderBy) POS 
         FROM [TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
         WHERE [TSM_Member_Product].[MemberGUID] = [TSM_Member].[MemberGUID]
          AND [TSM_Member_Product].[MemberGUID] = [TSM_ScoreCollection].[objID]
          AND [TSM_ScoreCollection].[ObjType] = @ObjType
          AND [TSM_ScoreCollection].[ScoreType] = @ScoreType
          AND [TSM_Member_Product].[IsPublish] = @IsPublish
          AND [TSM_Member_Product].[Title] LIKE '%'+@KeyWords+'%'
     ) [TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
WHERE POS>((@CurrentPage- 1)*@PageSize)
AND POS<= (@CurrentPage*@PageSize) 
GO只处理语法

Answer 2

SELECT A.[ProductGUID] ,A.[CategoryGUID] ,A.[Title] ,A.[ProductImage] ,A.[CreateTime]FROM  
(
SELECT [ProductGUID] ,[CategoryGUID] ,[Title] ,[ProductImage] ,[TSM_Member_Product].[CreateTime],
row_number() OVER(ORDER BY @OrderBy ) POS  
FROM [TSM_Member_Product] ,[TSM_Member] ,[TSM_ScoreCollection]
WHERE [TSM_Member_Product].[MemberGUID]  =  [TSM_Member].[MemberGUID]
AND  [TSM_Member_Product].[MemberGUID]  =  [TSM_ScoreCollection].[objID]
AND  [TSM_ScoreCollection].[ObjType]  =  @ObjType
AND  [TSM_ScoreCollection].[ScoreType]  =  @ScoreType
AND  [TSM_Member_Product].[IsPublish]  =  @IsPublish
AND  [TSM_Member_Product].[Title]  LIKE %@KeyWords%
)AWHERE A.POS > ((@CurrentPage - 1) * @PageSize)  
AND  A.POS <= (@CurrentPage * @PageSize)

Answer 3

ALTER PROCEDURE [dbo].[SP_TSM_GetProductByPage]
(
    @PageSize INT, 
    @CurrentPage INT, 
    @OrderBy VARCHAR(100), 
    @ObjType INT, 
    @ScoreType INT, 
    @IsPublish INT, 
    @KeyWords VARCHAR(100)
)
AS
DECLARE @s VARCHAR(MAX)
SET @s='
SELECT TOP ('+LTRIM(@PageSize)+') [ProductGUID], [CategoryGUID], [Title], [ProductImage], 
       [TSM_Member_Product].[CreateTime] 
FROM (
         SELECT [ProductGUID], [CategoryGUID], [Title], [ProductImage], [TSM_Member_Product].[CreateTime], 
                ROW_NUMBER() OVER(ORDER BY ['+@OrderBy+') POS 
         FROM [TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
         WHERE [TSM_Member_Product].[MemberGUID] = [TSM_Member].[MemberGUID]
          AND [TSM_Member_Product].[MemberGUID] = [TSM_ScoreCollection].[objID]
          AND [TSM_ScoreCollection].[ObjType] ='''+ LTRIM(@ObjType)+'''
          AND [TSM_ScoreCollection].[ScoreType] ='''+ LTRIM(@ScoreType)+'''
          AND [TSM_Member_Product].[IsPublish] ='''+ LTRIM(@IsPublish)+'''
          AND [TSM_Member_Product].[Title] LIKE ''%'+@KeyWords+'%''
     ) [TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
WHERE POS>'+LTRIM((+@CurrentPage- 1)*@PageSize)+'
AND POS<= '+LTRIM(@CurrentPage*@PageSize) 
EXEC(@s)
GO修改

Answer 4

ALTER PROCEDURE [dbo].[SP_TSM_GetProductByPage]
(
@PageSize INT,
@CurrentPage INT,
@OrderBy VARCHAR(100),
@Category CHAR(36),
@Area VARCHAR(50),
@ObjType INT,
@ScoreType INT,
@IsPublish INT,
@KeyWords VARCHAR(100)
)
ASSELECT TOP (@PageSize) [ProductGUID], [CategoryGUID], [Title], [ProductImage],[TSM_Member_Product].[CreateTime] 
FROM (
SELECT [ProductGUID], [CategoryGUID], [Title], [ProductImage], [TSM_Member_Product].[CreateTime], 
ROW_NUMBER() OVER(ORDER BY @OrderBy) POS 
FROM [TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
WHERE [TSM_Member_Product].[MemberGUID] = [TSM_Member].[MemberGUID]
AND [TSM_Member_Product].[MemberGUID] = [TSM_ScoreCollection].[objID]
AND [TSM_Member_Product].[CategoryGUID] = @Category
AND [TSM_ScoreCollection].[ObjType] = @ObjType
AND [TSM_ScoreCollection].[ScoreType] = @ScoreType
AND [TSM_Member_Product].[IsPublish] = @IsPublish
AND [TSM_Member_Product].[Title] LIKE '%'+@KeyWords+'%'
) 
[TSM_Member_Product], [TSM_Member], [TSM_ScoreCollection]
WHERE POS > ((@CurrentPage - 1) * @PageSize)
AND POS <= (@CurrentPage * @PageSize) 
问一下我如果想在条件里加条件分支也就是说当我一个条件的数值为空的话，就不把他当条件放进去应该怎么做