欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  php教程

SQL存储过程中SQL语句拼接

程序员文章站 2022-04-20 21:53:20
...
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

相关标签: SQL存储过程