UPDATE: For working example using AdventureWorks visit : SQL SERVER - Simple Example of Cursor - Sample Cursor Part 2
This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL.
DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
Reference: Pinal Dave (http://www.SQLAuthority.com), BOL
pinaldave
hi this is rajasekaran
Thanks for the simple demonstration! I was shown how to use cursors in a class years ago but never found an effective use for them as a VB.NET programmer (when you can make your own recordsets and twist them as you please). Now that I’m in an environment where I can’t use the nifty .NET data objects, a cursor was the only thing I could use to accomplish what I needed.
There are a lot of other demos on the web that are a lot more difficult, but this really explains just the guts of how cursors work.
Hi Pinal,
1. How can we manage SQL Server in Clustered Environment?
2. How can i stop and restart SQL Server services and instances in clustered environment?
3. How can i stop and restart SQL server instances/services from windows ?
If you have any documentation of managing SQL Server in clustered environment, please let me know.
Thanks
Abi
hi
pinal
myself Dhirendra kumar jha
i want to know that which action we have to take for performance tuning .
thx
Dhirendra
hi,
pinal
For database and query optimization,
what can i check that database is ok and query is ok.
what kind of necessory command is required to find out these things.
Please help me out
Thanks & regards
Dhirendra kumar
mumbai
Nice work. I keep finding this cursor as a good example for referencing.
The Snow flake affect on you web page is interesting, but a little distracting.
This is most excellent. I’ve been looking for just the bare bones of a cursor. This is a huge help to me. Did I mention it was huge?? :-)
Thanks,
Hosmerica
Hi,
its a nice example of Cursor.
Regrads
Faisal Ahmed Qureshi
Hi
This is most excellent. I’ve been looking for just the bare bones of a cursor. This is a huge help to me. Did I mention it was huge??
Regards
Sunil
Hi,
Its a very good sample code that explaining about cursor.
Thank alot…
Regards,
Googul…….
hi,
this is vanphan.
it’s a very good sample code that explaining about cursor
th’s
vanphan.
Excellent one!!!
Thanks dud.. good and very clear example
This the best code sample i have ever found. thanks a lot for this. this is really useful.
[...] 5, 2008 by pinaldave I have recently received email that I should update SQL SERVER - Simple Example of Cursor with example of AdventureWorks [...]
Thanxxx.
It’s so simple and good example.
what is the maximum size of storeprocedure in sql server 2000
Hi,
Pinal
You are really amazing
Hi, That was really a good example for cursor. But as I am new to SQL server. So can you tell me what changes should be required if we need to get multiple columns.
Wow, Thats a great help. Really appreciate it.
Thanks
Hi Pinal,
I have a function like below:
ALTER function dbo.GetPhoneNumber (
@CustId char(100) )
returns nvarchar(4000)
as
begin
declare @PhoneNum nvarchar(4000)
declare hC cursor for select rtrim(tellocn), telno from dbo.custtel where custid = @custid and datediscon is null
declare @telno nvarchar(4000)
declare @tellocn nvarchar(4000)
set @PhoneNum = ”
open hC
while ( @@fetch_status = 0)
begin
fetch next from hC into @tellocn, @telno
select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
end
close hC
deallocate hC
return @PhoneNum
end
—————————————————-
When I execute the function it returns like this:
420667 08123833136 08123833136
Why the last number is repeated twice?
Many thanks
Sumadrika,
I think Your answer lies in your query. maybe multiple telno for same customer?
select rtrim(tellocn), telno from dbo.custtel where custid = @custid and datediscon is null
Sumadrika,
You should check for @@fetch_status = 0 after fetching another record.
So if you replace
select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
by
if ( @@fetch_status = 0) select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
you get the result you want.
Checking @@fetch_status only in the while loop simply isn’t enough.
Regards
Erik
Great example. Thanks.
Really good example. Thanks :)
pinale it is nice.
but while i am executing it for my program it simply displays ‘command completed successfully’ but it didnt print anything
Ok nice code BUT
What is/are the purpose(s) of the cursor ?
I know you go row by row …
I want to results output a detail and a master
I only wish to query to get Master info one time - and query against that result to get detail,
I want to output both of these results…
how to do that ??
i want simple definition of cursor
very nice demo, thanks PinalDave!
very nice example that clears the idea of cursor…
saras ex chhe bhai.
thanks for the help.
Thanks, for the example, it came in handy !!
Thank you!
This is very helpful.
thanks
how to archive a database?
What is the advantages ?
Thanks.Really useful.
Thanks
Nice One ……………..
thank y its help me much, its simple cleare and very nice
Hi ..
I have parent categories and its related products.
I want to show top 5 random products for the particular category by use of cursur,,
can you give me some idea abt this…
thanks for the simple example, why we are using cursor?
hi,
This is very useful & Easy to understand the cursor …
Thank you..
Arjunvino
hi pinal on watching your example i have tried the following code what i want is the cursor should fetch the record into variable and the value in that variable is used to fetch the records from another table but i am not getting the result i want so what should i change in my code to get here is the code.
DECLARE @AccountID INT
DECLARE @RECORDCOUNT INT
DECLARE @MINUTECOUNT INT
DECLARE @COUNTRYNAME VARCHAR(225)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Codes,CountryName
FROM DailyReports.dbo.CountryCodes
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID,@COUNTRYNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RECORDCOUNT=(select count(comp_uncomp_calls)as total from DailyReports.dbo.RIL where comp_uncomp_calls=’1′ and dialed_digits like cast(@AccountId as varchar(20)) + ‘%’)
SET @MINUTECOUNT =(select sum(rounded_dur_secs)/60 as mincount from DailyReports.dbo.RIL where comp_uncomp_calls=’1′ and dialed_digits like cast(@AccountId as varchar(20)) + ‘%’)
PRINT @RECORDCOUNT
PRINT @MINUTECOUNT
PRINT @AccountID
PRINT @COUNTRYNAME
IF(@MINUTECOUNT = 0)
PRINT ‘inserting failed’
ELSE
INSERT INTO DailyReports.dbo.Temp(CountryName,TotalMinutes)values(@COUNTRYNAME,@MINUTECOUNT)
FETCH NEXT
FROM @getAccountID INTO @AccountID,@COUNTRYNAME
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
please help me i am new to cursors and stored procedures
Nice, simple and very useful article.
thanks its simplest and comprehensive example
Hi Pinal
Your blog is very nice.
Its very use ful.
Whenever I necessary about Sql Server than I will find from there.
nice blog
great. it realyl helped me
– simple use of cursor print all stored procedures on a sql server 2005
DECLARE @procName varchar(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
select s.name from sysobjects s where type = ‘P’ OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_HelpText @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName
[...] 20, 2008 by pinaldave SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of [...]
I need two (2) examples of the following programs:
1. Procedure
2. Function
3. Cursors
4. Exceptions
I hope someone will help me…..
Tnx…
need two (2) examples of the following programs:
1. Procedure
2. Function
3. Cursors
4. Exceptions
I hope someone will help me…..
Tnx…
hi friend…
thank u verymuch…..
its very useful for me and all learners..
You are doing a good work…
once again thank u..
bye
Ganesaan.M(CHN)
Hi friend,
tell me what is pl/sql and advantage and disadvantages