SQL存储过程中SQL语句拼接

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author:wgh

-- Create date: 2012-06-07

-- Description: IP访问预统计

-- =============================================

CREATE PROCEDURE [dbo].[PROC_HB_PreStatByIP]

    @BeginTime varchar(50),

    @EndTime varchar(50),

    @TimeType int,    --0代表小时  、 1代表天

    @ChannelID varchar(10)='0',

    @IpAddress varchar(20)

AS

BEGIN

    declare @sql1 nvarchar(2000)

    IF @TimeType = 0

    BEGIN

SET @sql1='SELECT CONVERT(varchar(13),AddTime,120) AS ''日期''

                  ,ChannelID AS ''推广渠道''

                  ,IPAddress AS ''IP地址''

                  ,ViewCount AS ''浏览次数''

                  ,ClickCount AS ''点击次数''

                  FROM dbo.HB_AdPreStatIP WHERE AddTime>='''+@BeginTime+''' AND AddTime<='''+@EndTIme+''''

IF @ChannelID <> '0'

        BEGIN

           SET @sql1 =@sql1 + ' AND ChannelID='''+@ChannelID+''''

        END

        IF @IpAddress <> ''

        BEGIN

          SET @sql1 =@sql1 +' AND IPAddress='''+@IpAddress+''''

        END

        SET @sql1 =@sql1+' ORDER BY ViewCount DESC'

    END

    ELSE

    BEGIN

SET @sql1='SELECT CONVERT(varchar(10),AddTime,120) AS ''日期''

                          ,ChannelID AS ''推广渠道''

                          ,IPAddress AS ''IP地址''

                          ,SUM(ViewCount)  AS ''浏览次数''

                          ,SUM(ClickCount)  AS ''点击次数'' 

                    FROM dbo.HB_AdPreStatIP WHERE AddTime>='''+@BeginTime+''' AND AddTime<='''+@EndTIme+''''

IF @ChannelID > '0'

        BEGIN

           SET @sql1 =@sql1 + ' AND ChannelID='''+@ChannelID+''''

        END

        IF @IpAddress <> ''

        BEGIN

          SET @sql1 =@sql1 +' AND IPAddress='''+@IpAddress+''''

        END

SET @sql1 =@sql1 + 'GROUP BY CONVERT(varchar(10),AddTime,120),ChannelID,IPAddress ORDER BY SUM(ViewCount) DESC'

    END

   EXEC sp_executesql @sql1 

END