while executing giving syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

while executing giving syntax error

Author

Topic

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-05-21 : 10:00:12

-- EXEC Get_emp_details 'AVIJIT'
CREATE PROCEDURE Get_emp_details
(
@ENAME varchar(50)
)
AS
BEGIN

set nocount on

select @ename = ltrim(rtrim(@ENAME))

DECLARE @company varchar(50)
DECLARE @location varchar(10)
DECLARE @userId varchar(8)
DECLARE @langId smallint

IF

@company = 'ccl'
@location = 'NV'
@userId = '4'
@langId = '1'

BEGIN

exec perdb..PI01_C30 @company,@location,@userId,@langId

SELECT DISTINCT
a.H01_emp_num AS ECODE,
ISNULL(RTRIM(a.H04_First_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Middle_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Last_Name), '')AS EMPLOYEENAME,
C.C12_Positiondesc AS DESIGNATION,e.C10_Job_desc AS LOCATION

FROM COMMON.dbo.H04_NAMEMASTER A,
PERDB.DBO.H10_EMPASGN B,
PERDB.dbo.C12_Position C,
PERDB.dbo.C09_Gradeheader D,
PERDB.DBO.C10_Job e

WHERE A.H01_EMP_NUM NOT IN (SELECT H01_EMP_NUM FROM PERDB.DBO.E01_EXIT_DETAILS)
AND a.H01_emp_num = B.H01_Emp_Num
and ISNULL(RTRIM(a.H04_First_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Middle_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Last_Name), '') like +'%'+@ENAME+'%'
AND b.C12_Positioncode = C.C12_positioncode
AND B.C10_Job_code = E.C10_Job_code
AND D.C09_grade_CODE = B.C09_grade
AND B.H10_EFFECTIVE_DATE_TO IS NULL
END

-- UNION ALL

ELSE
IF
@company = 'GSC'
@location = 'GM'
@userId = '4'
@langId = '1'
BEGIN
exec perdb..PI01_C30 @company,@location,@userId,@langId

SELECT DISTINCT
a.H01_emp_num AS ECODE,
ISNULL(RTRIM(a.H04_First_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Middle_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Last_Name), '')AS EMPLOYEENAME,
C.C12_Positiondesc AS DESIGNATION,e.C10_Job_desc AS LOCATION

FROM COMMON.dbo.H04_NAMEMASTER A,
PERDB.DBO.H10_EMPASGN B,
PERDB.dbo.C12_Position C,
PERDB.dbo.C09_Gradeheader D,
PERDB.DBO.C10_Job e
WHERE A.H01_EMP_NUM NOT IN (SELECT H01_EMP_NUM FROM PERDB.DBO.E01_EXIT_DETAILS)
AND a.H01_emp_num = B.H01_Emp_Num
and ISNULL(RTRIM(a.H04_First_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Middle_Name), '') + ' ' + ISNULL(RTRIM(a.H04_Last_Name), '') like +'%'+@ENAME+'%'
AND b.C12_Positioncode = C.C12_positioncode
AND B.C10_Job_code = E.C10_Job_code
AND D.C09_grade_CODE = B.C09_grade
AND B.H10_EFFECTIVE_DATE_TO IS NULL
END

--UNION ALL

SELECT a.ECode AS ECODE ,
a.EName as EmployeeName ,
b.dept_name as Designation,
c.locn_name as Location
FROM scl_EMPLOYEE_MASTER a,
scl_department b,
scl_location c

where a.dept_code = b.DEPT_CODE
and a.locn_code = c.LOCN_CODE
and a.CATEGORY = 'UNEMPLOYED'
and a.Ename like +'%'+@ENAME+'%'

set nocount off
END

while executing giving error:-
1.Incorrect syntax near '@location'.
2.Incorrect syntax near the keyword 'ELSE'.
3.Incorrect syntax near '@location'.

please someone help me to correct the logic....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-24 : 07:31:25

What are you trying with your IF condition? As laid out, I can't figure out if you are trying one IF condition or if (excuse the pun) you are looking to join 4 clauses together

IF
@company = 'ccl'
etc

avijit111@gmail.com
Yak Posting Veteran

55 Posts

Posted - 2010-05-27 : 09:44:27

Sorry my post was not properly synchronised step by step.....
i am extremely sorry for that......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources