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

Прайс Кевин Т.

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

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

/****** Object: Stored Procedure dbo.SelectEmployees Script Date: 10-Jul-02 12:41:13 PM ******/

create procedure dbo.SelectEmployees

AS

 SET NOCOUNT ON;

 SELECT FirstName, LastName, Department ID, Salary, ID 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.UpdateEmployee Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE dbo.UpdateEmployee (

 @FirstName varchar(50),

 @LastName varchar(70),

 @DepartmentID int,

 @Salary money,

 @Original_ID int)

AS

 SET NOCOUNT OFF;

 UPDATE tblEmployee SET FirstName = @FirstName, LastName = @LastName, DepartmentID = @DepartmentID, Salary = @Salary WHERE (ID = @Original_ID)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

/****** Object: Stored Procedure dbo.procEmployeesSorted Script Date: 10-Jul-02 12:41:13 PM ******/

CREATE PROCEDURE procEmployeesSorted

AS

 select * from tblEmployee

 order by LastName, FirstName return

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON