In this article I am explaining how to fetch records using a cursor in SQL Server 2012. A cursor in SQL serever 2012 is a database object that points to a result set. We use a cursor in SQL Server 2012 to retrieve a specific row or data from a result set.
There are two ways to implement a cursor in SQL Server 2012:
- Standard Database APIs : We can implement a cursor through standard database APIs.
- Through Transact-SQL : We can implement a cursor through Transact-SQL
Implementation of Cursor in SQL Server
To create a cursor we use various clauses. A DECLARE clause is used to define a new cursor, an OPEN clause is used to open the cursor and a FETCH clause is used to retrieve data from a result set. CLOSE is used to close a cursor. A DEALLOCATE clause is used to delete a cursor.
Types of cursors in SQL Server 2012:
- Dynamic cursor
- Keyset cursor
- Static cursor
- Forward-only cursor
- Forward-only KEYSET cursor
- Forward-only stataic cursor
- Fast_Forward cursor
Statement that creates a table
createtable mcninvoices
( invoiceidint notnull identityprimary key, vendoridint notnull, invoicenovarchar(15), invoicetotalmoney, paymenttotalmoney, creadittotalmoney ) |
Statement that inserts data into a table
insertinto mcninvoices values (20,'e001',100,100,0.00)
insertinto mcninvoices values (21,'e002',200,200,0.00) insertinto mcninvoices values (22,'e003',500,0.00,100) insertinto mcninvoices values (23,'e004',1000,100,100) insertinto mcninvoices values (24,'e005',1200,200,500) insertinto mcninvoices values (20,'e007',150,100,0.00) insertinto mcninvoices values (21,'e008',800,200,0.00) insertinto mcninvoices values (22,'e009',900,0.00,100) insertinto mcninvoices values (23,'e010',6000,100,100) insertinto mcninvoices values (24,'e011',8200,200,500) |
Statement that shows all data of mcninvoicetable
Statement that creates the mcnvendors table in SQL Server 2012
createtable mcnvendors
(
vendoridint,
vendornamevarchar(15),
vendorcityvarchar(15),
vendorstatevarchar(15)
)
|
Statements that insert data into the mcnvendors table in SQL Server 2012
insertinto mcnvendors values (20,'vipendra','noida','up')
insertinto mcnvendors values (21,'deepak','lucknow','up')
insertinto mcnvendors values (22,'rahul','kanpur','up')
insertinto mcnvendors values (23,'malay','delhi','delhi')
insertinto mcnvendors values (24,'mayank','noida','up')
|
A statement that is used to fetch data from the mcnvendors table in SQL Server 2012
A statement that is used to create a Cursor in SQL Server 2012
Here I am going to create a invoiceinfo_cr cursor which is global and static, which means it is accessible by all users and it is scrollable but not sensitive to a database. This cursor creates a resultset which retrieves data from two tables using join.
DECLAREinvoiceinfo_cr CURSOR
GLOBALSTATIC
FOR
SELECT vendorname,vendorcity,invoicetotal
FROM mcnvendorsjoin mcninvoices
ON mcnvendors.vendorid=mcninvoices.vendorid
|
Statement that is used to open a cursor in SQL Server 2012
Statement that retrieves the next row from a cursor in SQL Server 2012
Another statement that retrieves the next row from a cursor in SQL Server 2012
Statement that retrieves a previous row from a cursor in SQL Server 2012
Statement that retrieves the first row from a cursor in SQL Server 2012
Statement that retrieves the last row from a cursor in SQL Server 2012
Statement that retrieves the second row after the first row of a cursor in SQL Server 2012
Statement that retrieves the second row after the current row from cursor in SQL Server 2012
Statement that retrieves the second row before the current row from a cursor in SQL Server 2012
No comments:
Post a Comment