模糊查询的存储过程

USE [YanShu_IndexDW]
GO
/****** Object:  StoredProcedure [dbo].[SP_GetZTCStandardPromotionPlanKeyWord]    Script Date: 2017/9/19 19:55:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetZTCStandardPromotionPlanKeyWord]
    @ParamName NVARCHAR(100), --模糊查询
@PageNum INT --页数
   AS
     DECLARE @SQL NVARCHAR(4000) ,
        @SQLCount NVARCHAR(500) ,
        @SelectColumns NVARCHAR(500) ,
        @TmpStartRow NVARCHAR(10) ,
        @TmpPageSize NVARCHAR(10)


        BEGIN


  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  @SQLCount = 'select count(1) countes from #TmpZTC_standard_promotion_plan_keyword_query'
Select @SQL='select top  '+@TmpPageSize+'* from (SELECT  row_number() OVER(ORDER BY 关键词)AS RowNumber,* from TmpZTC_standard_promotion_plan_keyword_query WHERE 关键词 like ''%'+ @ParamName    +'%'' ) a  WHERE  RowNumber>'+@TmpStartRow 


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


END


exec [SP_GetZTCStandardPromotionPlanKeyWord] '喇叭裤 女 长裤' ,1