A doubt releated with cursor

Posted by Amritha444 under Sql Server on 6/20/2011 | Points: 10 | Views : 1029 | Status : [Member] | Replies : 5
Hi all

I have a doubt regarding with cursor

my procedure is like this

set @fl1=1
set @fl2=41
set @SB_Common1= 'where HR_EMP_REPORTING.BranchID =1'
set @Month=5
set @Year=2011
set @mode='Salary'
SET XACT_ABORT ON
BEGIN TRAN


IF @mode='Salary'
BEGIN

DECLARE CUR_SALARY CURSOR FOR
Select HR_EMP_MASTER.Emp_Id,HR_EMP_MASTER.Emp_Code,HR_EMP_MASTER.First_Name from HR_EMP_MASTER INNER JOIN HR_EMP_REPORTING on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id + @SB_Common1

OPEN CUR_SALARY
FETCH NEXT FROM CUR_SALARY INTO @Emp_Id,@EmpCode,@EmployeeName

My problem happens when i add "where" condition to select statement .i want to add where condition according to the selection happening in front end.but when i adding where stsement error like below happening..

Msg 245, Level 16, State 1, Line 47
Conversion failed when converting the varchar value 'where HR_EMP_REPORTING.BranchID =1' to data type int.

But i want dataset according to the selection ..how to do it

Thanks in Advance




Responses

Posted by: Ndebata on: 6/20/2011 [Member] Starter | Points: 25

Up
0
Down
Hi
You can not use like that,
Try to change that parameter to allow null something like
@SB_Common1 int = null 

Then change the query like
Select HR_EMP_MASTER.Emp_Id,HR_EMP_MASTER.Emp_Code,HR_EMP_MASTER.First_Name from HR_EMP_MASTER INNER JOIN HR_EMP_REPORTING on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id WHERE  HR_EMP_REPORTING.BranchID = ISNULL(@SB_Common1,HR_EMP_REPORTING.BranchID)


When passing parameters, if you don not want filtering then don not add the parameter
else
pass the BranchID (int) only.

Thanks,
Debata


Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amritha444 on: 6/20/2011 [Member] Starter | Points: 25

Up
0
Down
The query i send from front end is not constant..according to the front end selection i am adding extra things to this query.its not constant

eg: when user select branch only the statement like where HR_EMP_REPORTING.BranchID =1 .when he select department only its like HR_EMP_REPORTING.departmentid=1..when click both HR_EMP_REPORTING.BranchID =1 and HR_EMP_REPORTING.departmentid=1..Is this not possible in sql

when add constant values to the select statement normally it works fine..but wht happens when adding this statement

Any solution????????

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 6/20/2011 [Member] Starter | Points: 25

Up
0
Down
Hi
instead of a single parameter @SB_Common1 ,
Try to add two nullable parameters as bellow
@BranchID int=null,

@DepartmentID int=null,

Then based on the selection in the front end add respective parameter values.
Then update the query as bellow
Select HR_EMP_MASTER.Emp_Id,HR_EMP_MASTER.Emp_Code,HR_EMP_MASTER.First_Name from HR_EMP_MASTER INNER JOIN HR_EMP_REPORTING on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id WHERE  HR_EMP_REPORTING.BranchID = ISNULL(@BranchID,HR_EMP_REPORTING.BranchID) 

AND HR_EMP_REPORTING.departmentid=ISNULL(@DepartmentID,HR_EMP_REPORTING.departmentid)


Do not pass the parameter from front end in case it is not selected.

It will work.





Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amritha444 on: 6/20/2011 [Member] Starter | Points: 25

Up
0
Down
ok i will check

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amritha444 on: 6/21/2011 [Member] Starter | Points: 25

Up
0
Down
hi
I tried your query but doesnt return any dataset
Select HR_EMP_MASTER.Emp_Id,HR_EMP_MASTER.Emp_Code,HR_EMP_MASTER.First_Name from HR_EMP_MASTER INNER JOIN HR_EMP_REPORTING on HR_EMP_MASTER.Emp_Id=HR_EMP_REPORTING.emp_id
where HR_EMP_REPORTING.BranchID = ISNULL(@BranchID,HR_EMP_REPORTING.BranchID) AND HR_EMP_MASTER.DepartmentId=ISNULL(@DepartmentID,HR_EMP_MASTER.DepartmentId)AND HR_EMP_MASTER.DesignationId=ISNULL(@DesignationtID,HR_EMP_MASTER.DesignationId)

this is the query.Pls check any problem on this query
I didnt get dataset

Thanks
Amrutha


Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response