December 30, 2010 at 1:40 am
Hi Expert !!! i have following Query in which i want to get Full Name which contains (First name,Middle Name , And Last name).For this i have this Query ... but there is some problem in it when these Three name occur(like First,middle,last name) it will give me accurate result,when it get 4th name like ( Ahmed Ali Malik Zulfiqar ) then it will give me Error "Invalid length parameter passed to the SUBSTRING function." Could anybody help me to sort out this problem...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[RFPAP]
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
Delete from SBO_RAHNUMA.dbo.OUBR
Declare @Code int
Declare @Name nvarchar(200)
Declare @Remarks nvarchar(200)
--declare @HRMSCode nvarchar(25)
DECLARE C1 CURSOR
FOR
Select T1.Branch_Nm,T1.Branch_Cd from GMBranchMst As T1
OPEN C1
Set @Code=1
FETCH NEXT FROM C1
INTO @Name,@Remarks
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert Into SBO_RAHNUMA.dbo.OUBR (Code,Name,SBO_RAHNUMA.dbo.OUBR.U_HRMSCode)
If Not Exists(Select Code from SBO_RAHNUMA.dbo.OUBR Where SBO_RAHNUMA.dbo.OUBR.Remarks=@Remarks)
Begin
If Len(@Name)>20
Begin
Set @Name= SubString(@Name,0,20)
End
Insert Into SBO_RAHNUMA.dbo.OUBR (SBO_RAHNUMA.dbo.OUBR.Code, SBO_RAHNUMA.dbo.OUBR.Name,
SBO_RAHNUMA.dbo.OUBR.Remarks)
Values (@Code,@Name,@Remarks)
ENd
--Select @Name,@Remarks
--print @code
Set @Code=@Code+1
FETCH NEXT FROM C1
INTO @Name,@Remarks--,@HRMSCode
End
CLOSE C1
DEALLOCATE C1
---Department Start---------------------------------------------------------------------
--Delete from SBO_RAHNUMA.dbo.OUBR
--Declare @Code int
--Declare @Name nvarchar(200)
Declare @Remarks1 nvarchar(200)
declare @HRMSCode nvarchar(25)
DECLARE C2 CURSOR
FOR
Select T1.Dept_SNm,T1.Dept_Nm, T1.Dept_Cd from GMDeptMst As T1
OPEN C2
Set @Code=1
FETCH NEXT FROM C2
INTO @Name,@Remarks1,@HRMSCode
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert Into SBO_RAHNUMA.dbo.OUDP (Code,Name,SBO_RAHNUMA.dbo.OUDP.U_HRMSCode)
If Not Exists(Select Code from SBO_RAHNUMA.dbo.OUDP Where SBO_RAHNUMA.dbo.OUDP.U_HRMSCode=@HRMSCode)
Insert Into SBO_RAHNUMA.dbo.OUDP (SBO_RAHNUMA.dbo.OUDP.Code, SBO_RAHNUMA.dbo.OUDP.Name,
SBO_RAHNUMA.dbo.OUDP.Remarks,SBO_RAHNUMA.dbo.OUDP.U_HRMSCode)
Values (@Code,@Name,@Remarks1,@HRMSCode)
Set @Code=@Code+1
FETCH NEXT FROM C2
INTO @Name,@Remarks1,@HRMSCode
End
CLOSE C2
DEALLOCATE C2
---Department End---------------------------------------------------------------------
--Employee Start------------------------------------------------------------------
Delete from SBO_RAHNUMA.dbo.OHEM
Declare @EmpID int
Declare @FName nvarchar(200)
Declare @MName nvarchar(200)
Declare @LName nvarchar(200)
Declare @JTitle nvarchar(250)
Declare @EmpCode nvarchar(150)
Declare @OfficeTel nvarchar(150)
Declare @mobile nvarchar(100)
--Declare @pager nvarchar(100)
Declare @hometel nvarchar(100)
Declare @manager int
Declare @fax nvarchar(100)
Declare @email nvarchar(100)
Declare @Work_Street nvarchar(100)
Declare @Work_block nvarchar(100)
Declare @Work_city nvarchar(100)
Declare @Work_Zip nvarchar(100)
Declare @Work_Country nvarchar(100)
Declare @home_Street nvarchar(100)
Declare @home_block nvarchar(100)
Declare @home_city nvarchar(100)
Declare @home_Zip nvarchar(100)
Declare @home_Country nvarchar(100)
Declare @StartDate datetime
Declare @status nvarchar(50)
Declare @termdate datetime
Declare @Sex nvarchar(10)
Declare @birthdate datetime
Declare @martstatus nvarchar(10)
Declare @govID nvarchar(100)
Declare @Salary money
Declare @bankcode nvarchar(200)
Declare @bankAccount nvarchar(100)
Declare @bankBranch nvarchar(100)
Declare @EmpName nvarchar(500)
Declare @DeptCode nvarchar(100)
Declare @CAddress1 nvarchar(500)
Declare @CAddress2 nvarchar(500)
Declare @PAddress1 nvarchar(500)
Declare @PAddress2 nvarchar(500)
Declare @branch nvarchar(200)
DECLARE C3 CURSOR
FOR
Select Emp_Cd,Emp_Nm,Birth_Dt,Gender_Cd,
(Select GMDesgMst.Desg_Nm from GMDesgMst
Where GMDesgMst.Desg_Cd=GMEmpMst.Desg_Cd) As Designation,
--(Select SBO_RAHNUMA.dbo.OUDP.Code from SBO_RAHNUMA.dbo.OUDP
--Where SBO_RAHNUMA.dbo.OUDP.U_HRMSCode=GMEmpMst.Desg_Cd) As DeptCode,
GMEmpMst.Dept_Cd,
Join_Dt,Passpt_Cd,
--(Select Top 1 GMPARAMETERS.Para_Cd from GMPARAMETERS
--where GMPARAMETERS.Para_Cd=Emp_Cat_Cd) as Emp_Status,
Emp_Cat_Cd As Emp_Status,
--(Select SBO_RAHNUMA.dbo.OUBR.Code from SBO_RAHNUMA.dbo.OUBR
--Where SBO_RAHNUMA.dbo.OUBR.Remarks=GMEmpMst.Branch_Cd) As Branch,
GMEmpMst.Branch_Cd,
Marital_Cd,Cr_Add1 ,Cr_Add2 ,Cr_Pincode,
Pr_Add1,Pr_Add2,Pr_Pincode,
Phone1,Phone2,mobile,Email,
--(Select T1.BankCode From SBO_RAHNUMA.dbo.ODSC As T1
--Where T1.BankName Like SalBankNm) As BankCode,
SalBankNm,
SalBankBranch,Salary_Ac_No,Present_CTC,
PanNo As ContractEndDate
From GMEMPMST
OPEN C3
Set @EmpID=1
FETCH NEXT FROM C3
INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@StartDate,
@govID,@Status,@branch,
@martstatus,@CAddress1,@CAddress2,@Work_Zip,
@PAddress1,@PAddress2,@home_Zip,
@OfficeTel,@hometel,@mobile,
@email,@bankcode,@bankBranch,@bankAccount,
@Salary,@termdate
WHILE @@FETCH_STATUS = 0
BEGIN
If Not Exists(Select empid from SBO_RAHNUMA.dbo.OHEM
where SBO_RAHNUMA.dbo.OHEM.jobTitle=@EmpCode)
Begin
--Select @DeptCode
Set @DeptCode=(Select SBO_RAHNUMA.dbo.OUDP.Code from SBO_RAHNUMA.dbo.OUDP
Where SBO_RAHNUMA.dbo.OUDP.U_HRMSCode=@DeptCode)
--Select @DeptCode
Set @bankcode=(Select top 1 T1.BankCode From SBO_RAHNUMA.dbo.ODSC As T1
Where T1.BankName Like @bankcode)
Set @branch=(Select SBO_RAHNUMA.dbo.OUBR.Code from SBO_RAHNUMA.dbo.OUBR
Where SBO_RAHNUMA.dbo.OUBR.Remarks=@branch)
--Select @status
Set @status= (Select statusID From SBO_RAHNUMA.dbo.OHST where descriptio Like @status)
---Split Name
--Select @EmpName
Set @fname=Left(@EmpName,CHARINDEX(' ', @EmpName))
If len(@EmpName)-Len(REPLACE(@EmpName,' ',''))=1
Begin
set @lname=(Select Substring(@EmpName, CHARINDEX(@fname, @EmpName) +len(@FName),5000)) --AS LeftSide)
set @mname=''
End
else
begin
set @mname =(Select SubString(@EmpName, CHARINDEX(' ', @EmpName)+1,
CHARINDEX(' ', @EmpName,CHARINDEX(' ', @EmpName)+1)-CHARINDEX(' ', @EmpName)-1)
)
set @lname=(Select Substring(@EmpName, CHARINDEX(@MName, @EmpName) +len(@MName),5000)) --AS LeftSide)
end
[/b]
--Select @EmpID,@EmpCode,
--@FName,@MName,@LName,@Sex,@DeptCode,@branch,
--@CAddress1,@CAddress2,@Work_Zip,'PK',
--@OfficeTel,@hometel,@mobile,@email,@StartDate,
--@status,@Salary,@termdate,@bankcode,@bankBranch,@bankAccount,
--@PAddress1,@PAddress2,@home_Zip,'PK',
--@birthdate,@govID,@JTitle
--SBO_RAHNUMA
Insert Into SBO_RAHNUMA.dbo.OHEM(empid,jobTitle,
firstName,middleName,lastName,
sex,dept,branch,
workStreet,workBlock,workZip,workCountr,
officeTel,mobile,homeTel,
email,startDate,status,
salary,termDate,
bankCode,bankBranch,bankAcount,
homeStreet,homeBlock,homeZip,
homeCountr,birthDate,
govID,U_JobTitle)
Values(@EmpID,@EmpCode,
@FName,@MName,@LName,@Sex,@DeptCode,@branch,
@CAddress1,@CAddress2,@Work_Zip,'PK',
@OfficeTel,@hometel,@mobile,@email,@StartDate,
@status,@Salary,@termdate,@bankcode,@bankBranch,@bankAccount,
@PAddress1,@PAddress2,@home_Zip,'PK',
@birthdate,@govID,@JTitle)
End
Set @EmpID=@EmpID+1
FETCH NEXT FROM C3
INTO @EmpCode,@EmpName,@birthdate,@Sex,@JTitle,@DeptCode ,@StartDate,
@govID,@Status,@branch,
@martstatus,@CAddress1,@CAddress2,@Work_Zip,
@PAddress1,@PAddress2,@home_Zip,
@OfficeTel,@hometel,@mobile,
@email,@bankcode,@bankBranch,@bankAccount,
@Salary,@termdate
END
CLOSE C3
DEALLOCATE C3
Set @Empid=(Select Max(empid) from SBO_RAHNUMA.dbo.OHEM)
if not Exists(Select * from SBO_RAHNUMA.dbo.ONNM where objectcode='171'
And AutoKey=@empid+1)
update SBO_RAHNUMA.dbo.ONNM Set AutoKey=@empid+1 where objectcode='171'
--Employee End------------------------------------------------------------------
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
END
December 30, 2010 at 2:12 am
its too tough(for me) to find where exactly you taking the nmae value in a variable but I suspect this could be because of length of variable. If you can try to change it nvarchar(max) or some higher value and see the output.
As well if you can just post the code which you think is generating the problem then it will be easy to troubleshoot.
----------
Ashish
December 30, 2010 at 2:26 am
Here is the code part in which i got error,and also i think i have already mention that it can get the name of employee which have three name like
(first name,last name,middle name) but i want the forth name is to be eliminated ....like (ahmed Ali khan Dilawar) i want Dilawar to be eliminated...please help me out to solve this problem
---Split Name
--Select @EmpName
Set @fname=Left(@EmpName,CHARINDEX(' ', @EmpName))
If len(@EmpName)-Len(REPLACE(@EmpName,' ',''))=1
Begin
set @lname=(Select Substring(@EmpName, CHARINDEX(@fname, @EmpName) +len(@FName),5000)) --AS LeftSide)
set @mname=''
End
else
begin
set @mname =(Select SubString(@EmpName, CHARINDEX(' ', @EmpName)+1,
CHARINDEX(' ', @EmpName,CHARINDEX(' ', @EmpName)+1)-CHARINDEX(' ', @EmpName)-1)
)
set @lname=(Select Substring(@EmpName, CHARINDEX(@MName, @EmpName) +len(@MName),5000)) --AS LeftSide)
end
December 30, 2010 at 2:48 am
ok, so if I am getting you correctly then you want only first three value. In that case take the full name in a variable(say x) and count the space in x variable.
Take another value(say y) and Put the condition to collect value till third space.
I hope you understand the logic.
Like
abc pqr stv xyz
Logic will be
Count the space and take the value in a variable upto third space.
so
x = 'abc pqr stv xyz'
y= 'abc pqr stv '
----------
Ashish
December 30, 2010 at 3:16 am
Hi Thanx for your reply..
i got the logic .. but actually i am new in SQL could please write the Code for this using my Code which u can alter that...
thanx please help me out
December 30, 2010 at 3:26 am
After a first glance at the 250 lines of undocumented code I don't think you'll need a those nested c.u.r.s.o.r.s *cough* at all.
I'm confident the issue can be resolved with a set based solution boosting performance.
If you're interested how it would look like, please read the first link in my signature on how to post ready to use sample data. Also, add your expected result based on your sample data and briefly describe your business case.
December 30, 2010 at 3:30 am
Brother this procedure is not written by me it has written by someone else.
and i am a new internee over this organization..
thats y i ask you to solve this issue
thanx
December 30, 2010 at 3:57 am
rizkhan92 (12/30/2010)
Brother this procedure is not written by me it has written by someone else.and i am a new internee over this organization..
thats y i ask you to solve this issue
thanx
So, please post the data we need so we can help you.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply