Create cursor in SQL server via transact SQL

[In2TheBlues]

Hi,
How can I create a cursor using t-sql. I want create it for a project work. Also how can I use it in store procedure. I am running Microsoft SQL Server 2008 in my system. Can anyone please post a complete procedure with steps and a general information about the the above process. I even want to know about the transact process of sql. As it is in my course study but I need some more simple examples. I am new to it so I do not know much about this. I will grateful for the help. Thanks in advance.

[Steve123]

There is a problem in working of some applications for example interactive and online one to work with the entire set as a unit. They need an proper mechanism to work in one row or in a small block of rows at a particular time. We will take an examples of medicines in a chemist shop. Now here the user want to see a record from the table. He will like to view more records. A cursor is a kind of database object which is used by applications. They use it to manipulate data by rows instead of record sets. The cursor is used to perform many operation in a row manner agaist the result set. This can be doing without returning to original table. Or if I say more elaborately a cursor tentatively returns a result set based on tables within a database. It can be used for the following thing :-

• It a particular rows of resultset it allows proper positioning.
• You can retrieve a single or a set of rows from the current position in the resultset.
• At the time of current position in the resultset it supports data modification.
• A visibility option is supported by it to track the changes made by other users to the database data in the presented resultset.
• It triggers to access the data in resultset alongwith a T-SQL statements in script and store procedures.

[Milton.J]

I am working on database cursor from a long time. It in useful thing. It allow to take a subset of data and output the information in multiple ways. I normally use them to make an automated email with cursor set inside the stored procedure. To create a cursor via transact sql follow the below instructions :

• You will first require to associate a cursor with a resultset of a t-sql statement. Then you have to define the characteristics of the cursor.
• To populate the cursor, execute the t-sql statement.
• Then you will need to retrieve the rows in the cursor. This called fetch. In fetch we can retrieve one row or a set of rows.
• If you want you can do some modifications on a row at the position of cursor.
• Now close the cursor.

Now in the second part we will see the syntax you will need in this.
To create a cursor you have to use DECLARE statement. Then you have to use the OPEN statement to make the cursor accessible. Then use the FETCH statement to get the records from the cursor for the coming process. Then at last use CLOSE statement to close the cursor on a temporary purpose. If you don't want the cursor you can remove its reference by DEALLOCATE statement.

[Spyrus]

I am listing a sample of cursor used in store procedure. You can refer to it. Either you can copy/paste and edit it or make you own from this example. Cursors are very useful in stored procedures. Cursor allows to get result just by passing a single query instead of multiple . I had listed an example of a hotel database.
create procedure au_sales (@author_id id)
as

 
/* declare local variables used for fetch */
declare @title_id tid
declare @title varchar(80)
declare @ytd_sales int
declare @msg varchar(120)
 
/* declare the cursor to get each model number of
    of mobile */
declare model_sales cursor for
select ta.title_id, t.title, t.total_sales
from titlemodel ta, titles t
where ta.title_id = t.title_id
and ta.au_id = @model_id
 
open model_sales

 
fetch model_sales
        into @title_id, @title, @ytd_sales

 
if (@@sqlstatus = 2)
begin
    print "The model is out of stock."
    close model_sales
    return
end
 
/* if cursor result set is not empty, then process
    each row of information */
while (@@sqlstatus = 0)
begin
    if (@ytd_sales = NULL)
    begin
        select @msg = @title +
           " -- Record sales this month."
        print @msg
    end
    else if (@ytd_sales < 875)
    begin
        select @msg = @title +
            " -- Sale decreases this month."
        print @msg
    end