T-SQL Examples – DANTEPRO.COM

Included below are some examples of hand-coded T-SQL SQL objects created including nested stored procedures, cursors, triggers, and DBMS / data-mart fact table transactional / extraction routines.

*** USER LOGIN STORED PROC FIRED OFF BY ASP SCRIPTLET

CREATE Proc asp_AddCustomer
@EmailAddress varchar(150),
@ServiceCode char(2)
As
Set NoCount OnUpdate Customer Set
Status = 4,
EditDate = Convert(char(12),GetDate(),1)
Where EmailAddress = @EmailAddressInsert Into Customer(EmailAddress,SubscriptionCode,SignupDate,Status,EditDate)
Values(@EmailAddress,@ServiceCode,Convert(char(12),GetDate(),1),1,Convert(char(12),GetDate(),1))

Set NoCount Off

*** STORED PROCEDURE SNIPPET [OF 1250 LINES OF CODE]
*** CALCULATES STATISTICS AND POPULATES DATAMART
*** CURSOR FIRED OFF BY TIMED TRIGGER

——————————————————
Delete YDayStatsSummary — Clears “Old” stats
SET DATEFIRST 1 — Sets Sunday as the first day of the week
——————————————————
— Cursor to perform statistical calculation routine on all “UserID’s”
Declare Hcpr Cursor For Select Distinct UserID From UserTable
Where SecurityID = ‘2’ AND Status = ‘1’
Open Hcpr
Fetch Next From Hcpr Into @HcprID
While @@Fetch_Status = 0
Begin
If Exists(Select UserID From UserTable
Where UserID = @HcprID
)
Begin
——————————————————
— Previous SEASON MLB STATS
SET @Temp = (
SELECT SUM(ALL dayNumofPicksMLBTotal)
FROM DailyStats d
Join UserTable u On d.HcprID = u.UserID
WHERE d.HcprID = @HcprID
AND d.StatDate >= @MLBSeasonStartPrevious
AND @MLBSeasonEndPrevious <= d.StatDate
)IF @Temp <> 0
BEGIN
SET @LastSeasonMLBWinP = (
SELECT
CAST(ROUND(SUM(ALL dayNumofWinsMLB)/SUM(ALL
dayNumofPicksMLBTotal)*100, 0) AS int)
FROM DailyStats d
Join UserTable u On d.HcprID = u.UserID
WHERE d.HcprID = @HcprID
AND d.StatDate >= @MLBSeasonStartPrevious
AND @MLBSeasonEndPrevious <= d.StatDate
)
END
ELSE
BEGIN
SET @LastSeasonMLBWinP = 0
END
SELECT
@LastSeasonMLBWins = SUM(ALL dayNumofWinsMLB),
@LastSeasonMLBLoss = SUM(ALL dayNumofLossMLB),
@LastSeasonMLBPicks = SUM(ALL dayNumofPicksMLBTotal)
FROM DailyStats d
Join UserTable u On d.HcprID = u.UserID
WHERE d.HcprID = @HcprID
AND d.StatDate >= @MLBSeasonStartPrevious
AND @MLBSeasonEndPrevious <= d.StatDate
——————————————————
Insert Into YDayStatsSummary(
HcprID, ThisWeekWins, ThisWeekLoss, ThisWeekPicks, ThisWeekWinP,
… )
Values(
@LastSeasonCBBWins, @LastSeasonCBBLoss, @LastSeasonCBBPicks, @LastSeasonCBBWinP,
… )
——————————————————
END
— Start Loop Again
Fetch Next From Hcpr Into @HcprID
END — End LoopClose Hcpr — Close Cursor
Deallocate Hcpr
— END

*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE]
*** AUTO EMAILS SPECIFIED USERS THAT HAVE NOT COMPLETED A REQUIRED TASK
*** EMAILER FIRED OFF BY TIMED TRIGGER

— EMAIL ADMIN u0026amp; HANDICAPPERS CODE
DECLARE @em1 varchar(80), @bdy1 varchar(100),
@queryCode varchar(980), @subj varchar(85), @cpy_recpt varchar(28)SET @em1 = ‘user1@company.com;user2@company.com;user3@company.com’
SET @bdy1 = ‘SGC SYSTEM WARNING: Please Update Previous Pick Results ASAP. Do Not reply to this email.’
SET @subj = ‘SGC SYSTEM WARNING: Previous Pick Results Require Immediate Attention’
SET @cpy_recpt = ‘user4@company.com’
SET @queryCode = ‘SELECT Convert(char(11),p.Play_Date) as Play_Date,
Pick_Sport, u.FirstName AS HndCpr,
SGCTM.TM_Name as FavoriteTeam,
HMTEAM.TM_Name as HomeTeam,
AWAYTM.TM_Name as AwayTeam,
win_loss AS Result, PickType, PickSign, Pick_Spread
FROM SportsTeller..Picks p
Join SportsTeller..UserTable u On p.UserID = u.UserID
INNER JOIN SportsTeller..Teams AS HMTEAM ON p.HM_TM_ID = HMTEAM.Team_ID
INNER JOIN SportsTeller..Teams AS AWAYTM ON p.Opp_ID = AWAYTM.Team_ID
INNER JOIN SportsTeller..Teams AS SGCTM ON p.SGCPickTM = SGCTM.Team_ID
WHERE win_loss = 0 ORDER BY Play_Date ASC’

EXEC master.dbo.xp_sendmail
@recipients = @em1,
@message = @bdy1,
@query = @queryCode,
@subject = @subj,
@copy_recipients = @cpy_recpt

PRINT ‘Request to Update Pick Results Have Been Emailed’

*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE]
*** CALCULATES STATISTICS AND RETURNS TO ASP PAGE
*** ROUTINE FIRED ON REQUEST BY ASP SCRIPTLET

—————————————-
CREATE PROCEDURE Get_AllCurrentCBBSeasonStats ASDECLARE @CBBSeasonStart datetime
DECLARE @CBBSeasonEnd datetime
SET @CBBSeasonStart = (SELECT Season_Start FROM Seasons
WHERE Sport = ‘CBB’ AND
Season_Type = ‘Regular’ AND
Season = ‘Current’)

SET @CBBSeasonEnd = (SELECT Season_End FROM Seasons
WHERE Sport = ‘CBB’ AND
Season_Type = ‘Regular’ AND
Season = ‘Current’)

SELECT d.HcprID, u.FirstName AS HndCpr,
SUM(ALL dayNumofWinsCBB) AS CBBSeasonWins,
SUM(ALL dayNumofLossCBB) AS CBBSeasonLoss,
SUM(ALL dayNumofPicksCBBTotal) AS CBBSeasonPicksTotal,
CAST(ROUND(SUM(ALL dayNumofWinsCBB)/SUM(ALL dayNumofPicksCBBTotal)*100, 0) AS int)
AS [CBBWin% This Season]

From DailyStats d
Join UserTable u On d.HcprID = u.UserID

WHERE
d.StatDate >= @CBBSeasonStart AND
@CBBSeasonEnd > GetDate()
GROUP BY u.FirstName, HcprID
GO

*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE]
*** CALCULATES DAILY WEBSITE HITS (FROM SRVR LOGS) AND POPULATES DATAMART
*** ROUTINE FIRED OFF BY TIMED TRIGGER

—————————————-

CREATE PROC usp_updateSGCLogsdaily AS

DECLARE @StartDate Char(11) — e.g. Mar 15 2003
DECLARE @avail numeric

BEGIN TRANSACTION

SET @StartDate = (SELECT MAX(Convert(char(11),LogTime)) FROM SGCLogs..IISLog)
SET @avail = (SELECT Count(*) FROM IISLogs..IISLog i
HAVING MAX(Convert(char(11),i.LogTime)) > @StartDate)

IF @avail <> ‘0’
BEGIN

INSERT INTO SGCLogs..IISLog
SELECT *
FROM IISLogs..IISLog i
WHERE i.LogTime > @StartDate

IF (@@ERROR <> 0) GOTO on_error

— DELETE
— FROM IISLogs..IISLog i
— WHERE i.LogTime > @StartDate

— IF (@@ERROR <> 0) GOTO on_error

COMMIT TRANSACTION
SELECT ‘Weekly Hits and Sessions summary succeeded.’
RETURN (0)

on_error:
ROLLBACK TRANSACTION
SELECT ‘Error – Weekly Hits and Sessions summary aborted.’
RETURN (1)
END
GO

*** STORED PROCEDURE SNIPPET [OF xxxx LINES OF CODE]
*** CALCULATES WEEKLY MULTI-WEBSITE TRAFFIC + SESSION TOTALS (FROM SRVR LOGS) AND
*** POPULATES DATAMART
*** ROUTINE FIRED OFF BY TIMED TRIGGER

—————————————-

BEGIN TRANSACTION

