A doubt releated with cursor

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

0	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

0	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

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

Latest Posts