We'll talk about the basics of cursors. These let you move through
rows one at a time and perform processing on each row. (This article
has been updated through SQL Server 2005. and 2008. )
SQL Server is very good at handling sets of data. For example, you can
use a single UPDATE statement to update many rows of data. There are
times when you want to loop through a series of rows a perform
processing for each row. In this case you can use a cursor.
Please note that cursors are the SLOWEST way to access data inside
SQL Server. The should only be used when you truly need to access one
row at a time. The only reason I can think of for that is to call a
stored procedure on each row. In the Cursor Performance article I
discovered that cursors are over thirty times slower than set based
alternatives.
The basic syntax of a cursor is:
DECLARE @AuthorID char(11)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1
INTO @AuthorID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID
END
CLOSE c1
DEALLOCATE c1
The DECLARE CURSOR statement defines the SELECT statement that forms
the basis of the cursor. You can do just about anything here that you
can do in a SELECT statement. The OPEN statement statement executes
the SELECT statement and populates the result set. The FETCH statement
returns a row from the result set into the variable. You can select
multiple columns and return them into multiple variables. The variable
@@FETCH_STATUS is used to determine if there are any more rows. It
will contain 0 as long as there are more rows. We use a WHILE loop to
move through each row of the result set.
The READ_ONLY clause is important in the code sample above. That
dramatically improves the performance of the cursor.
In this example, I just print the contents of the variable. You can
execute any type of statement you wish here. In a recent script I
wrote I used a cursor to move through the rows in a table and call a
stored procedure for each row passing it the primary key. Given that
cursors are not very fast and calling a stored procedure for each row
in a table is also very slow, my script was a resource hog. However,
the stored procedure I was calling was written by the software vendor
and was a very easy solution to my problem. In this case, I might have
something like this:
EXEC spUpdateAuthor (@AuthorID)
instead of my Print statement. The CLOSE statement releases the row
set and the DEALLOCATE statement releases the resources associated
with a cursor.
If you are going to update the rows as you go through them, you can
use the UPDATE clause when you declare a cursor. You'll also have to
remove the READ_ONLY clause from above.
DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname
You can code your UPDATE statement to update the current row in the
cursor like this
UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
On 1/4/11, laxman gautam <laxmangautam@gmail.com> wrote:
> Cursor Example In SQl server 2008,
>
> USE [skbbl]
> GO
> /****** Object: UserDefinedFunction [dbo].[get_account_holder]
> Script Date: 01/05/2011 16:01:39 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER FUNCTION [dbo].[get_account_holder](@acc_no nvarchar(50))
> RETURNS nvarchar(2500)
> WITH EXECUTE AS CALLER
> AS
> BEGIN
> DECLARE @Holder_Name nvarchar(255)
> DECLARE @Ret_Holder_Names nvarchar(255)
>
> DECLARE Cursor_Acc_Holder CURSOR READ_ONLY
>
> FOR
> SELECT m.full_name FROM mem_general_mcg m join sav_acc_holder_mcg sa
> ON m.mid=sa.mid and sa.acc_no=@acc_no
> --SELECT au_id FROM authors
> OPEN Cursor_Acc_Holder
> SET @Ret_Holder_Names=''
>
> FETCH NEXT FROM Cursor_Acc_Holder INTO @Holder_Name
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> --PRINT @Holder_Name
> SET @Ret_Holder_Names=@Ret_Holder_Names+@Holder_Name+','
> FETCH NEXT FROM Cursor_Acc_Holder
> INTO @Holder_Name
>
> END
>
> CLOSE Cursor_Acc_Holder
> DEALLOCATE Cursor_Acc_Holder
> RETURN @Ret_Holder_Names
> END
>
No comments:
Post a Comment