SET ANSI_NULLS ON
GO
Наконец, для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.
/****** object: View dbo.EmployeeDepartment_view Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE view EmployeeDepartment_view as
select e.ID, FirstName, LastName, DepartmentName
from tblEmployee e, tblDepartment t
where e.DepartmentID = t.ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo.qryEmployee_view Script Date: 10-Jul-02 12:41:13 PM ******/
create view qryEmployee_view as
SELECT ID, FirstName, LastName, DepartmentID from tblEmployee
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.DeleteEmployee Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.DeleteEmployee (@Original_ID int)
AS
SET NOCOUNT OFF;
DELETE FROM tblEmployee WHERE (ID = @Original_ID)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.GetCustomerFromID Script Date: 10-Jul-02 12:41:13 PM ******/
create procedure GetCustomerFromID @custID int
as
select * from tblCustomer where ID = @custID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.InsertEmployee (
@FirstName varchar(50),
@LastName varchar(70),
@DepartmentID int,
@Salary money)
AS
SET NOCOUNT OFF;
if (@Salary = 0 or @Salary is null) begin
-– Do complicated salary calculations
set @Salary = @DepartmentID * 10000
end
INSERT INTO tblEmployee(FirstName, LastName, Salary) VALUES
(@FirstName, @LastName, @DepartmentID, @Salary)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.InsertEmployeeOrg Script Date: 10-Jul-02 12:41:13 PM ******/
CREATE PROCEDURE dbo.InsertEmployeeOrg (
@FirstName varchar(50),
@LastName varchar(70),
@DepartmentID int,
@Salary money
)
AS
SET NOCOUNT OFF;
INSERT INTO tblEmployee(FirstName, LastName, DepartmentID, Salary) VALUES
(@FirstName, @LastName, @DepartmentID, @Salary)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.LastNameLookup Script Date: 10-Jul-02 12:41:13 PM ******/
create proc LastNameLookup
@name varchar(40) as
select * from tblCustomer where soundex(gname) = LastNameSoundex
GO
