SQL 저장 프로시저의 SQL 문 연결

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