SQL存储过程中SQL语句拼接

SQL存储过程中SQL语句拼接

转载自http://blog.sina.com.cn/s/blog_a525ba0201016klz.html

 

当作例子，看懂。 

 

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