Create a Business Logic Layer, Data Access Layer classes, and Stored Procedure scripts from a database table

--------- SP_Employee_Select


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Employee_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_Employee_Select]
Go

SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go

CREATE PROCEDURE SP_Employee_Select


@EmployeeID int,
@EmployeeName varchar(50),
@EmployeeType varchar(50)

AS

Select 

EmployeeID,
EmployeeName,
EmployeeType

from Employee

where 
( @EmployeeID is null or @EmployeeID = EmployeeID ) and
( @EmployeeName is null or @EmployeeName = EmployeeName ) and
 @IsActive = IsActive

Go


SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go





--------- SP_Employee_Delete


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Employee_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_Employee_Delete]
Go

SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go

CREATE PROCEDURE SP_Employee_Delete

@EmployeeID int

AS

Update 
Employee

set IsActive = 0

where EmployeeID=@EmployeeID

Go


SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go





--------- SP_Employee_Insert


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Employee_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_Employee_Insert]
Go

SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go

CREATE PROCEDURE SP_Employee_Insert


@EmployeeName varchar(50)

AS

Insert into Employee
(
EmployeeName,
EmployeeType
)
values
(
@EmployeeName,
1
)

Go


SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go





--------- SP_Employee_Update


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Employee_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_Employee_Update]
Go

SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go

CREATE PROCEDURE SP_Employee_Update


@EmployeeID    int,
@EmployeeName    varchar(50)

AS

Update Employee

Set
	
	EmployeeName = @EmployeeName,
	 IsActive = 1
Where

EmployeeID = @EmployeeID

Go


SET QUOTED_IDENTIFIER OFF 
Go
SET ANSI_NULLS OFF
Go