CREATE XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection] AS
'
Home|Work|PermanentHigh School|Associate|Bachelor|Master|DoctorateIn case the institution does not follow a GPA systemISO 3166 Country CodeVoice|Fax|Pager';
ALTER XML SCHEMA COLLECTION [Production].[ProductDescriptionSchemaCollection] ADD
'
Product description has a summary blurb, if its manufactured elsewhere it
includes a link to the manufacturers site for this component.
Then it has optional zero or more sequences of features, pictures, categories
and technical specifications.
Pictures of the component, some standard sizes are "Large" for zoom in, "Small" for a normal web page and "Thumbnail" for product listing pages.Features of the component that are more "sales" oriented.A single technical aspect of the component.A single categorization element that designates a classification taxonomy and a code within that classification type. Optional description for default display if needed.';
-- uspPrintError prints error information about the error that caused
-- execution to jump to the CATCH block of a TRY...CATCH construct.
-- Should be executed from within the scope of a CATCH block otherwise
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError]
AS
BEGIN
SET NOCOUNT ON;
-- Print error information.
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END;
Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
PersonType
[nchar](2) NOT NULL
//
0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
NameStyle
[NameStyle] NOT NULL DEFAULT ((0))
//
A courtesy title. For example, Mr. or Ms.
Title
[nvarchar](8) NULL
//
First name of the person.
FirstName
[Name] NOT NULL
//
Middle name or middle initial of the person.
MiddleName
[Name] NULL
//
Last name of the person.
LastName
[Name] NOT NULL
//
Surname suffix. For example, Sr. or Jr.
Suffix
[nvarchar](10) NULL
//
0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.
EmailPromotion
[int] NOT NULL DEFAULT ((0))
//
Additional contact information about the person stored in xml format.
AdditionalContactInfo
[xml] NULL
//
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
Demographics
[xml] NULL
//
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
rowguid
[uniqueidentifier] NOT NULL DEFAULT (newid())
//
Date and time the record was last updated.
ModifiedDate
[datetime] NOT NULL DEFAULT (getdate())
Indexes
//
Unique nonclustered index. Used to support replication samples.
AK_Person_rowguid
UNIQUE
rowguid
IX_Person_LastName_FirstName_MiddleName
LastName , FirstName , MiddleName
//
Clustered index created by a primary key constraint.
Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)
CK_Person_EmailPromotion
EmailPromotion
([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
//
Check constraint [PersonType] is one of SC, VC, IN, EM or SP.
CK_Person_PersonType
PersonType
([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))
Sample Data
BusinessEntityID
PersonType
NameStyle
Title
FirstName
MiddleName
LastName
Suffix
EmailPromotion
AdditionalContactInfo
Demographics
rowguid
ModifiedDate
1
EM
false
null
Ken
J
Sánchez
null
0
[xml]
[xml]
92C4279F-1207-48A3-8448-4636514EB7E2
Tue Jan 06 2009 19:00:00 GMT-0500 (Eastern Standard Time)
2
EM
false
null
Terri
Lee
Duffy
null
1
[xml]
[xml]
D8763459-8AA8-47CC-AFF7-C9079AF79033
Wed Jan 23 2008 19:00:00 GMT-0500 (Eastern Standard Time)
3
EM
false
null
Roberto
null
Tamburello
null
0
[xml]
[xml]
E1A2555E-0828-434B-A33B-6F38136A37DE
Sat Nov 03 2007 20:00:00 GMT-0400 (Eastern Daylight Time)
4
EM
false
null
Rob
null
Walters
null
0
[xml]
[xml]
F2D7CE06-38B3-4357-805B-F4B6B71C01FF
Tue Nov 27 2007 19:00:00 GMT-0500 (Eastern Standard Time)
5
EM
false
Ms.
Gail
A
Erickson
null
0
[xml]
[xml]
F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF
Sat Dec 29 2007 19:00:00 GMT-0500 (Eastern Standard Time)
6
EM
false
Mr.
Jossef
H
Goldberg
null
0
[xml]
[xml]
0DEA28FD-EFFE-482A-AFD3-B7E8F199D56F
Sun Dec 15 2013 19:00:00 GMT-0500 (Eastern Standard Time)
7
EM
false
null
Dylan
A
Miller
null
2
[xml]
[xml]
C45E8AB8-01BE-4B76-B215-820C8368181A
Sat Jan 31 2009 19:00:00 GMT-0500 (Eastern Standard Time)
8
EM
false
null
Diane
L
Margheim
null
0
[xml]
[xml]
A948E590-4A56-45A9-BC9A-160A1CC9D990
Sun Dec 21 2008 19:00:00 GMT-0500 (Eastern Standard Time)
9
EM
false
null
Gigi
N
Matthew
null
0
[xml]
[xml]
5FC28C0E-6D36-4252-9846-05CAA0B1F6C5
Thu Jan 08 2009 19:00:00 GMT-0500 (Eastern Standard Time)
10
EM
false
null
Michael
null
Raheem
null
2
[xml]
[xml]
CA2C740E-75B2-420C-9D4B-E3CBC6609604
Sat Apr 25 2009 20:00:00 GMT-0400 (Eastern Daylight Time)
Wed Dec 13 2017 08:58:03 GMT-0500 (Eastern Standard Time)
[hierarchyid]
1
Overview
217
true
Overview
0
0
2
null
[varbinary]
26A266F1-1D23-40E2-AF48-6AB8D954FE37
Wed Dec 13 2017 08:58:03 GMT-0500 (Eastern Standard Time)
[hierarchyid]
2
Introduction 1
219
false
Introduction 1.doc
.doc
4
28
2
null
[varbinary]
48265174-8451-4967-973A-639C2276CFAF
Wed May 29 2013 20:00:00 GMT-0400 (Eastern Daylight Time)
[hierarchyid]
2
Repair and Service Guidelines
220
false
Repair and Service Guidelines.doc
.doc
0
8
2
It is important that you maintain your bicycle and keep it in good repair. Detailed repair and service guidelines are provided along with instructions for adjusting the tightness of the suspension fork.
[varbinary]
7ED4DEF5-D5BB-4818-8748-5BB5F8315FA2
Sun Mar 30 2008 20:00:00 GMT-0400 (Eastern Daylight Time)
[hierarchyid]
1
Maintenance
217
true
Maintenance
0
0
2
null
[varbinary]
5184D96A-EE8C-499A-9316-625496784DE6
Wed Dec 13 2017 08:58:04 GMT-0500 (Eastern Standard Time)
[hierarchyid]
2
Crank Arm and Tire Maintenance
219
false
Crank Arm and Tire Maintenance.doc
.doc
0
0
1
null
[varbinary]
D11B82B0-EB40-4A66-8193-15F765A61F05
Fri Oct 19 2012 05:56:38 GMT-0400 (Eastern Daylight Time)
[hierarchyid]
2
Lubrication Maintenance
219
false
Lubrication Maintenance.doc
.doc
2
11
1
Guidelines and recommendations for lubricating the required components of your Adventure Works Cycles bicycle. Component lubrication is vital to ensuring a smooth and safe ride and should be part of your standard maintenance routine. Details instructions are provided for each bicycle component requiring regular lubrication including the frequency at which oil or grease should be applied.
[varbinary]
2A8BD319-36FB-4FBA-9E07-E303839F1974
Wed May 29 2013 20:00:00 GMT-0400 (Eastern Daylight Time)
[hierarchyid]
1
Assembly
217
true
Assembly
0
0
2
null
[varbinary]
F4F544AE-381A-4323-9A59-F5A629E21F46
Wed Dec 13 2017 08:58:04 GMT-0500 (Eastern Standard Time)
[hierarchyid]
2
Front Reflector Bracket and Reflector Assembly 3
220
false
Front Reflector Bracket and Reflector Assembly 3.doc
.doc
3
25
2
null
[varbinary]
82F61D73-0BF5-44FF-AB99-DD73AEE9B9CC
Sun Apr 29 2012 20:00:00 GMT-0400 (Eastern Daylight Time)
[hierarchyid]
2
Front Reflector Bracket Installation
220
false
Front Reflector Bracket Installation.doc
.doc
1
15
2
Reflectors are vital safety components of your bicycle. Always ensure your front and back reflectors are clean and in good repair. Detailed instructions and illustrations are included should you need to replace the front reflector or front reflector bracket of your Adventure Works Cycles bicycle.
[varbinary]
52F6BE23-5363-4D93-96AE-F714B851E52C
Sun Apr 29 2012 20:00:00 GMT-0400 (Eastern Daylight Time)
Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL
CK_Product_Class
Class
(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
//
Check constraint [DaysToManufacture] >= (0)
CK_Product_DaysToManufacture
DaysToManufacture
([DaysToManufacture]>=(0))
//
Check constraint [ListPrice] >= (0.00)
CK_Product_ListPrice
ListPrice
([ListPrice]>=(0.00))
//
Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
CK_Product_ProductLine
ProductLine
(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
//
Check constraint [ReorderPoint] > (0)
CK_Product_ReorderPoint
ReorderPoint
([ReorderPoint]>(0))
//
Check constraint [SafetyStockLevel] > (0)
CK_Product_SafetyStockLevel
SafetyStockLevel
([SafetyStockLevel]>(0))
//
Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL
CK_Product_SellEndDate
TABLE
([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
//
Check constraint [SafetyStockLevel] > (0)
CK_Product_StandardCost
StandardCost
([StandardCost]>=(0.00))
//
Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL
CK_Product_Style
Style
(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
This bike delivers a high-level of performance on a budget. It is responsive and maneuverable, and offers peace-of-mind when you decide to go off-road.
Serious back-country riding. Perfect for all levels of competition. Uses the same HL Frame as the Mountain-100.
130709E6-8512-49B9-9F62-1F5C99152056
Sat Feb 08 2014 05:32:17 GMT-0500 (Eastern Standard Time)
168
Top-of-the-line competition mountain bike. Performance-enhancing options include the innovative HL Frame, super-smooth front suspension, and traction for all terrain.
I can't believe I'm singing the praises of a pair of socks, but I just came back from a grueling
3-day ride and these socks really helped make the trip a blast. They're lightweight yet really cushioned my feet all day.
The reinforced toe is nearly bullet-proof and I didn't experience any problems with rubbing or blisters like I have with
other brands. I know it sounds silly, but it's always the little stuff (like comfortable feet) that makes or breaks a long trip.
I won't go on another trip without them!
Tue Nov 12 2013 19:00:00 GMT-0500 (Eastern Standard Time)
david@graphicdesigninstitute.com
4
A little on the heavy side, but overall the entry/exit is easy in all conditions. I've used these pedals for
more than 3 years and I've never had a problem. Cleanup is easy. Mud and sand don't get trapped. I would like
them even better if there was a weight reduction. Maybe in the next design. Still, I would recommend them to a friend.
Tue Nov 12 2013 19:00:00 GMT-0500 (Eastern Standard Time)
3
937
Jill
Thu Nov 14 2013 19:00:00 GMT-0500 (Eastern Standard Time)
jill@margiestravel.com
2
Maybe it's just because I'm new to mountain biking, but I had a terrible time getting use
to these pedals. In my first outing, I wiped out trying to release my foot. Any suggestions on
ways I can adjust the pedals, or is it just a learning curve thing?
Thu Nov 14 2013 19:00:00 GMT-0500 (Eastern Standard Time)
4
798
Laura Norman
Thu Nov 14 2013 19:00:00 GMT-0500 (Eastern Standard Time)
laura@treyresearch.net
5
The Road-550-W from Adventure Works Cycles is everything it's advertised to be. Finally, a quality bike that
is actually built for a woman and provides control and comfort in one neat package. The top tube is shorter, the suspension is weight-tuned and there's a much shorter reach to the brake
levers. All this adds up to a great mountain bike that is sure to accommodate any woman's anatomy. In addition to getting the size right, the saddle is incredibly comfortable.
Attention to detail is apparent in every aspect from the frame finish to the careful design of each component. Each component is a solid performer without any fluff.
The designers clearly did their homework and thought about size, weight, and funtionality throughout. And at less than 19 pounds, the bike is manageable for even the most petite cyclist.
We had 5 riders take the bike out for a spin and really put it to the test. The results were consistent and very positive. Our testers loved the manuverability
and control they had with the redesigned frame on the 550-W. A definite improvement over the 2002 design. Four out of five testers listed quick handling
and responsivness were the key elements they noticed. Technical climbing and on the flats, the bike just cruises through the rough. Tight corners and obstacles were handled effortlessly. The fifth tester was more impressed with the smooth ride. The heavy-duty shocks absorbed even the worst bumps and provided a soft ride on all but the
nastiest trails and biggest drops. The shifting was rated superb and typical of what we've come to expect from Adventure Works Cycles. On descents, the bike handled flawlessly and tracked very well. The bike is well balanced front-to-rear and frame flex was minimal. In particular, the testers
noted that the brake system had a unique combination of power and modulation. While some brake setups can be overly touchy, these brakes had a good
amount of power, but also a good feel that allows you to apply as little or as much braking power as is needed. Second is their short break-in period. We found that they tend to break-in well before
the end of the first ride; while others take two to three rides (or more) to come to full power.
On the negative side, the pedals were not quite up to our tester's standards.
Just for fun, we experimented with routine maintenance tasks. Overall we found most operations to be straight forward and easy to complete. The only exception was replacing the front wheel. The maintenance manual that comes
with the bike say to install the front wheel with the axle quick release or bolt, then compress the fork a few times before fastening and tightening the two quick-release mechanisms on the bottom of the dropouts. This is to seat the axle in the dropouts, and if you do not
do this, the axle will become seated after you tightened the two bottom quick releases, which will then become loose. It's better to test the tightness carefully or you may notice that the two bottom quick releases have come loose enough to fall completely open. And that's something you don't want to experience
while out on the road!
The Road-550-W frame is available in a variety of sizes and colors and has the same durable, high-quality aluminum that AWC is known for. At a MSRP of just under $1125.00, it's comparable in price to its closest competitors and
we think that after a test drive you'l find the quality and performance above and beyond . You'll have a grin on your face and be itching to get out on the road for more. While designed for serious road racing, the Road-550-W would be an excellent choice for just about any terrain and
any level of experience. It's a huge step in the right direction for female cyclists and well worth your consideration and hard-earned money.
Thu Nov 14 2013 19:00:00 GMT-0500 (Eastern Standard Time)
Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W')
CK_TransactionHistory_TransactionType
TransactionType
(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
Clustered index created by a primary key constraint.
PK_TransactionHistoryArchive_TransactionID
UNIQUE
TransactionID
Check Constraints
//
Check constraint [TransactionType]='p' OR [TransactionType]='s' OR [TransactionType]='w' OR [TransactionType]='P' OR [TransactionType]='S' OR [TransactionType]='W'
CK_TransactionHistoryArchive_TransactionType
TransactionType
(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')
CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON p.[BusinessEntityID] = ea.[BusinessEntityID];
HumanResources.vEmployeeDepartment
//
Returns employee name, title, and current department.
CREATE VIEW [HumanResources].[vEmployeeDepartment]
AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[BusinessEntityID] = edh.[BusinessEntityID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
WHERE edh.EndDate IS NULL
HumanResources.vEmployeeDepartmentHistory
//
Returns employee name and current and previous departments.
CREATE VIEW [HumanResources].[vEmployeeDepartmentHistory]
AS
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,s.[Name] AS [Shift]
,d.[Name] AS [Department]
,d.[GroupName]
,edh.[StartDate]
,edh.[EndDate]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[BusinessEntityID] = edh.[BusinessEntityID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
INNER JOIN [HumanResources].[Shift] s
ON s.[ShiftID] = edh.[ShiftID];
CREATE VIEW [HumanResources].[vJobCandidate]
AS
SELECT
jc.[JobCandidateID]
,jc.[BusinessEntityID]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Prefix)[1]', 'nvarchar(30)') AS [Name.Prefix]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.First)[1]', 'nvarchar(30)') AS [Name.First]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Middle)[1]', 'nvarchar(30)') AS [Name.Middle]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Last)[1]', 'nvarchar(30)') AS [Name.Last]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Name/Name.Suffix)[1]', 'nvarchar(30)') AS [Name.Suffix]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/Skills)[1]', 'nvarchar(max)') AS [Skills]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Type)[1]', 'nvarchar(30)') AS [Addr.Type]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Addr.Loc.CountryRegion]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Addr.Loc.State]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Addr.Loc.City]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Address/Addr.PostalCode)[1]', 'nvarchar(20)') AS [Addr.PostalCode]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/EMail)[1]', 'nvarchar(max)') AS [EMail]
,[Resume].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/Resume/WebSite)[1]', 'nvarchar(max)') AS [WebSite]
,jc.[ModifiedDate]
FROM [HumanResources].[JobCandidate] jc
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume') AS Resume(ref);
HumanResources.vJobCandidateEducation
//
Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.
CREATE VIEW [HumanResources].[vJobCandidateEducation]
AS
SELECT
jc.[JobCandidateID]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Level)[1]', 'nvarchar(max)') AS [Edu.Level]
,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.StartDate]
,CONVERT(datetime, REPLACE([Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Edu.EndDate]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Degree)[1]', 'nvarchar(50)') AS [Edu.Degree]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Major)[1]', 'nvarchar(50)') AS [Edu.Major]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Minor)[1]', 'nvarchar(50)') AS [Edu.Minor]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.GPA)[1]', 'nvarchar(5)') AS [Edu.GPA]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.GPAScale)[1]', 'nvarchar(5)') AS [Edu.GPAScale]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.School)[1]', 'nvarchar(100)') AS [Edu.School]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(100)') AS [Edu.Loc.CountryRegion]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Location/Location/Loc.State)[1]', 'nvarchar(100)') AS [Edu.Loc.State]
,[Education].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Edu.Location/Location/Loc.City)[1]', 'nvarchar(100)') AS [Edu.Loc.City]
FROM [HumanResources].[JobCandidate] jc
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Education') AS [Education](ref);
HumanResources.vJobCandidateEmployment
//
Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.
CREATE VIEW [HumanResources].[vJobCandidateEmployment]
AS
SELECT
jc.[JobCandidateID]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.StartDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.StartDate]
,CONVERT(datetime, REPLACE([Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.EndDate)[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [Emp.EndDate]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.OrgName)[1]', 'nvarchar(100)') AS [Emp.OrgName]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.JobTitle)[1]', 'nvarchar(100)') AS [Emp.JobTitle]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Responsibility)[1]', 'nvarchar(max)') AS [Emp.Responsibility]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.FunctionCategory)[1]', 'nvarchar(max)') AS [Emp.FunctionCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.IndustryCategory)[1]', 'nvarchar(max)') AS [Emp.IndustryCategory]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.CountryRegion)[1]', 'nvarchar(max)') AS [Emp.Loc.CountryRegion]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.State)[1]', 'nvarchar(max)') AS [Emp.Loc.State]
,[Employment].ref.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(Emp.Location/Location/Loc.City)[1]', 'nvarchar(max)') AS [Emp.Loc.City]
FROM [HumanResources].[JobCandidate] jc
CROSS APPLY jc.[Resume].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
/Resume/Employment') AS Employment(ref);
Person.vAdditionalContactInfo
//
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
CREATE VIEW [Person].[vStateProvinceCountryRegion]
WITH SCHEMABINDING
AS
SELECT
sp.[StateProvinceID]
,sp.[StateProvinceCode]
,sp.[IsOnlyStateProvinceFlag]
,sp.[Name] AS [StateProvinceName]
,sp.[TerritoryID]
,cr.[CountryRegionCode]
,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp
INNER JOIN [Person].[CountryRegion] cr
ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
Person.vStateProvinceCountryRegion
//
Joins StateProvince table with CountryRegion table.
CREATE VIEW [Person].[vStateProvinceCountryRegion]
WITH SCHEMABINDING
AS
SELECT
sp.[StateProvinceID]
,sp.[StateProvinceCode]
,sp.[IsOnlyStateProvinceFlag]
,sp.[Name] AS [StateProvinceName]
,sp.[TerritoryID]
,cr.[CountryRegionCode]
,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp
INNER JOIN [Person].[CountryRegion] cr
ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
Production.vProductAndDescription
//
Product names and descriptions. Product descriptions are provided in multiple languages.
CREATE VIEW [Production].[vProductAndDescription]
WITH SCHEMABINDING
AS
-- View (indexed or standard) to display products and product descriptions by language.
SELECT
p.[ProductID]
,p.[Name]
,pm.[Name] AS [ProductModel]
,pmx.[CultureID]
,pd.[Description]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [Production].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
Production.vProductAndDescription
//
Clustered index on the view vProductAndDescription.
CREATE VIEW [Production].[vProductAndDescription]
WITH SCHEMABINDING
AS
-- View (indexed or standard) to display products and product descriptions by language.
SELECT
p.[ProductID]
,p.[Name]
,pm.[Name] AS [ProductModel]
,pmx.[CultureID]
,pd.[Description]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [Production].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
Production.vProductModelCatalogDescription
//
Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.
CREATE VIEW [Production].[vProductModelCatalogDescription]
AS
SELECT
[ProductModelID]
,[Name]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace html="http://www.w3.org/1999/xhtml";
(/p1:ProductDescription/p1:Summary/html:p)[1]', 'nvarchar(max)') AS [Summary]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Name)[1]', 'nvarchar(max)') AS [Manufacturer]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:Copyright)[1]', 'nvarchar(30)') AS [Copyright]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Manufacturer/p1:ProductURL)[1]', 'nvarchar(256)') AS [ProductURL]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:WarrantyPeriod)[1]', 'nvarchar(256)') AS [WarrantyPeriod]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Warranty/wm:Description)[1]', 'nvarchar(256)') AS [WarrantyDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:NoOfYears)[1]', 'nvarchar(256)') AS [NoOfYears]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";
(/p1:ProductDescription/p1:Features/wm:Maintenance/wm:Description)[1]', 'nvarchar(256)') AS [MaintenanceDescription]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:wheel)[1]', 'nvarchar(256)') AS [Wheel]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:saddle)[1]', 'nvarchar(256)') AS [Saddle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:pedal)[1]', 'nvarchar(256)') AS [Pedal]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:BikeFrame)[1]', 'nvarchar(max)') AS [BikeFrame]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wf="http://www.adventure-works.com/schemas/OtherFeatures";
(/p1:ProductDescription/p1:Features/wf:crankset)[1]', 'nvarchar(256)') AS [Crankset]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Angle)[1]', 'nvarchar(256)') AS [PictureAngle]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:Size)[1]', 'nvarchar(256)') AS [PictureSize]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Picture/p1:ProductPhotoID)[1]', 'nvarchar(256)') AS [ProductPhotoID]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Material)[1]', 'nvarchar(256)') AS [Material]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Color)[1]', 'nvarchar(256)') AS [Color]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/ProductLine)[1]', 'nvarchar(256)') AS [ProductLine]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/Style)[1]', 'nvarchar(256)') AS [Style]
,[CatalogDescription].value(N'declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
(/p1:ProductDescription/p1:Specifications/RiderExperience)[1]', 'nvarchar(1024)') AS [RiderExperience]
,[rowguid]
,[ModifiedDate]
FROM [Production].[ProductModel]
WHERE [CatalogDescription] IS NOT NULL;
Production.vProductModelInstructions
//
Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.
CREATE VIEW [Purchasing].[vVendorWithAddresses] AS
SELECT
v.[BusinessEntityID]
,v.[Name]
,at.[Name] AS [AddressType]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
FROM [Purchasing].[Vendor] v
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = v.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN [Person].[AddressType] at
ON at.[AddressTypeID] = bea.[AddressTypeID];
Purchasing.vVendorWithContacts
//
Vendor (company) names and the names of vendor employees to contact.
CREATE VIEW [Purchasing].[vVendorWithContacts] AS
SELECT
v.[BusinessEntityID]
,v.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
FROM [Purchasing].[Vendor] v
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = v.[BusinessEntityID]
INNER JOIN [Person].ContactType ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
Sales.vIndividualCustomer
//
Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
CREATE VIEW [Sales].[vIndividualCustomer]
AS
SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
,at.[Name] AS [AddressType]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
,p.[Demographics]
FROM [Person].[Person] p
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = p.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN [Person].[AddressType] at
ON at.[AddressTypeID] = bea.[AddressTypeID]
INNER JOIN [Sales].[Customer] c
ON c.[PersonID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;
Sales.vPersonDemographics
//
Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.
CREATE VIEW [Sales].[vPersonDemographics]
AS
SELECT
p.[BusinessEntityID]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
TotalPurchaseYTD[1]', 'money') AS [TotalPurchaseYTD]
,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
DateFirstPurchase[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [DateFirstPurchase]
,CONVERT(datetime, REPLACE([IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
BirthDate[1]', 'nvarchar(20)') ,'Z', ''), 101) AS [BirthDate]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
MaritalStatus[1]', 'nvarchar(1)') AS [MaritalStatus]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
YearlyIncome[1]', 'nvarchar(30)') AS [YearlyIncome]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
Gender[1]', 'nvarchar(1)') AS [Gender]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
TotalChildren[1]', 'integer') AS [TotalChildren]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
NumberChildrenAtHome[1]', 'integer') AS [NumberChildrenAtHome]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
Education[1]', 'nvarchar(30)') AS [Education]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
Occupation[1]', 'nvarchar(30)') AS [Occupation]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
HomeOwnerFlag[1]', 'bit') AS [HomeOwnerFlag]
,[IndividualSurvey].[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
NumberCarsOwned[1]', 'integer') AS [NumberCarsOwned]
FROM [Person].[Person] p
CROSS APPLY p.[Demographics].nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
/IndividualSurvey') AS [IndividualSurvey](ref)
WHERE [Demographics] IS NOT NULL;
Sales.vSalesPerson
//
Sales representiatives (names and addresses) and their sales-related information.
CREATE VIEW [Sales].[vStoreWithAddresses] AS
SELECT
s.[BusinessEntityID]
,s.[Name]
,at.[Name] AS [AddressType]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN [Person].[AddressType] at
ON at.[AddressTypeID] = bea.[AddressTypeID];
Sales.vStoreWithContacts
//
Stores (including store contacts) that sell Adventure Works Cycles products to consumers.
CREATE VIEW [Sales].[vStoreWithContacts] AS
SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];
Sales.vStoreWithDemographics
//
Stores (including demographics) that sell Adventure Works Cycles products to consumers.
CREATE VIEW [Sales].[vStoreWithDemographics] AS
SELECT
s.[BusinessEntityID]
,s.[Name]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees]
FROM [Sales].[Store] s;
Input parameter for the stored procedure uspGetBillOfMaterials. Enter a valid ProductID from the Production.Product table.
@StartProductID
[int] NULL
//
Input parameter for the stored procedure uspGetBillOfMaterials used to eliminate components not used after that date. Enter a valid date.
@CheckDate
[datetime] NULL
Definition
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
@StartProductID [int],
@CheckDate [datetime]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
-- components of a level 0 assembly, all level 2 components of a level 1 assembly)
-- The CheckDate eliminates any components that are no longer used in the product on this date.
WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
AS (
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
FROM [Production].[BillOfMaterials] b
INNER JOIN [Production].[Product] p
ON b.[ComponentID] = p.[ProductID]
WHERE b.[ProductAssemblyID] = @StartProductID
AND @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
UNION ALL
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [BOM_cte] cte
INNER JOIN [Production].[BillOfMaterials] b
ON b.[ProductAssemblyID] = cte.[ComponentID]
INNER JOIN [Production].[Product] p
ON b.[ComponentID] = p.[ProductID]
WHERE @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
)
-- Outer select from the CTE
SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
FROM [BOM_cte] b
GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
OPTION (MAXRECURSION 25)
END;
uspGetEmployeeManagers
//
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.
Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.
@BusinessEntityID
[int] NULL
Definition
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
@BusinessEntityID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] as p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName],
[EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName' -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
END;
uspGetManagerEmployees
//
Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table.
@BusinessEntityID
[int] NULL
Definition
CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
@BusinessEntityID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
END;
uspGetWhereUsedProductID
//
Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.
@StartProductID
[int] NULL
//
Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.
@CheckDate
[datetime] NULL
Definition
CREATE PROCEDURE [dbo].[uspGetWhereUsedProductID]
@StartProductID [int],
@CheckDate [datetime]
AS
BEGIN
SET NOCOUNT ON;
--Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 components of a level 0 assembly, all level 2 components of a level 1 assembly)
WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
AS (
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
FROM [Production].[BillOfMaterials] b
INNER JOIN [Production].[Product] p
ON b.[ProductAssemblyID] = p.[ProductID]
WHERE b.[ComponentID] = @StartProductID
AND @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
UNION ALL
SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [BOM_cte] cte
INNER JOIN [Production].[BillOfMaterials] b
ON cte.[ProductAssemblyID] = b.[ComponentID]
INNER JOIN [Production].[Product] p
ON b.[ProductAssemblyID] = p.[ProductID]
WHERE @CheckDate >= b.[StartDate]
AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
)
-- Outer select from the CTE
SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
FROM [BOM_cte] b
GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
OPTION (MAXRECURSION 25)
END;
uspLogError
//
Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.
Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.
@ErrorLogID
[int] NULL
Definition
-- uspLogError logs error information in the ErrorLog table about the
-- error that caused execution to jump to the CATCH block of a
-- TRY...CATCH construct. This should be executed from within the scope
-- of a CATCH block otherwise it will return without inserting error
-- information.
CREATE PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS -- by uspLogError in the ErrorLog table
BEGIN
SET NOCOUNT ON;
-- Output parameter value of 0 indicates that error
-- information was not logged
SET @ErrorLogID = 0;
BEGIN TRY
-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;
-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
RETURN;
END
INSERT [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
);
-- Pass back the ErrorLogID of the row inserted
SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure uspLogError: ';
EXECUTE [dbo].[uspPrintError];
RETURN -1;
END CATCH
END;
uspPrintError
//
Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.
-- uspPrintError prints error information about the error that caused
-- execution to jump to the CATCH block of a TRY...CATCH construct.
-- Should be executed from within the scope of a CATCH block otherwise
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[uspPrintError]
AS
BEGIN
SET NOCOUNT ON;
-- Print error information.
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END;
--A stored procedure which demonstrates integrated full text search
CREATE PROCEDURE [dbo].[uspSearchCandidateResumes]
@searchString [nvarchar](1000),
@useInflectional [bit]=0,
@useThesaurus [bit]=0,
@language[int]=0
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @string nvarchar(1050)
--setting the lcid to the default instance LCID if needed
IF @language = NULL OR @language = 0
BEGIN
SELECT @language =CONVERT(int, serverproperty('lcid'))
END
--FREETEXTTABLE case as inflectional and Thesaurus were required
IF @useThesaurus = 1 AND @useInflectional = 1
BEGIN
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN FREETEXTTABLE([HumanResources].[JobCandidate],*, @searchString,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
END
ELSE IF @useThesaurus = 1
BEGIN
SELECT @string ='FORMSOF(THESAURUS,"'+@searchString +'"'+')'
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
END
ELSE IF @useInflectional = 1
BEGIN
SELECT @string ='FORMSOF(INFLECTIONAL,"'+@searchString +'"'+')'
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
END
ELSE --base case, plain CONTAINSTABLE
BEGIN
SELECT @string='"'+@searchString +'"'
SELECT FT_TBL.[JobCandidateID],KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*,@string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.[KEY]
END
END;
HumanResources.uspUpdateEmployeeHireInfo
//
Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid BusinessEntityID from the Employee table.
@BusinessEntityID
[int] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
@JobTitle
[nvarchar](50) NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
@HireDate
[datetime] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
@RateChangeDate
[datetime] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
@Rate
[money] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
@PayFrequency
[tinyint] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.
@CurrentFlag
[Flag] NULL
Definition
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
@BusinessEntityID [int],
@JobTitle [nvarchar](50),
@HireDate [datetime],
@RateChangeDate [datetime],
@Rate [money],
@PayFrequency [tinyint],
@CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE [HumanResources].[Employee]
SET [JobTitle] = @JobTitle
,[HireDate] = @HireDate
,[CurrentFlag] = @CurrentFlag
WHERE [BusinessEntityID] = @BusinessEntityID;
INSERT INTO [HumanResources].[EmployeePayHistory]
([BusinessEntityID]
,[RateChangeDate]
,[Rate]
,[PayFrequency])
VALUES (@BusinessEntityID, @RateChangeDate, @Rate, @PayFrequency);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
HumanResources.uspUpdateEmployeeLogin
//
Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.
Input parameter for the stored procedure uspUpdateEmployeePersonalInfo. Enter a valid BusinessEntityID from the HumanResources.Employee table.
@BusinessEntityID
[int] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a national ID for the employee.
@NationalIDNumber
[nvarchar](15) NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a birth date for the employee.
@BirthDate
[datetime] NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a marital status for the employee.
@MaritalStatus
[nchar](1) NULL
//
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a gender for the employee.
@Gender
[nchar](1) NULL
Definition
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
@BusinessEntityID [int],
@NationalIDNumber [nvarchar](15),
@BirthDate [datetime],
@MaritalStatus [nchar](1),
@Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
UPDATE [HumanResources].[Employee]
SET [NationalIDNumber] = @NationalIDNumber
,[BirthDate] = @BirthDate
,[MaritalStatus] = @MaritalStatus
,[Gender] = @Gender
WHERE [BusinessEntityID] = @BusinessEntityID;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
Scalar function used in the uSalesOrderHeader trigger to set the starting account date.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function used in the uSalesOrderHeader trigger to set the starting account date.
returns
[datetime] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime]
AS
BEGIN
RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
ufnGetAccountingStartDate
//
Scalar function used in the uSalesOrderHeader trigger to set the ending account date.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function used in the uSalesOrderHeader trigger to set the ending account date.
returns
[datetime] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetAccountingStartDate]()
RETURNS [datetime]
AS
BEGIN
RETURN CONVERT(datetime, '20030701', 112);
END;
ufnGetDocumentStatusText
//
Scalar function returning the text representation of the Status column in the Document table.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the text representation of the Status column in the Document table.
returns
[nvarchar](16) NULL
//
Input parameter for the scalar function ufnGetDocumentStatusText. Enter a valid integer.
@Status
[tinyint] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetDocumentStatusText](@Status [tinyint])
RETURNS [nvarchar](16)
AS
-- Returns the sales order status text representation for the status value.
BEGIN
DECLARE @ret [nvarchar](16);
SET @ret =
CASE @Status
WHEN 1 THEN N'Pending approval'
WHEN 2 THEN N'Approved'
WHEN 3 THEN N'Obsolete'
ELSE N'** Invalid **'
END;
RETURN @ret
END;
ufnGetProductDealerPrice
//
Scalar function returning the dealer price for a given product on a particular order date.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the dealer price for a given product on a particular order date.
returns
[money] NULL
//
Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid ProductID from the Production.Product table.
@ProductID
[int] NULL
//
Input parameter for the scalar function ufnGetProductDealerPrice. Enter a valid order date.
@OrderDate
[datetime] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetProductDealerPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money]
AS
-- Returns the dealer price for the product on a specific date.
BEGIN
DECLARE @DealerPrice money;
DECLARE @DealerDiscount money;
SET @DealerDiscount = 0.60 -- 60% of list price
SELECT @DealerPrice = plph.[ListPrice] * @DealerDiscount
FROM [Production].[Product] p
INNER JOIN [Production].[ProductListPriceHistory] plph
ON p.[ProductID] = plph.[ProductID]
AND p.[ProductID] = @ProductID
AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!
RETURN @DealerPrice;
END;
ufnGetProductListPrice
//
Scalar function returning the list price for a given product on a particular order date.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the list price for a given product on a particular order date.
returns
[money] NULL
//
Input parameter for the scalar function ufnGetProductListPrice. Enter a valid ProductID from the Production.Product table.
@ProductID
[int] NULL
//
Input parameter for the scalar function ufnGetProductListPrice. Enter a valid order date.
@OrderDate
[datetime] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetProductListPrice](@ProductID [int], @OrderDate [datetime])
RETURNS [money]
AS
BEGIN
DECLARE @ListPrice money;
SELECT @ListPrice = plph.[ListPrice]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductListPriceHistory] plph
ON p.[ProductID] = plph.[ProductID]
AND p.[ProductID] = @ProductID
AND @OrderDate BETWEEN plph.[StartDate] AND COALESCE(plph.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!
RETURN @ListPrice;
END;
ufnGetProductStandardCost
//
Scalar function returning the standard cost for a given product on a particular order date.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the standard cost for a given product on a particular order date.
returns
[money] NULL
//
Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid ProductID from the Production.Product table.
@ProductID
[int] NULL
//
Input parameter for the scalar function ufnGetProductStandardCost. Enter a valid order date.
@OrderDate
[datetime] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetProductStandardCost](@ProductID [int], @OrderDate [datetime])
RETURNS [money]
AS
-- Returns the standard cost for the product on a specific date.
BEGIN
DECLARE @StandardCost money;
SELECT @StandardCost = pch.[StandardCost]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductCostHistory] pch
ON p.[ProductID] = pch.[ProductID]
AND p.[ProductID] = @ProductID
AND @OrderDate BETWEEN pch.[StartDate] AND COALESCE(pch.[EndDate], CONVERT(datetime, '99991231', 112)); -- Make sure we get all the prices!
RETURN @StandardCost;
END;
ufnGetPurchaseOrderStatusText
//
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.
returns
[nvarchar](15) NULL
//
Input parameter for the scalar function ufnGetPurchaseOrdertStatusText. Enter a valid integer.
@Status
[tinyint] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetPurchaseOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15)
AS
-- Returns the sales order status text representation for the status value.
BEGIN
DECLARE @ret [nvarchar](15);
SET @ret =
CASE @Status
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Approved'
WHEN 3 THEN 'Rejected'
WHEN 4 THEN 'Complete'
ELSE '** Invalid **'
END;
RETURN @ret
END;
ufnGetSalesOrderStatusText
//
Scalar function returning the text representation of the Status column in the SalesOrderHeader table.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the text representation of the Status column in the SalesOrderHeader table.
returns
[nvarchar](15) NULL
//
Input parameter for the scalar function ufnGetSalesOrderStatusText. Enter a valid integer.
@Status
[tinyint] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15)
AS
-- Returns the sales order status text representation for the status value.
BEGIN
DECLARE @ret [nvarchar](15);
SET @ret =
CASE @Status
WHEN 1 THEN 'In process'
WHEN 2 THEN 'Approved'
WHEN 3 THEN 'Backordered'
WHEN 4 THEN 'Rejected'
WHEN 5 THEN 'Shipped'
WHEN 6 THEN 'Cancelled'
ELSE '** Invalid **'
END;
RETURN @ret
END;
ufnGetStock
//
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.
Created: Thu Jul 17 2014 12:11:40 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.
returns
[int] NULL
//
Input parameter for the scalar function ufnGetStock. Enter a valid ProductID from the Production.ProductInventory table.
@ProductID
[int] NULL
Definition
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END;
ufnLeadingZeros
//
Scalar function used by the Sales.Customer table to help set the account number.
Created: Thu Jul 17 2014 12:11:14 GMT-0400 (Eastern Daylight Time), Modified: Thu Jan 20 2022 08:46:04 GMT-0500 (Eastern Standard Time)
Parameters
//
Scalar function used by the Sales.Customer table to help set the account number.
returns
[varchar](8) NULL
//
Input parameter for the scalar function ufnLeadingZeros. Enter a valid integer.
@Value
[int] NULL
Definition
CREATE FUNCTION [dbo].[ufnLeadingZeros](
@Value int
)
RETURNS varchar(8)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ReturnValue varchar(8);
SET @ReturnValue = CONVERT(varchar(8), @Value);
SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;
RETURN (@ReturnValue);
END;