[dbo].[titleview]
Added
View
CREATE VIEW [dbo].[titleview]
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
[dbo].[Storesview]
Removed
View
CREATE VIEW [dbo].[Storesview]
AS SELECT s.stor_id
,s.stor_name
,s.stor_address
,s.city
,s.state
,s.zip FROM stores s
[dbo].[ufnLeadingZeros]
Removed
Function
/****** Object: Table [HumanResources].[Department] Script Date: 14-Feb-19 11:08:44 ******/
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;
[ddlDatabaseTriggerLog]
Removed
DDL Trigger
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
INSERT [dbo].[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data
);
END;
[PubsDev] with [PubsProd]
Modified
Database
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
SET
ANSI_NULL_DEFAULT OFF,
ANSI_NULLS OFF,
ANSI_PADDING OFF,
ANSI_WARNINGS OFF,
ARITHABORT OFF,
AUTO_CLOSE ON,
AUTO_CLOSE OFF,
AUTO_CREATE_STATISTICS ON,
AUTO_SHRINK OFF,
AUTO_UPDATE_STATISTICS ON,
AUTO_UPDATE_STATISTICS_ASYNC OFF,
COMPATIBILITY_LEVEL = 140,
CONCAT_NULL_YIELDS_NULL OFF,
CONTAINMENT = NONE,
CURSOR_CLOSE_ON_COMMIT OFF,
CURSOR_DEFAULT GLOBAL,
DATE_CORRELATION_OPTIMIZATION OFF,
DB_CHAINING OFF,
HONOR_BROKER_PRIORITY OFF,
MULTI_USER,
NESTED_TRIGGERS = ON,
NUMERIC_ROUNDABORT OFF,
PAGE_VERIFY CHECKSUM,
PARAMETERIZATION SIMPLE,
QUOTED_IDENTIFIER OFF,
READ_COMMITTED_SNAPSHOT OFF,
RECOVERY SIMPLE,
RECOVERY FULL,
RECURSIVE_TRIGGERS OFF,
TRANSFORM_NOISE_WORDS = OFF,
TRUSTWORTHY OFF
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
COLLATE Cyrillic_General_CI_AS
GO
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
SET DISABLE_BROKER
GO
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
SET FILESTREAM (NON_TRANSACTED_ACCESS = OFF)
GO
ALTER DATABASE [PubsProd]
ALTER DATABASE [PubsDev]
SET QUERY_STORE = OFF
GO
USE [PubsProd]
USE [PubsDev]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER AUTHORIZATION ON DATABASE :: [PubsProd] TO [sa]
ALTER AUTHORIZATION ON DATABASE :: [PubsDev] TO [DATASOFT\JSanders]
GO
GRANT CONNECT TO [dbo]
GO
GRANT CONNECT TO [Jordan_Sanders]
GO
GRANT
VIEW ANY COLUMN ENCRYPTION KEY DEFINITION,
VIEW ANY COLUMN MASTER KEY DEFINITION TO [public]
[dbo].[authors]
Modified
Table
CREATE TABLE [dbo].[authors] (
[au_id] [dbo].[id] NOT NULL,
[au_lname] [varchar](40) NOT NULL,
[au_fname] [varchar](20) NOT NULL,
[phone] [char](12) NOT NULL DEFAULT ('UNKNOWN'),
[address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [char](5) NULL,
[contract] [bit] NOT NULL
[contract] [bit] NOT NULL,
[skype] [nvarchar](50) NULL
)
ON [PRIMARY]
GO
CREATE INDEX [aunmind]
ON [dbo].[authors] ([au_lname], [au_fname])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[authors]
ADD CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED ([au_id])
GO
ALTER TABLE [dbo].[authors]
ADD CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO
ALTER TABLE [dbo].[authors]
ADD CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO
ALTER TABLE [dbo].[authors]
ADD CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO
ALTER TABLE [dbo].[authors]
ADD CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO
ALTER TABLE [dbo].[authors]
ADD CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
[dbo].[jobs]
Modified
Table
CREATE TABLE [dbo].[jobs] (
[job_id] [smallint] IDENTITY,
[job_desc] [varchar](50) NOT NULL DEFAULT ('New Position - title not formalized yet'),
[min_lvl] [tinyint] NOT NULL
[min_lvl] [tinyint] NOT NULL,
[max_lvl] [smallint] NOT NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[jobs]
ADD PRIMARY KEY CLUSTERED ([job_id])
GO
ALTER TABLE [dbo].[jobs]
ADD CHECK ([max_lvl]<=(3000))
GO
ALTER TABLE [dbo].[jobs]
ADD CHECK ([min_lvl]>=(10))
[dbo].[publishers]
Modified
Table
CREATE TABLE [dbo].[publishers] (
[pub_id] [char](4) NOT NULL,
[pub_name] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[country] [varchar](30) NULL DEFAULT ('USA')
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[publishers]
ADD CONSTRAINT [UPKCL_pubind] PRIMARY KEY CLUSTERED ([pub_id])
GO
ALTER TABLE [dbo].[publishers]
ALTER TABLE [dbo].[publishers] WITH NOCHECK
ADD CHECK ([pub_id]='1753' OR [pub_id]='1756' OR [pub_id]='1622' OR [pub_id]='0877' OR [pub_id]='0736' OR [pub_id]='1389' OR [pub_id] like '99[0-9][0-9]')
[dbo].[pub_info_test] with [dbo].[pub_info]
Modified
Table
CREATE TABLE [dbo].[pub_info] (
CREATE TABLE [dbo].[pub_info_test] (
[pub_id] [char](4) NOT NULL,
[logo] [image] NULL,
[pr_info] [text] NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[pub_info]
ALTER TABLE [dbo].[pub_info_test]
ADD CONSTRAINT [UPKCL_pubinfo] PRIMARY KEY CLUSTERED ([pub_id])
GO
ALTER TABLE [dbo].[pub_info] WITH NOCHECK
ALTER TABLE [dbo].[pub_info_test] WITH NOCHECK
ADD CONSTRAINT [FK__pub_info__pub_id__3F466844] FOREIGN KEY ([pub_id]) REFERENCES [dbo].[publishers] ([pub_id])
[dbo].[sales]
Modified
Table
CREATE TABLE [dbo].[sales] (
[stor_id] [char](4) NOT NULL,
[ord_num] [varchar](20) NOT NULL,
[ord_date] [datetime] NOT NULL,
[qty] [smallint] NOT NULL,
[payterms] [varchar](12) NOT NULL,
[title_id] [dbo].[tid] NOT NULL
)
ON [PRIMARY]
GO
CREATE INDEX [titleidind]
ON [dbo].[sales] ([title_id])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[sales]
ADD CONSTRAINT [UPKCL_sales] PRIMARY KEY CLUSTERED ([stor_id], [ord_num], [title_id])
GO
ALTER TABLE [dbo].[sales] WITH NOCHECK
ADD CONSTRAINT [FK__sales__stor_id__59063A47] FOREIGN KEY ([stor_id]) REFERENCES [dbo].[stores] ([stor_id])
GO
ALTER TABLE [dbo].[sales] WITH NOCHECK
ADD CONSTRAINT [FK__sales__title_id__59FA5E80] FOREIGN KEY ([title_id]) REFERENCES [dbo].[titles] ([title_id])
[dbo].[stores]
Modified
Table
CREATE TABLE [dbo].[stores] (
[stor_id] [char](4) NOT NULL,
[stor_name] [varchar](40) NULL,
[stor_address] [varchar](40) NULL,
[store_address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [char](5) NULL
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[stores]
ADD CONSTRAINT [UPK_storeid] PRIMARY KEY CLUSTERED ([stor_id])
[dbo].[titleauthor]
Modified
Table
CREATE TABLE [dbo].[titleauthor] (
[au_id] [dbo].[id] NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
[au_ord] [smallint] NULL,
[royaltyper] [int] NULL
)
ON [PRIMARY]
GO
CREATE INDEX [auidind]
ON [dbo].[titleauthor] ([au_id])
ON [PRIMARY]
GO
CREATE INDEX [titleidind]
ON [dbo].[titleauthor] ([title_id])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[titleauthor]
ADD CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED ([au_id], [title_id])
GO
ALTER TABLE [dbo].[titleauthor] WITH NOCHECK
ADD CONSTRAINT [FK__titleauth__au_id__534D60F1] FOREIGN KEY ([au_id]) REFERENCES [dbo].[authors] ([au_id])
GO
ALTER TABLE [dbo].[titleauthor] WITH NOCHECK
ADD CONSTRAINT [FK__titleauth__title__5441852A] FOREIGN KEY ([title_id]) REFERENCES [dbo].[titles] ([title_id])
[dbo].[titles] with [dbo].[titles_Development]
Modified
Table
CREATE TABLE [dbo].[titles_Development] (
CREATE TABLE [dbo].[titles] (
[title_id] [dbo].[tid] NOT NULL,
[title] [varchar](80) NOT NULL,
[type] [char](12) NOT NULL DEFAULT ('UNDECIDED'),
[pub_id] [char](4) NULL,
[price] [money] NULL,
[advance] [money] NULL,
[royalty] [int] NULL,
[ytd_sales] [int] NULL,
[notes] [varchar](200) NULL,
[pubdate] [datetime] NOT NULL DEFAULT (getdate())
)
ON [PRIMARY]
GO
CREATE INDEX [titleind]
ON [dbo].[titles_Development] ([title])
ON [dbo].[titles] ([title])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[titles_Development]
ALTER TABLE [dbo].[titles]
ADD CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED ([title_id])
GO
ALTER TABLE [dbo].[titles_Development] WITH NOCHECK
ALTER TABLE [dbo].[titles] WITH NOCHECK
ADD CONSTRAINT [FK__titles__pub_id__4D94879B] FOREIGN KEY ([pub_id]) REFERENCES [dbo].[publishers] ([pub_id])
[HumanResources].[Employee]
Modified
Table
CREATE TABLE [HumanResources].[Employee] (
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[Gender] [nchar](1) NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()) ROWGUIDCOL,
[ModifiedDate] [datetime] NOT NULL
)
ON [PRIMARY]
GO
ALTER TABLE [HumanResources].[Employee]
ADD CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED ([BusinessEntityID])
[dbo].[reptq3]
Modified
Procedure
CREATE PROCEDURE [dbo].[reptq3] @lolimit money, @hilimit money,
@type char(12)
AS
select
case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
AVG(price) AS avg_price
from titles
where price IS NOT NULL
group by pub_id with ROLLUP
order by pub_id;
SELECT * FROM titles_Development td;
SELECT * from titles;
[Person]
Modified
Schema
CREATE SCHEMA [Person] AUTHORIZATION [public]
CREATE SCHEMA [Person] AUTHORIZATION [dbo]
[dbo].[DatabaseLog]
Identical
Table
CREATE TABLE [dbo].[DatabaseLog] (
[DatabaseLogID] [int] IDENTITY,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NOT NULL,
[Object] [sysname] NOT NULL,
[TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[XmlEvent] [xml] NOT NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[DatabaseLog]
ADD CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED ([DatabaseLogID])
[dbo].[employee]
Identical
Table
CREATE TABLE [dbo].[employee] (
[emp_id] [dbo].[empid] NOT NULL,
[fname] [varchar](20) NOT NULL,
[minit] [char](1) NULL,
[lname] [varchar](30) NOT NULL,
[job_id] [smallint] NOT NULL DEFAULT (1),
[job_lvl] [int] NULL DEFAULT (10),
[pub_id] [char](4) NOT NULL DEFAULT ('9952'),
[hire_date] [datetime] NOT NULL DEFAULT (getdate())
)
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [employee_ind]
ON [dbo].[employee] ([lname], [fname], [minit])
ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[employee_insupd]
ON [employee]
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
raiserror ('Job id 1 expects the default level of 10.',16,1)
ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
raiserror ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
end
GO
ALTER TABLE [dbo].[employee]
ADD CONSTRAINT [PK_emp_id] PRIMARY KEY NONCLUSTERED ([emp_id])
GO
ALTER TABLE [dbo].[employee]
ADD CONSTRAINT [CK_emp_id] CHECK ([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
GO
ALTER TABLE [dbo].[employee] WITH NOCHECK
ADD CONSTRAINT [FK__employee__job_id__6477ECF3] FOREIGN KEY ([job_id]) REFERENCES [dbo].[jobs] ([job_id])
GO
ALTER TABLE [dbo].[employee] WITH NOCHECK
ADD CONSTRAINT [FK__employee__pub_id__656C112C] FOREIGN KEY ([pub_id]) REFERENCES [dbo].[publishers] ([pub_id])
[dbo].[byroyalty]
Identical
Procedure
CREATE PROCEDURE [dbo].[byroyalty] @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
[dbo].[reptq1]
Identical
Procedure
CREATE PROCEDURE [dbo].[reptq1] AS
select
case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
avg(price) as avg_price
from titles
where price is NOT NULL
group by pub_id with rollup
order by pub_id
[dbo].[reptq2]
Identical
Procedure
CREATE PROCEDURE [dbo].[reptq2] AS
select
case when grouping(type) = 1 then 'ALL' else type end as type,
case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
avg(ytd_sales) as avg_ytd_sales
from titles
where pub_id is NOT NULL
group by pub_id, type with rollup
[dbo].[uspGetBillOfMaterials]
Identical
Procedure
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]
,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;
[dbo].[uspGetEmployeeManagers]
Identical
Procedure
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]
,e.BirthDate
,e.MaritalStatus
,e.Gender
,e.HireDate
,e.SalariedFlag
,e.VacationHours
,e.SickLeaveHours
,e.CurrentFlag
,e.LoginID
,e.NationalIDNumber
,e.OrganizationLevel
,e.OrganizationNode
,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;
[HumanResources].[uspUpdateEmployeeHireInfo]
Identical
Procedure
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]
, [jobs])
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;
[dbo].[JobLevel]
Identical
Function
CREATE FUNCTION [dbo].[JobLevel] (@Max_lvl INT)
RETURNS TABLE
AS
RETURN (SELECT max_lvl
FROM jobs);
[dbo].[SalesByStore]
Identical
Function
CREATE FUNCTION [dbo].[SalesByStore] (@storeid INT)
RETURNS TABLE
AS
RETURN (SELECT
s.stor_id
,s.ord_num
,s.ord_date
,s.payterms
,s.title_id
FROM sales s);
[Jordan_Sanders]
Identical
User
CREATE USER [Jordan_Sanders]
WITHOUT LOGIN
[dbo].[AccountNumber]
Identical
Data Type
CREATE TYPE [dbo].[AccountNumber] FROM [nvarchar](15)
[dbo].[empid]
Identical
Data Type
CREATE TYPE [dbo].[empid] FROM [char](9) NOT NULL
GO
GRANT REFERENCES ON TYPE :: [dbo].[empid] TO [public]
[dbo].[Flag]
Identical
Data Type
CREATE TYPE [dbo].[Flag] FROM [bit] NOT NULL
[dbo].[id]
Identical
Data Type
CREATE TYPE [dbo].[id] FROM [varchar](11) NOT NULL
GO
GRANT REFERENCES ON TYPE :: [dbo].[id] TO [public]
[dbo].[tid]
Identical
Data Type
CREATE TYPE [dbo].[tid] FROM [varchar](6) NOT NULL
GO
GRANT REFERENCES ON TYPE :: [dbo].[tid] TO [public]
[HumanResources]
Identical
Schema
CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
[Sal]
Identical
Schema
CREATE SCHEMA [Sal] AUTHORIZATION [dbo]