• Creating Cursors in SQL

    Miranda65M Member

    All of the employees that work on project p1 are moving to p2 and all of the employees working on project p2 are moving to project p1.
    I am to write a cursor to manage this, I’m not really sure what to do next I’ve got some code written down can someone please help?

    DECLARE employees CURSOR
    FOR SELECT Emp_no, Project_no FROM Works_on
    INNER JOIN Employee
    ON Works_on.Emp_no = Employee.Emp_no
    INNER JOIN Project
    ON Works_on.Project_no = Project.project_no
    WHERE Project_no = 'p1'
    OPEN employees
    DECLARE @empno int
    DECLARE @project_no char(3)
    FETCH NEXT FROM employees INTO @empno, @projectno
    SELECT @empno, @project_no
    While @@Fetch_Status=0
          Select @empno, @project_no
          FETCH NEXT From employees INTO @empno, @project_no
    CLOSE employees
    DEALLOCATE employees

    The table has these rows; Emp_no, Project_no, Job, Enter_date

  • Amit Member

    Is this for an assignment? And did they ask for an explicit cursor or implicit?

    Each time you use a SELECT statement, there is an implicit cursor that is used.

    Many companies do not use explicit cursors .. period. They have their limitations. Here is a pretty good overview:

    SQL Server Cursor Analysis

    The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:

    Online Transaction Processing (OLTP) – In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
    Reporting – Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.

    Serialized processing – If you have a need to complete a process in serialized manner, cursors are a viable option.

    Administrative tasks – Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
    Large data sets – With large data sets you could run into any one or more of the following:
    Cursor based logic may not scale to meet the processing needs.

    With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor based approach may meet the need.

    Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
    If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.

    SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.

    Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult.

    I would do something like this if you needed to use an explicit cursor

    l_project_no employee.project_no%TYPE;
    SELECT project_no
    INTO l_project_no
    FROM employees'

    Starting like this, you have basically saved the old project number in a separate temp field called l_project_no

    Then just go ahead and so a: if l_project_no = 1 then change project_no to 2, and if l_project_no = 2 then change project_no to 1

  • ShikhaTan Member

    It is for a class assignment, the professor didn’t specify what type of cursor it needs to be, when I was googling for help I did notice the two different types.

    The code I have so far does what you’re saying too doesn’t it?

    FOR SELECT Emp_no, Project_no FROM Works_on
    INNER JOIN Employee
    ON Works_on.Emp_no = Employee.Emp_no
    INNER JOIN Project
    ON Works_on.Project_no = Project.project_no
    WHERE Project_no = 'p1'

    Except I probably should change the where statement since it’s supposed to go both ways.

    The code you have written up is that just taking the data from the table into a temporary field? How would you then update the old values so that project p1 are moving to p2 and all of the employees working on project p2 are moving to project p1

  • SapnaVishwas Member

    It becomes a normal update command; no inner joins etc required as it is all in the same table.

    So something like:

    UPDATE employees
    SET project_no='1
    WHERE l_project_no='2';
    UPDATE employees
    SET project_no='2
    WHERE l_project_no='1';

    Maybe I need to clarify something though:

    You have ONE table, that has fields of Emp_no, Project_no, Job, Enter_date.

    As there is the one table, the cursor basically creates a temp field so that you don’t have to get twisted in the joining.

    If becomes a table with Emp_no, Project_no, Job, Enter_date, l_project_no

    The first part copies the current project into last project (project_no to l_project_no)
    Now you are free to just update the project_no field.

Viewing 3 reply threads
  • You must be logged in to reply to this topic.