storeprocedure

USE [ChotaMotaKaam]
GO
/****** Object:  StoredProcedure [dbo].[Proc_M_WorkerDetail]    Script Date: 4/30/2015 10:15:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[Proc_M_WorkerDetail]
@ID int=NUll,
@Name nvarchar(50)=NULL,
@DOB nvarchar(50)=NULL,
@Mobileno nvarchar(50)=NULL,
@Address1 nvarchar(50)=NULL,
@Address2 nvarchar(50)=NULL,
@EmailID nvarchar(50)=NULL,
@Refrence1 nvarchar(50)=NULL,
@Refrence2 nvarchar(50)=NULL,
@IdentificationMark nvarchar(50)=NULL,
@Trainning nvarchar(50)=NULL,
@TrainningDocument nvarchar(50)=NULL,
@Image nvarchar(50)=NULL,
@Experience nvarchar(50)=NULL,
@IDproof nvarchar(50)=NULL,
@PolVerificationDoc nvarchar(50)=NULL,
@Remark nvarchar(50)=NULL,
@QueryType Varchar(50)=NULL
as
BEGIN
If (@QueryType='Insert')

BEGIN
if not exists (Select ID from WorkerDetail where ID=@ID)
begin                
INSERT INTO WorkerDetail
(

Name ,
DOB ,
Mobileno ,
Address1 ,
Address2 ,
EmailID ,
Refrence1 ,
Refrence2,
IdentificationMark ,
Trainning ,
TrainningDocument ,
Image ,
Experience ,
IDproof ,
PolVerificationDoc ,
Remark
)
VALUES
(


@Name ,
@DOB ,
@Mobileno ,
@Address1 ,
@Address2 ,
@EmailID ,
@Refrence1,
@Refrence2,
@IdentificationMark ,
@Trainning ,
@TrainningDocument ,
@Image ,
@Experience ,
@IDproof ,
@PolVerificationDoc ,
@Remark
)
END
else begin
Update WorkerDetail set
Name=@Name ,
DOB=@DOB,
Mobileno=@Mobileno,
Address1= @Address1,
Address2=@Address2,
EmailID=@EmailID ,
Refrence1=@Refrence1,
Refrence2=@Refrence2 ,
IdentificationMark=@IdentificationMark ,
Trainning=@Trainning,
Experience=@Experience,
Remark=@Remark
where ID=@ID
end
select * from WorkerDetail where ID=  SCOPE_IDENTITY()

end

else If (@QueryType='ReadALLData')
begin
SELECT        TOP (50) WorkerDetail.ID, WorkerDetail.Name, WorkerDetail.DOB, WorkerDetail.Mobileno, WorkerDetail.Address1, WorkerDetail.Address2, WorkerDetail.EmailID,
                         WorkerDetail.IdentificationMark, WorkerDetail.TrainningDocument, WorkerDetail.Image, WorkerDetail.Experience, WorkerDetail.IDproof,
                         WorkerDetail.PolVerificationDoc, WorkerDetail.Remark, WorkerDetail.Refrence1, WorkerDetail.Refrence2, WorkerDetail.Trainning, WorkerDetail.AdminID,
                       M_WorkType.WorkType
FROM            WorkerDetail INNER JOIN
                         M_WorkType ON WorkerDetail.WID = M_WorkType.ID
end
else If (@QueryType='ReadSearch')
begin
SELECT       distinct TOP (10)WorkerDetail.Name
FROM            WorkerDetail  WHERE        (Name LIKE '%' + @Name + '%')

end
else IF(@QueryType='DeleteAdmin')
BEGIN
Delete  FROM  WorkerDetail where ID=@ID
END








End