SQL存储过程中SQL语句拼接
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