Обработка баз данных на Visual Basic®.NET

SET ANSI_NULLS ON

GO

Наконец, для создания представлений, хранимых процедур и триггеров следует выполнить сценарий из листинга 3.6.

Листинг 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