SELECT @ToYear = DATEPART(year, GETDATE()),
@ToWeek = DATEPART(week, GETDATE())

SELECT @ThisYear = MAX(TYearNumber),
@ThisWeek = MAX(TWeekNumber)
FROM WeekSummary

SELECT @ThisWeek = @ThisWeek + 1
IF (@ThisWeek = 54)
BEGIN
SELECT @ThisWeek = 1
SELECT @ThisYear = @ThisYear + 1
END

WHILE (@ThisYear = @ToYear AND @ThisWeek < @ToWeek) OR (@ThisYear < @ToYear)
BEGIN
SELECT @WDHits = SUM(THitCount),
@WDKBytes = SUM(TKBytes)
FROM DaySummary
WHERE DATEPART(year, TSumDate) = @ThisYear
AND DATEPART(week, TSumDate) = @ThisWeek
AND TSiteIP = ‘192.168.0.228’

IF (@@ERROR <> 0) GOTO on_error

SELECT @WDSessions = COUNT(EventDateTime)
FROM Sessions
WHERE DATEPART(year, EventDateTime) = @ThisYear
AND DATEPART(week, EventDateTime) = @ThisWeek
AND EventType = ‘New Session’
AND HostIP = ‘192.168.0.228’

IF (@@ERROR <> 0) GOTO on_error

SELECT @CDHits = SUM(THitCount),
@CDKBytes = SUM(TKBytes)
FROM DaySummary
WHERE DATEPART(year, TSumDate) = @ThisYear
AND DATEPART(week, TSumDate) = @ThisWeek
AND TSiteIP = ‘192.168.0.229’

IF (@@ERROR <> 0) GOTO on_error

SELECT @CDSessions = COUNT(EventDateTime)
FROM Sessions
WHERE DATEPART(year, EventDateTime) = @ThisYear
AND DATEPART(week, EventDateTime) = @ThisWeek
AND EventType = ‘New Session’
AND HostIP = ‘192.168.0.229’

IF (@@ERROR <> 0) GOTO on_error

SELECT @WAHits = SUM(THitCount),
@WAKBytes = SUM(TKBytes)
FROM DaySummary
WHERE DATEPART(year, TSumDate) = @ThisYear
AND DATEPART(week, TSumDate) = @ThisWeek
AND TSiteIP = ‘dtpro.dantepro.com’
— AND TSiteIP = ‘192.168.0.230’

IF (@@ERROR <> 0) GOTO on_error

SELECT @WASessions = COUNT(EventDateTime)
FROM Sessions
WHERE DATEPART(year, EventDateTime) = @ThisYear
AND DATEPART(week, EventDateTime) = @ThisWeek
AND EventType = ‘New Session’
AND HostIP = ‘192.168.0.230’

IF (@@ERROR <> 0) GOTO on_error

INSERT INTO WeekSummary
SELECT @ThisYear,
@ThisWeek,
ISNULL(@WDHits, 0),
ISNULL(@WDKBytes, 0),
ISNULL(@CDHits, 0),
ISNULL(@CDKBytes, 0),
ISNULL(@WAHits, 0),
ISNULL(@WAKBytes, 0),
ISNULL(@WDSessions, 0),
ISNULL(@CDSessions, 0),
ISNULL(@WASessions, 0)

IF (@@ERROR <> 0) GOTO on_error

SELECT @ThisWeek = @ThisWeek + 1
IF (@ThisWeek = 54)
BEGIN
SELECT @ThisWeek = 1
SELECT @ThisYear = @ThisYear + 1
END
END

INSERT INTO RefererSummary
SELECT RefYear = MAX(DATEPART(year, EventDateTime)),
RefWeek = MAX(DATEPART(week, EventDateTime)),
RefURL=MAX(
CASE WHEN CHARINDEX(‘?’, Referer) > 5
THEN SUBSTRING(Referer, 1, CHARINDEX(‘?’, Referer) – 1)
ELSE Referer
END),
RefCount=COUNT(Referer),
RefHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
AND Referer IS NOT NULL AND Referer <> ”
AND CHARINDEX(‘.wrox.co’, Referer) = 0
AND CHARINDEX(‘file:’, Referer) = 0
GROUP BY CASE WHEN CHARINDEX(‘?’, Referer) > 5
THEN SUBSTRING(Referer, 1, CHARINDEX(‘?’, Referer) – 1)
ELSE Referer
END,
DATEPART(year, EventDateTime),
DATEPART(week, EventDateTime),
HostIP

