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
196 Comments. Leave new
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
…………
open hC
fetch next from hC into @tellocn, @telno
while ( @@fetch_status = 0)
begin
select @PhoneNum = @PhoneNum + (@telno + ‘ ‘)
fetch next from hC into @tellocn, @telno
end
close hC
deallocate hC
return @PhoneNum
end
This is correct. try it…..
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
It means you didn’t have any data in the table
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
Read about Cursor in SQL Server help file
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?