![ms sql cursors ms sql cursors](http://4.bp.blogspot.com/-faChF-LRqbw/Vel4TbR6QVI/AAAAAAAACTE/FKc3QPM21aQ/s1600/sqlcur.gif)
![ms sql cursors ms sql cursors](https://imgs.developpaper.com/imgs/201406121531093.gif)
In this article we saw that how cursor based operations lead to performance issues. This is because we are only updating the salaries of employees from these departments. The WHERE clause is used to filter records where the name of the department is Sales, HR or IT. The JOIN has been implemented on the id column of the department table and the dep_id column of the employee table. Cursor is supported in all SQL Server versions i.e.
#MS SQL CURSORS HOW TO#
The employee and department tables have been joined so that we can access salary from the employee table and dep_name from department table in the same query. Here Mudassar Ahmed Khan has provided a tutorial with simple example that explains how to use Cursor in SQL Server Stored Procedures.
#MS SQL CURSORS UPDATE#
We have used a CASE statement to update the salary based on the dep_name. The above query will be at least 20 times faster than the cursor and will achieve same results. This is a sort of a hybrid between the cursors and the set-based solution and is often the best one for large changes on production systems. Without getting into the details of how cursors work, which Microsoft explains fully in Microsoft SQL Server 2005 Books Online, notice that when you use a WHILE loop, there is no requirement to declare, open, close, and deallocate anything.
#MS SQL CURSORS CODE#
WHERE ( dep_name = 'Sales' OR dep_name = 'HR' OR dep_name = 'IT' ) Cursors or loops can be used also to process batches of records if the entire set-based insert/update/delete will take too long and lock up the tables. The cursor also requires additional commands, which make the code look cluttered. Finally, if the department name is “IT”, we will increase the salary of the employee by 50% by multiplying the actual salary by 1.5. If the department name is “HR”, we will increase the salary of the employee by 20% by multiplying the actual salary by 1.2. In addition, if the department name is “Sales”, we will increase the salary of the employee by 10% by multiplying the actual salary by 1.1.
![ms sql cursors ms sql cursors](https://csharpcorner-mindcrackerinc.netdna-ssl.com/UploadFile/b5eb6a/use-cursor-in-sql/Images/cursor.png)
As you can see in the table rows below, the Id field of the track table is the foreign key in the matches table in the form of trackId. matches table which stores tracks matched in external data. Inside the WHILE loop, we will retrieve the department name from the department table where the id of the department matches the dep_id of the employee table. We have two sample tables : tracks which store audio track info. In the next example, we will use a cursor that will store the id and dep_id of the employee. The output of the above script will be as follows: Finally, we close the cursor we opened using CLOSE keyword. Inside the loop, we print the id and name from each row in the EmpCursor. When there are no more rows left, the returns a non-zero value and the loop will exit. In the above script, we used a WHILE loop to iterate through all the records in EmpCursor until returns 0. The returns 0 as long as there are more rows in a set of records. A better approach is to use a loop and call FETCH NEXT within the loop so that the cursor can iterate over all the rows.