存储过程排序+模糊查询

USE [YanShu_IndexDW]
GO
/****** Object:  StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord]    Script Date: 2017/9/20 8:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[SP_GetZTCSearchPromotionPlanKeyWord]
@SellerId BIGINT ,--店铺ID
    @ParamName NVARCHAR(100), --模糊查询
@SortName NVARCHAR(100) ,--排序字段名称
    @SortType NVARCHAR(50) ,--排序规则
@PageNum INT --页数


   AS
     DECLARE @SQL NVARCHAR(4000) ,
        @SQLCount NVARCHAR(500) ,
        @SelectColumns NVARCHAR(500) ,
        @TmpStartRow NVARCHAR(10) ,
        @TmpPageSize NVARCHAR(10)


        BEGIN
  IF LEN(@SortName) = 0
            BEGIN
                SELECT  @SortName = '点击量' ,
                        @SortType = 'desc'
            END
  SELECT DISTINCT
                [keyWord] AS 关键词 ,
                CAST([computerGrade] AS INT) AS 计算机质量分 ,
                CAST([phoneGrade] AS INT) AS 移动质量分 ,
                [computerRanking] AS 计算机排名 ,
                [phoneRanking] AS 移动排名 ,
                CAST(REPLACE([impressions], ',', '') AS INT) AS 展现量 ,
                CAST(REPLACE([clickVolume], ',', '') AS INT) AS 点击量 ,
                CAST([clickRate] AS FLOAT) AS 点击率 ,
                CAST([expenditure] AS FLOAT) AS 花费 ,
                CAST([directDealMoney] AS FLOAT) AS 直接成交金额 ,
                CAST([meanClick] AS FLOAT) AS 平均点击花费 ,
                CAST([totalVolume] AS INT) AS 总成交笔数 ,
                CAST([averageShoWranking] AS INT) AS 平均展现排名 ,
                CAST([inputOutput] AS FLOAT) AS 投入产出比 ,
                CAST([directDealNum] AS INT) AS 直接成交笔数 ,
                CAST([totalTurnover] AS FLOAT) AS 总成交金额 ,
                CAST([clickConversion] AS FLOAT) AS 点击转化率 ,
                CAST([directTrolleyNum] AS INT) AS 直接购物数 ,
                CAST([enshrineComdityNum] AS INT) AS 收藏宝贝数 ,
                CAST([thousandShow] AS FLOAT) AS 千次展现花费 ,
                CAST([indirectTurnover] AS FLOAT) AS 间接成交金额 ,
                CAST([indirectDealNum] AS INT) AS 间接成交笔数 ,
                CAST([enshrineStoreNum] AS INT) AS 收藏店铺数 ,
                CAST([totalCollectionNum] AS INT) AS 总收藏数 ,
                CAST([toalTrolleyNum] AS INT) AS 总购物车数 ,
                CAST([indirectTrolleyNum] AS INT) AS 间接购物车数
       INTO    #TmpZTC_standard_promotion_plan_keyword_query
        FROM    [YanShu_IndexDW].[ztc].[ZTC_standard_promotion_plan_KeyWord](NOLOCK) 


SET @TmpStartRow = ( @PageNum - 1 ) * 10 
        SET @TmpPageSize = 10
--Select @SQL='select * from emp where name like ''%'+ @name +'%'''
SELECT  @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName+'%'' '





Select @SQL='select top  '+@TmpPageSize+'* from (SELECT  row_number() OVER(ORDER BY ' + @SortName + ' ' + @SortType + ' )AS RowNumber,* from #TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName    +'%'' ) a  WHERE  RowNumber>'+@TmpStartRow 


        PRINT @SQL
EXEC(@SQL)
EXEC(@SQLCount)
        


END