IF (@@ERROR <> 0) GOTO on_error
INSERT INTO SessionTargetSummary
SELECT TargYear = MAX(DATEPART(year, EventDateTime)),
TargWeek = MAX(DATEPART(week, EventDateTime)),
TargText = MAX(URL),
TargCount = COUNT(URL),
TargHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
AND URL IS NOT NULL
AND URL <> ”
GROUP BY URL,
DATEPART(year, EventDateTime),
DATEPART(week, EventDateTime),
HostIP

IF (@@ERROR <> 0) GOTO on_error

INSERT INTO UserAgentSummary
SELECT UAYear = MAX(DATEPART(year, EventDateTime)),
UAWeek = MAX(DATEPART(week, EventDateTime)),
UAText = MAX(UserAgent),
UACount = COUNT(UserAgent),
UAHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
AND UserAgent IS NOT NULL
AND UserAgent <> ”
GROUP BY UserAgent,
DATEPART(year, EventDateTime),
DATEPART(week, EventDateTime),
HostIP

IF (@@ERROR <> 0) GOTO on_error

INSERT INTO CountrySummary
SELECT CoYear = MAX(DATEPART(year, EventDateTime)),
CoWeek = MAX(DATEPART(week, EventDateTime)),
CoText = MAX(
CASE WHEN CHARINDEX(‘,’, UALanguage) > 1
THEN SUBSTRING(UALanguage, 1, CHARINDEX(‘,’, UALanguage) – 1)
ELSE UALanguage
END),
CoCount = COUNT(UALanguage),
CoHostIP = MAX(HostIP)
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0
AND UALanguage IS NOT NULL
AND UALanguage <> ”
GROUP BY CASE WHEN CHARINDEX(‘,’, UALanguage) > 1
THEN SUBSTRING(UALanguage, 1, CHARINDEX(‘,’, UALanguage) – 1)
ELSE UALanguage
END,
DATEPART(year, EventDateTime),
DATEPART(week, EventDateTime),
HostIP

IF (@@ERROR <> 0) GOTO on_error

DELETE
FROM Sessions
WHERE DATEDIFF(week, EventDateTime, GETDATE()) > 0

IF (@@ERROR <> 0) GOTO on_error

COMMIT TRANSACTION
SELECT ‘Weekly Hits and Sessions summary succeeded.’
RETURN (0)

on_error:
ROLLBACK TRANSACTION
SELECT ‘Error – Weekly Hits and Sessions summary aborted.’RETURN (1)
GO

** Data Tool Used to Populate OLAP TIME Dimension Table

CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GOSET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = ‘1/1/2005’, @x = 1

–Add WeekEnds
WHILE @x < 52
BEGIN
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT DATEADD(ww,@x,@FirstSat), ‘SAT’ UNION ALL
SELECT DATEADD(ww,@x,@FirstSat+1), ‘SUN’
SELECT @x = @x + 1
END
SET NOCOUNT OFF
GO

— SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
— GO

— Add 2005 US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName, IsHoliday, IsUSFedHoliday, Comments)
SELECT ‘1/1/2005’, ‘THU’, ‘1’, ‘1’, ‘New Years Day’ UNION ALL —
SELECT ‘1/17/2005’, ‘MON’, ‘1’, ‘1’, ‘Birthday of Martin Luther King’ UNION ALL —
SELECT ‘2/21/2005’, ‘MON’, ‘1’, ‘1’, ‘Washingtons Birthday’ UNION ALL —
SELECT ‘5/30/2005’, ‘MON’, ‘1’, ‘1’, ‘Memorial Day’ UNION ALL —
SELECT ‘7/4/2005’, ‘MON’, ‘1’, ‘1’, ‘Independence Day’ UNION ALL —
SELECT ‘9/5/2005’, ‘MON’, ‘1’, ‘1’, ‘Labor Day’ UNION ALL —
SELECT ’10/10/2005′, ‘MON’, ‘1’, ‘1’, ‘Columbus Day’ UNION ALL —
SELECT ’11/11/2005′, ‘FRI’, ‘1’, ‘1’, ‘Veterans Day’ UNION ALL —
SELECT ’11/24/2005′, ‘THU’, ‘1’, ‘1’, ‘Thanksgiving Day’ UNION ALL —
SELECT ’12/26/2005′, ‘MON’, ‘1’, ‘1’, ‘Christmas Day’
GO

— SELECT * FROM WeekEndsAndHolidays
— Update WeekEndsAndHolidays Set IsUSFedHoliday = ‘0’
— Where IsUSFedHoliday IS NULL

