SQL 저장 프로시저의 SQL 문 연결
- 高洛峰원래의
- 2016-12-14 15:22:591750검색
ANSI_NULLS 설정
QUOTED_IDENTIFIER 설정
이동
-- =================== =========================
-- 작성자:wgh
-- 작성 날짜: 2012-06 -07
-- 설명: IP 접속 사전 통계
-- ========================= = ===================
프로시저 생성 [dbo].[PROC_HB_PreStatByIP]
@BeginTime varchar(50),
@EndTime varchar(50),
@TimeType int, --0은 시간을 나타내고, 1은 일을 나타냅니다.
@ChannelID varchar(10)='0',
@IpAddress varchar(20)
AS
BEGIN
@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