--- title: "Implementing Concept of Cursor in SQL Server" description: "A Cursor is a database object that represents a result set and is used to manipulate data row by row. When a cursor is opened, it is positioned on a r" author: "Anonymous User" published: 2011-01-27 updated: 2018-03-22 canonical: https://www.mindstick.com/articles/428/implementing-concept-of-cursor-in-sql-server category: "database" tags: ["database"] reading_time: 4 minutes --- # Implementing Concept of Cursor in SQL Server A Cursor is a database object that represents a result set and is used to manipulate data row by row. When a cursor is opened, it is positioned on a row and that row is available for processing.\ ##### Some system stored procedure related with cursor 1) **sp_cursor_list returns a list-** of cursor currently visible on the connection and their attribute 2) **sp_describe_cursor-**Describe the attributes of a cursor such as whether it is forward only or scrolling cursor. 3) **sp_describe_cursor-**_columnsDescribe the attributes of the columns in the cursor result set. 4) **sp_describe_cursor-**_tablesDescribe the base tables accessed by the cursor. ##### Steps for implementing a simple cursor in your program 1) A programmer makes a cursor known as DBMS using declare……cursor statement and assigning the cursor a (compulsory) name. ##### Syntax for declaring cursor ``` declare cursor_name cursor for select .... from ...where     declare is a keyword used for declare a new cursor     cursor_name is the name of the cursor that you want to use     for is a clause after that you have to told the select statement ``` 2) After declaring the cursor we can access the data. Before accessing the data of the cursor it must be opened by using open statement. Directly following a successful opening, the cursor is positioned before the first row in the result set. ##### Syntax for opening cursor open **cursor_name** 3) After opening cursor on a specific row in the result set with the fetch statement. A fetch operation transfers the data of the row into the application. Simply we can say by using fetch statement we read one by one row at a time in forward only by default. ##### Syntax for fetching cursor fetch cursor_name 4) Once an application has processed all the rows of a table or the fetch operation is to be positioned on a non-existing row. The final step involves closing the cursor using the close statement. ##### Example which demonstrate the use of cursor ``` declare stu_cursor cursor for select * from Student -- A cursor is declaredopen stu_cursor -- after this steps cursor is opened.fetch stu_cursor --Read one row at a time and display the value of row.Again and again executing this statement will read next rows.close stu_cursor --close the open cursor ``` ##### Scrollable Cursor Programmers may declare cursor as scrollable or not scrollable. The scrollablity indicates the direction in which a cursor can move. With a non scrollable cursor, also known as forward only can fetch each row at most one. A programmer may position a scrollable cursor anywhere in the result set using the fetch SQL statement. The keyword scroll must be specified when declaring the cursor. The default is non-scroll cursor. ##### Syntax for declaring scrollable cursor ``` declare cursor_name senstivity scroll cursor for select .... from ... ``` The target position for a scrollable cursor can be specified relative to the current cursor positions or absolute from beginning of the result sets. ##### Syntax for fetching records in cursor ``` fetch [next/prior/first/last] from cursor_name   fetch absolute n from cursor_namefetch relative n from cursor_name ``` Scrollable cursor can potentially access the same row in the result set multiple times. Thus, data modification (insert, update, delete) operations from other transaction could have an impact on the result set. A cursor can be sensitive or insensitive to such modification. ##### Example which demonstrate the use of Scrollable cursor ``` declare cur_std scroll cursor for select * from student --Declare a cursor named cur_std  open cur_std -- Open the cursor  fetch next from cur_std -- Fetch next records in the cursor and display it  fetch absolute 3 from cur_std  --Fetch 3 records in the cursor  fetch prior from cur_std --Fetch previous record in the cursor ``` ![Implementing Concept of Cursor in SQL Server](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAARQAAACRCAIAAACNGNDxAAAABGdBTUEAALGPC/xhBQAAAAlwSFlzAAAOwgAADsMBDDN7wQAAD7NJREFUeF7tne9zFEUax/cvsco/wKtQASr4H/j+EKssoUio/Q+uFMpXIpZCLHlxQq0Fli9Oyzu8ELirqMUdcqXWCSxGE0g8EAXCLyMhvyEkoXJP9/PMTE/P7O5Mb2Yyu/v91FOz3U8/3dPd09/pmZQjpZmUrAEANBAPAI5APAA4AvEA4AjEA4AjEA8AjkA8ADgC8QDgCMQDgCMQDwCOQDwAOALxAOAIxAOAIxAPAI5APAA4AvEA4AjEA4AjpSUAgBOlXxpx3ePmzZtff/31xx9//GfN+wZRDwBtT+lWYu7cuUPKuX379sLCwjwAHU/pXiPuekxOTh45cmRxcXF+Xolnbi6w2dk5MtMDg7W9lX7TkDA48fDhw9nZWTpy1mRqauro0aMLCyQeUsus/AEBgE4lJB5Wzvbt2+nIfhMWD207MzOkrukHD6ZgsE62QDysnGefffbVV1+tK575h9MzVHNy8ncYrJNNxMPKeeaZZ1544YXjx4/XEQ896k1NPaSa9+//BoN1sol46GX/ueee27Jly969ewcGBuqIh3apGzduXr3689jYTzBYJ1to53n55Zf7+/s///zzOuIhwV2//uv4+P9GRq7AYJ1s9jvPW2+99dVXX9URz71792nbGR0d+/77H2GwTrZAPHRk/QwNDdURz92798bHrw4Pj1y4cAkG62QLiYdg/dCRsyYsnlsTt0lz3357/ptv/guDdbLZ4qkDi+fnn385d+6bs2f/869/n4PBOtlKDxrxu8f09PSJEycuXLj4ww+jly79UK1+D4N1spXoCY0gYXCiDjMzM8PDwwMDAx8CAD78sEQPbAAAB0ofAQCcSP0ZNgCAgXgAcATiAcARiAcAR0rv9L/fTibDAiB7SvK/oGoLIB6QJ+0mns4xuYBg42g38ax2BhBPEbDFU65UY627bJtUKBK0pObCnNOcDvPRR3+R4tYE4ikIMeKpVNfEKpSNt1YRT4WGEWZycrJcqUhxawLxFITGO4+52wTWXUM8197bVgpRHpKSdFA72967Jpmk0JKaDeOLh9LVB2tkn1yYKZcrXNqirKysQDxFwBaPeau+ePGipNbW6OGHE0tLSxTT3V3mrI216J00oOCKKatHlxQPZ3x8vFqt0nBIOeQh8UhxawLxFIR64vEFQ9CrAh1JOXTno5ht27ax38Ze7kNlt73HVTzTYairlWqVOk8JetVhI/FIcUMuHdhCu+euv0mW81sOXJLsxgDxFARbPLS26D5NN2n9ph3A64/ftlOIx89Sgh/jSlLsO1TerMVpdSyXOUQXheJrQEtqKgx1Vco8aPORLaiqdHX9+nUJjaX65mbFm1U/Sz3wsxvE8vIyxFMEYsQjqfDOQ6uQH7jptYFi6omH17jA244hDkl6O5KKLw8Z5RJgHpU3HK88MdCSks9fPajbExMT/PVFtaqUQybRelD0/En6kegoF/Zv3rx//87N+y/o7Kc7N+/cSR7ObRQQT0FI9NhGT2u057ByGovHl4G/0tWvCctCUhLo1+K0eRSvEV8DWlLyybgHDYe0ofdOhdo/SUNEpbJWKVMp7UKsH6lgcV6J5zxpZv95yqnfT7WHi1SPCM4HDqvcq8s5xc5PVbzRQqROjTYZiKcg2OI5cuQIvVvzUvPfFvhIRrLRj22VpI9tvGGolR+/XVC52p3MWpw2j+zXSLzkbGhJkQxMqM9S5iHi0VBAd7lM4mH9xHD29U2bXj/Lx+vHXwrS8hPEUGnppeOcVQkraxC04BUEnvptCo8ePYJ4ikCMeCSldyH+CwFBmlHbjn7yoZikf22LqoeT6shxXMF7KiMo6cuGjxwViteREWhJXQvD4uE9Uz25PfDEUymvldUQypVuiukul6WCxZl9mzbtO6N+dhw7tqO045h46Kg3BI/ApcKlppn1HRoO98s4bQYoAlfQiAbiKQj1xMOParzylHi8d4Y04lGrXstC7xkKKVYFRt7PlmhTI5e0w7VUA1Z8LLSkrobxxcPKUeIx9iLy042c5ETDkQoWZ/Z1de07w79dXTuOeR46kpIkKARJrGQUeVn61Q0RfgueI22bEE9BiBGPvBvo5zQyflTTT2tVKmVqimdDoSX1UxiWiq8c0j971LZT7p6YmFA7jxaPVLD4cm9X194vOVF68YPA4+e9InX0Q7v2fhDOfvnBi174T5SMtpCkTZVQLC4uQjxFIEY8dOenxWQdzQQjFYoELamxMCHxVNfK1W66C3AwOcfHx9Wrnf6bm1Sw+OK1rq7XvpCMJvBUXlQbISF5KjHztbKKqCtZmwzEUxBs8bQ0tKQuhyFhXLx4kY60vbCJeMrdZOpPbWX9ztNdlgobwtHtpe1HJZ2AhYUFiKcItJt4RsIoYZTLu3btoiMbi4fe3JSWvOdSEo9UyI8jf+Q9RfGHP/1TvEmAeApCu4nnx0awVKImxa3A/Pw8xFME2k08wx0AxFMQ2k08MFhu1lbiASBPSif+/g8YDOZgSjyio2yYm5uTVDEoWn+yIOcxtvqUOvcf4mlDIJ5UFFc8s7OzkioGRetPFuQ8xlafUuf+QzxtCMSTijYSz7XDzz9/uOZ/ON00EM+6A/FkxcxM8NlzDFGpZCyeBv1pC3IeY+rT8SXO+EInx3m6MhfP9PS0pBKS8Zym7k8LkvMYHU9XGPE4T5cSz8rK6uOl5SxsdfXpvfu/We1f7pfP2nr6xx5feben593L5KcEewn2ZGAJ+3Oojx2lvlMqYNDLEsqjYvb06e729J8+JIkx1Vowim2HrgSnyNMajzE6IoqJzn+ysSQ53eOl0/4JrQmkc6nZ7jvNFaljXpWcjPsvakiJEs/yyuqjx0+yMOrZnbv3wu2f6i3tGaTE5f4eStCxp3/08djBnlLvoA4Y3EPzNxrEr6cl6k/J64n0zY/0PCqm5+BlruIlZBQ6G1s3L2s8xsBv9twaddKxJDid0bgq6jk4qBv0m5Uwo2KOxv0XNaQkW/E8ffr09p274fbVVKo7zqExleUZNC+PmV5vS9ofLvLTlFAhGrO3VsIMU3iLL19rPEayWiPiojRjSTelbP4pPD9tOEpddN/sOxWE5WLcf1FDSrR4llcWHy1lYatPn07cvhvb/slemt6+k6NqBkf4yEVmer0taX/YKWl9sxw1PH6MlaAjtcB1N84aj7HOiExPsrGkm1K26EmVek6d7PV6laNx/0UNKcldPOqq8BxdUQ8GegcfUWm696iAEUpZc71+lrg/XpFK01LzlpF+JK8pHmMUutmNEVLjMY5GRmT1PM1YEpzOaIp1y5MsJ/L9ag/M6LrXsWbF82R5ZWHxcRZGD5Q3b9222lfy0Gw9eHlh5NDWnkMj5KcEe3t6xJOBpegPmZf2AxTkMftsJRYH1SLQce+MBKfI0xqPMToiiqEhSN7zJBtLktMZTZV6T/rTxc6+AV1loNcPztW4/6KGlCjxLD1Znl94lIVRz27cnMiu/bRWtP5kYc2OkVZx76DtrG3rNKWDu0u9A7YzD+P+ixpSosWztDw3v5iFra6u/nrjVnbtp7Wi9ScLcxrjyd16W9D0vP2jVVrP1mFKSa60R70zYvtzMe6/qCElSjyPl57Mzi1kYSurq7/8ejO79tNa0fqTheU8xlafUu6/qCElSjxXAOhsRA0pUeJZyZLl5WVJFYOi9ScLch5jq08p9V/UkBIlnpmMoSfaQh0BMBEppEfEI7kMoMbvT80Vx0g/0rP2JdMLGiXn060vRRcPrVdrBW+gtfSVTkjOY2zpKW2m89h52hCIJzlFFw92npzJeYwtPaXNdL6+eIx/sM0VatxavoFV3/b/Uc5X/irOT3aJp7Trs+QebZ+9Utr9SZCNN6okPYtF/WtazX2itV6fSTbRQp0F4f8bY+EL29SFbrD+zCmtPzlJhpx2WhrFZyEeOmfMHDtAjVMr1grWRmt96xtVnVYq0uueElve/k6VDr+xRZcm8agE97axeOpPllpE5XKqq2PDVyvtNY7SRAu1xqguqlxPP6kSmqzEE5pSHlStodXymySJMWkUX7/z9dm4nSfQgGF/3W1uL2o7SuLhuuuw89BM03iHmlIPX6201zhKEy0kWRDh5rPbecJTWn9ykgw5SYxJo/h1EE+NL7FP95X2DNrOdEaN03q1VrA2tV1sPjBsOr87sNX3cDqJh9MJxVNvsmii1RLy5ls9b/CS8tcWJVSRKvGQRUeVxKEfUnQbh72wSIxuv0GM3w7dtylDt2/2KiS4BgkWhDqN0Uhm4qHzqIb1UL0h++Kxp1H7rQmJi+EJEUdMDEFh4mjwGJ5grmoi4qnxMemp5j+Lpcat5WuY/7glz29JpBL1cLr5nYcmnKfeu7jer7443pULXwnx0I9xJcmjHMaFlBivMifjY6LteLV8JLgmDRdEpFU6mZzWgTqnoxNxu9Jl/on23/fbE2IQiVFJq9dSyyjhadTJWIouHlp51gq2jZ7E9CNcEqlEPZxueufRl8VHzzivKfXMMRR6+gjF8hX1rxCnYz1SgdFtJqlleqRuU3dT1YxdPSPxmD02JsoflDUi389FSWK42/Vj/HQc9eeqPp544j8mNT45dDVq3Fq+ceat+w1856FZDtaPN+VKLYe1YgIFhe5mHBi9WrEea4EmrCWeyEl1MpY6C4Kq+s0YZCMeOpk1pXy/kP4nmMZ1i6lJ0cVD69VawWS0afga8Hce17+2sTW181iT7GXpwsiVUSlZYZT0FoVcOAoXl0qRx2xO0kGMJOvGqFSonehJa1JzQagxeI2EMBpPT4oppbeVYFCREZkV6sR4Hi8ZifFL/GnUhbHUnKsEiHhqfIk9SOLhr2SdjRq3lq9vtG94BIs+cBrbi1Dbo62ZnUdmPUDm37hWxjVSXh+u57ueJ2LXAUFNSJDKx8bUbscvUfgV46i1IPzTM0YbxtjSU+N0sVOqz+oNyh6RMVh/QmJiJBMMwI4xXTyNOiyWdRBPdl8mU+M0BGsFb6A1M1mtQs5jbOkpbabznngy+zKZGreW78YaKVmG3r5APMlZB/Fk9xktNY6dJ2dyHmNLT2kznRfxyNeoGUCNW8t3Yw07z7rT6eKRD1IzgBrHzpMzOY+xpae0mc6LeLKG9FOoIwCM6MAJJR5JAgDSAPEA4AjEA4AjEA8AjkA8ADgC8QDgCMQDgCMQDwCOQDwAOALxAOAIxAOAIxAPAI5APAA4AvEA4AjEA4AjEA8AjkA8ADgC8QDgCMQDgCMQDwCOQDwAOALxAOAIxAOAIxAPAI5APAA4AvEA4AjEA4AjEA8AjkA8ADgC8QDgCMQDgCMQDwCOQDwAOALxAOAIxAOAIxAPAI5APAA4AvEA4AjEA4AjEA8AjkA8ADgC8QDgCMQDgCMQDwBOrK39HyZDsAo5BdB4AAAAAElFTkSuQmCC) ``` close cur_std ``` **Note: For efficiency point of view it is advisable not to use the concept of the cursor until you exactly needs for processing single row at a time.** --- Original Source: https://www.mindstick.com/articles/428/implementing-concept-of-cursor-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.