How To Declare and Use Cursor Variables?
Submitted by: AdministratorThere are two ways to representing a cursor:
1. A cursor name - A static name representing a cursor object. A cursor name should be linked to a cursor object in the DECLARE statement.
2. A cursor variable name - A variable name pointing to a cursor object. A cursor variable name should be declared with the CURSOR data type. It should be then assigned with a cursor object using the SET statement.
The tutorial exercise below shows you how to declare a cursor variable and assign a cursor object to it:
USE GlobalGuideLineDatabase;
GO
-- declare a cursor variable
DECLARE @ggl_cursor CURSOR;
-- assign a cursor object
SET @ggl_cursor = CURSOR FOR
SELECT id, url, notes, counts, time FROM ggl_links;
Submitted by: Administrator
1. A cursor name - A static name representing a cursor object. A cursor name should be linked to a cursor object in the DECLARE statement.
2. A cursor variable name - A variable name pointing to a cursor object. A cursor variable name should be declared with the CURSOR data type. It should be then assigned with a cursor object using the SET statement.
The tutorial exercise below shows you how to declare a cursor variable and assign a cursor object to it:
USE GlobalGuideLineDatabase;
GO
-- declare a cursor variable
DECLARE @ggl_cursor CURSOR;
-- assign a cursor object
SET @ggl_cursor = CURSOR FOR
SELECT id, url, notes, counts, time FROM ggl_links;
Submitted by: Administrator
-- execute the query and fetch results
<pre>OPEN @ggl_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
@counts INT, @time DATETIME;
FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT CONVERT(CHAR(5),ISNULL(@id,0))
+CONVERT(CHAR(18),ISNULL(@url,'NULL'))
+CONVERT(CHAR(20),ISNULL(@notes,'NULL'))
+CONVERT(CHAR(4),ISNULL(@counts,0))
+CONVERT(CHAR(11),ISNULL(@time,'2007'));
FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
END
CLOSE @ggl_cursor;
DEALLOCATE @ggl_cursor;
GO
101 globalguideline.com NULL 0 Jan 1 2007
102 globalguideline.com/sql Nice site. 8 Jan 1 2007
1101 globalguideline.com/xml NULL 0 Jan 1 2007
202 www.google.com It's another search 0 Jan 1 2007
2101 globalguideline.com/seo NULL 0 Jan 1 2007
2102 globalguideline.com/html NULL 0 Jan 1 2007
301 netscape.com Added long time ago!0 Jan 1 1999
302 yahoo.com Added today! 0 Jul 1 2007</pre>
Submitted by: Administrator
<pre>OPEN @ggl_cursor;
DECLARE @id INT, @url VARCHAR(80), @notes VARCHAR(80),
@counts INT, @time DATETIME;
FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT CONVERT(CHAR(5),ISNULL(@id,0))
+CONVERT(CHAR(18),ISNULL(@url,'NULL'))
+CONVERT(CHAR(20),ISNULL(@notes,'NULL'))
+CONVERT(CHAR(4),ISNULL(@counts,0))
+CONVERT(CHAR(11),ISNULL(@time,'2007'));
FETCH NEXT FROM @ggl_cursor INTO @id, @url, @notes,
@counts, @time;
END
CLOSE @ggl_cursor;
DEALLOCATE @ggl_cursor;
GO
101 globalguideline.com NULL 0 Jan 1 2007
102 globalguideline.com/sql Nice site. 8 Jan 1 2007
1101 globalguideline.com/xml NULL 0 Jan 1 2007
202 www.google.com It's another search 0 Jan 1 2007
2101 globalguideline.com/seo NULL 0 Jan 1 2007
2102 globalguideline.com/html NULL 0 Jan 1 2007
301 netscape.com Added long time ago!0 Jan 1 1999
302 yahoo.com Added today! 0 Jul 1 2007</pre>
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Get the Definition of a View Out of the SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Create a View on an Existing Table in MS SQL Server? |
☺ | How To Select All Columns of All Rows from a Table with a SELECT statement in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |