Curd Operation - Stored Procedure
Use IDE :- Microsoft SQL Server - 2008 R2
=================================================================
Create Database
=================================================================
CREATE DATABASE CurdDB;
USE CurdDB;
=================================================================
Create Table
=================================================================
CREATE TABLE [dbo].[Employee](
[EmpId] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
[EmpNo] [varchar](50) NOT NULL,
[Department] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)
) ON [PRIMARY]
=================================================================
Select Table
=================================================================
Select * from Employee;
=================================================================
Create Procedure for Get Employees Details
=================================================================
USE [CurdDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmployeeDetails]
AS
BEGIN
SELECT
EmpId ,
FirstName ,
LastName ,
EmpNo ,
Department
FROM Employee
END
=================================================================
Create Procedure for INSERT AND UPDATE Record
=================================================================
USE [CurdDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddEmployee]
@EmpId BIGINT ,
@FirstName VARCHAR(50) ,
@LastName VARCHAR(150),
@EmpNo VARCHAR(50) ,
@Department VARCHAR(50)
AS
IF(@EmpId= 0)
BEGIN
INSERT INTO Employee
(
FirstName ,
LastName ,
EmpNo ,
Department
)
values
(
@FirstName ,
@LastName ,
@EmpNo ,
@Department
)
END
ELSE
BEGIN
UPDATE Employee
SET FirstName = @FirstName ,
LastName = @LastName ,
EmpNo = @EmpNo ,
Department = @Department
WHERE EmpId = @EmpId
END
=================================================================
Create Procedure for Delete Record
=================================================================
USE [CurdDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteEmployee]
@EmpId BIGINT
AS
BEGIN
DELETE FROM Employee
WHERE EmpId = @EmpId
END
================================================================
Note :- Use these stored procedure for curd operation in application.
================================================================
No comments:
Post a Comment