How to use cursor............?

Question

1.00/5 (1 vote)

See more:

SQL

declare @emp varchar(50)
declare @oldfscode varchar(50)
declare @newfsname varchar(50)
declare @fstype varchar(50)
declare @parentcode varchar(50)
declare @areaname varchar(50)
declare @areacode varchar(50)
declare @parentype int
declare @grade varchar(50)
declare @qual varchar(50)
declare @add1 varchar(50)
declare @add2 varchar(50)
declare @add3 varchar(50)
declare @add4 varchar(50)
declare @pin char(50)
declare @phone char(50)
declare @email varchar(50)
declare @div char(50)
declare @newcode varchar(50)
declare @desg varchar(50)


declare toupdate cursor for 
select
empcode,fscode,fsname,type,parentcode,area,
areacode,parenttype,grade,qual,add1,add2,add3,add4
,pin,phone,email,div
,newcode,todes from r

open toupdate 
fetch next from  toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
while @@FETCH_STATUS=0
begin
   update Tbl_FS_Mst set C_Name='vacant - '+' '+@desg+' '+'('+@areaname+')',C_EmpNo='000000' where C_Code=@oldfscode
   update tbl_fs_emp_rel set d_date_to='2015/03/03' where c_fs_code=@oldfscode
   insert into Tbl_FS_Mst 
   select  @newcode,@newfsname,@fstype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,
'','',@areacode,@parentcode,@emp,'','','','',0,@email,0,GETDATE(),GETDATE(),'admin',@parentype,@div
  insert into tbl_fs_emp_rel select @newcode,@emp,GETDATE(),null,GETDATE()
  update Tbl_Doc_Stock_Chem_Add_Mst set C_FsCode=@newcode where C_FsCode=@oldfscode
  update Tbl_Cust_Div set c_fs_code=@newcode where c_fs_code=@oldfscode
  update Tbl_STP set C_FS_Code=@newcode where C_FS_Code=@oldfscode
end
close toupdate
deallocate toupdate

Only one record it takes..What problems it could be..

Posted 3-Mar-15 23:50pm

Member 11337367

Updated 4-Mar-15 0:13am

Suvendu Shekhar Giri

v3

Add a Solution

4 solutions

Solution 1

That's what it is supposed to do: a Cursor is used to fetch data one row at a time for processing.
In order to move to the next row, you have to explicitly use the FETCH NEXT command.

See here: http://en.wikipedia.org/wiki/Cursor_(databases)[^]

Posted 4-Mar-15 0:26am

OriginalGriff

Comments

Member 11337367 4-Mar-15 6:29am

In my query what i have to change or how can i alter my query..

OriginalGriff 4-Mar-15 6:46am

I can't say - there is no code there that uses the cursor - it just sets it up!

Solution 4

There's no need for a cursor in this case:

SQL

UPDATE
    T
SET
    C_Name = 'vacant - ' + ' ' + S.todes + ' ' + '(' + S.area + ')',
    C_EmpNo = '000000'
FROM
    Tbl_FS_Mst As T
    INNER JOIN r As S
    ON S.fscode = T.C_Code
;

UPDATE
    T
SET
    d_date_to = '2015/03/03'
FROM
    tbl_fs_emp_rel As T
    INNER JOIN r As S
    ON S.fscode = T.c_fs_code
;

INSERT INTO Tbl_FS_Mst
SELECT
    newcode,
    fsname,
    type,
    grade,
    qual,
    add1,
    add2,
    add3,
    add4,
    pin,
    phone,
    '',
    '',
    areacode,
    parentcode,
    empcode,
    '',
    '',
    '',
    '',
    0,
    email,
    0,
    GETDATE(),
    GETDATE(),
    'admin',
    parenttype,
    div
FROM
    r
;

INSERT INTO tbl_fs_emp_rel
SELECT
    newcode,
    empcode,
    GETDATE(),
    Null,
    GETDATE()
FROM
    r
;

UPDATE
    T
SET
    C_FsCode = S.newcode
FROM
    Tbl_Doc_Stock_Chem_Add_Mst As T
    INNER JOIN r As S
    ON S.fscode = T.C_FsCode
;

UPDATE
    T
SET
    c_fs_code = S.newcode
FROM
    Tbl_Cust_Div As T
    INNER JOIN r As S
    ON S.fscode = T.c_fs_code
;

UPDATE
    T
SET
    C_FS_Code = S.newcode
FROM
    Tbl_STP As T
    INNER JOIN r As S
    ON S.fscode = T.C_FS_Code
;

Posted 4-Mar-15 8:29am

Richard Deeming

Comments

Wendelius 4-Mar-15 14:45pm

This would be an efficient way to do the task. My 5.

Solution 2

Check this...
How to use Cursor in Sql[^]

Posted 4-Mar-15 0:35am

Raja Sekhar S

Comments

Member 11337367 4-Mar-15 6:36am

Can you check and give solution for my query..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2015-03-04T07:56:00

Fetch the Next row in order to preceed the loop.

SQL

declare @toupdate cursor for
select
empcode,fscode,fsname,type,parentcode,area,
areacode,parenttype,grade,qual,add1,add2,add3,add4
,pin,phone,email,div
,newcode,todes from r

open @toupdate
fetch next from  @toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
while @@FETCH_STATUS=0
begin
  --Query


  fetch next from  @toupdate into
@emp,@oldfscode,@newfsname,@fstype,@parentcode,@areaname,@areacode,
@parentype,@grade,@qual,@add1,@add2,@add3,@add4,@pin,@phone,@email,
@div,@newcode,@desg
end
close @toupdate
deallocate @toupdate

How to use cursor............?

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us