— Update WeekEndsAndHolidays Set Comments = ”
— Where Comments IS NULL

— WeekDays in May
DECLARE @Start datetime, @End datetime
SELECT @Start = ‘5/1/2004’, @End = ‘6/1/2004’

SELECT DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
FROM WeekEndsAndHolidays
WHERE DayOfWeekDate BETWEEN @Start AND @End
GO

** DATAPUMP SCRIPT – Created to Extract, Transform, u0026amp; Load OLTP Data into a Data Mart for OLTP Analytics

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Time]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Time]
GOCREATE TABLE [dbo].[Time] (
[TimeID] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[SeasonID] [int] NOT NULL ,
[DayOfWeek] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Month] [numeric](10, 0) NULL ,
[Day] [numeric](18, 0) NULL ,
[Year] [numeric](10, 0) NULL ,
[Quarter] [numeric](10, 0) NULL ,
[DayOfYear] [numeric](10, 0) NULL ,
[Holiday] [numeric](18, 0) NULL ,
[Weekend] [numeric](18, 0) NULL ,
[YearMonth] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WeekOfYear] [numeric](10, 0) NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
————————————————–
DECLARE @PickNum int
DECLARE @SeasonID int
DECLARE @Date datetime
DECLARE @DayOfWeek char(20)
DECLARE @YearMonth char(10)
DECLARE @Day numeric
DECLARE @Year numeric
DECLARE @Quarter numeric
DECLARE @DayOfYear numeric
DECLARE @Holiday numeric
DECLARE @Weekend numeric
DECLARE @WeekOfYear numeric
DECLARE @Month numeric

————————————————–

Declare cPicks Cursor For
Select
p.Pick_Num AS [Pick#],
p.SeasonID AS [SeasonID],
p.Play_Date AS [Date],
DATENAME ( dw , p.Play_Date ) AS DayOfWeek,
DATENAME ( mm , p.Play_Date ) AS YearMonth,
DATEPART ( dd , p.Play_Date ) AS [Day],
DATEPART ( yy , p.Play_Date ) AS [Year],
DATEPART ( qq , p.Play_Date ) AS [Quarter],
DATEPART ( dy , p.Play_Date ) AS DayOfYear,
Holiday = (Select COUNT(*) From SGCDataMart..WeekEndsAndHolidays Where DayOfWeekDate = p.Play_Date),
DATEPART ( wk , p.Play_Date ) AS WeekOfYear,
DATEPART ( mm , p.Play_Date ) AS [Month]
From SportsTeller..Picks p
— Select * From SportsTeller..Picks p
————————————————–

Open cPicks
Fetch Next From cPicks
Into
@PickNum, @SeasonID, @Date, @DayOfWeek,
@YearMonth, @Day, @Year, @Quarter, @DayOfYear,
@Holiday, @WeekOfYear, @Month

While @@Fetch_Status = 0
Begin

————————————————–

— Select
— @PickNum, @SeasonID, @Date, @DayOfWeek,
— @YearMonth, @Day, @Year, @Quarter, @DayOfYear,
— @Holiday, @WeekOfYear, @Month
— Select * From Time
— Delete From Time

INSERT INTO SGCDataMart..Time
(TimeID, SeasonID, [Date], DayOfWeek,
YearMonth, [Day], [Year], [Quarter], DayOfYear,
Holiday, WeekOfYear, [Month] — Weekend,
)

VALUES
(@PickNum, @SeasonID, @Date, @DayOfWeek,
@YearMonth, @Day, @Year, @Quarter, @DayOfYear,
@Holiday, @WeekOfYear, @Month
)

— END — Start Loop Again

————————————————-

Fetch Next From cPicks
Into
@PickNum, @SeasonID, @Date, @DayOfWeek,
@YearMonth, @Day, @Year, @Quarter, @DayOfYear,
@Holiday, @WeekOfYear, @Month
END — End Loop

Close cPicks — Close Cursor
Deallocate cPicks

————————————————–

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
————————————————–

Update Time
Set Time.Holiday = ‘1’ — Select *
From Time t, WeekEndsAndHolidays w
Where t.Date = w.DayOfWeekDate AND w.IsHoliday = ‘1’
GO

Update Time
Set Time.Weekend = ‘1’ — Select *
From Time t, WeekEndsAndHolidays w
Where t.Date = w.DayOfWeekDate AND w.DayName = ‘SAT’ OR w.DayName = ‘SUN’
GO
————————————————–