Can I give a parameter in the cursor?

[ravula2000]

Can I declare a cursor with a parameter?
Actually I need to fetch rows from a cursor in a loop by passing a parameter.


DECLARE ACURSOR(ETYPE CHAR(1)) CURSOR FOR
SELECT EMPID
FROM emp WITH (NOLOCK)
WHERE (emp_sys_id <> 0 AND emp_type = ETYPE)

In the loop for every record I want to get EMPID from the above cursor by passing a Emp_type value.

Thanks

 

[mwa]

If i'm understanding your question right, I think what you are trying to do is cursor through a group of employee records where the emptype = a value. What you would have to do is this:

declare @w_emp_type char(1)

select @w_emp_type = 'X'

Declare emp_cursor cursor for
SELECT EMPID
FROM emp
WHERE (emp_sys_id <> 0 AND emp_type = @w_emp_type)


If you were wanting to cursor through the emptype and select the empid's for each emptype, then you would need to do a nested cursor:

Declare emptype_cursor cursor for
SELECT EMPTYPE
FROM EMPTYPE_TABLE

open emptype_cursor

FETCH from emptype_cursor into
@w_emp_type

while @@fetch_status = 0
begin

Declare empid_cursor cursor for
SELECT EMPID
FROM emp
WHERE (emp_sys_id <> 0 AND emp_type = @w_emp_type)


etc. etc.


But, Check my syntax... I'm doing this off the top of my head...

Hope that helps,
mwa

 

[ravula2000]

Thanks for your reply.
But I think we should not use declare statement in the loop.
I will check.

Thanks