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
Thanks so much…
Very Simple & very easy to understand…
Good example… =)
Hello,
I’m relatively new to SQL stored procedure and I’m having a requirement.
I’m having a table in a database with some many rows and i need to display some particulars based on a condition with loops.
HELP ME OUT
Post some sample data with expected result
Hi,
I have table like below,
first_nm last_nm Gender
Raj s F
Rajp e M
Kar Sh M
De re M
Sw ko F
Kal la F
Sa Sa M
now i need to update my table each row last_nm with another record last_nm within gender(means female-female,male-male)
example:
first_nm last_nm Gender
Rajp re M
Kar e M
De Sh M
Sw s F
Kal ko F
Raj la F
Thanks,
Srr
Its nice article….. THis is basic level of things…
But i need lettle Critical calculation and some Critical and depth manipluations…
if its possible plz provide code for me….
thank u so much
–Hi friends see small example of Cursor versus Non Cursor
create table #Temp
(
Id int identity(1,1),
Fname nvarchar(25) not null,
Lname nvarchar(25) not null
)
insert into #Temp values (‘Think’,’India’)
insert into #Temp values (‘Kniht’,’India’)
insert into #Temp values (‘How ‘,’India’)
insert into #Temp values (‘Fine ‘,’India’)
select * from #Temp
—-Cursor and non cursor example
Declare @id int
Declare Curs_Temp cursor for
select id from #Temp
open Curs_Temp
Fetch next from Curs_Temp into @id
while @@fetch_status=0
Begin
Select * from #Temp where id=@id
Fetch next from Curs_Temp into @id
end
close Curs_Temp
deallocate Curs_Temp
——Same example without cursor
Declare @id int
Declare @i int
select @id=Count(id) from #Temp
set @i=0
while (@i@id)
Begin
Set @i=@i+1
Select * from #Temp where id=@i
end
——-Drop Temp Table
drop table #Temp
without cusor example is not working .
plz execute it and correct it
Hi i want to genrate RegID Like That
R201012310001
R201012310002
……………………
R2010123100010
(R +Year + MM+DD + 00001)
and again in next year last 4 digit will be start from 00001.
How Can i do this in sql server 2008
You need to do this everytime a row is added to the table
declare @n int
set @n=(select right(col,4)*1 from table)
set @n=coalesce(@n,0)
select ‘R’+convert(char(8),getdate(),112)+right(‘0000’+cast(@n+1 as varchar(4)),4)
hi,
thanks for your simple example.this helps the beginners to get the knowledge .
hii… can anyone plz tell me how to retrive the value of cursor output type variable in java.. i have a following Stored Procedure…
Create PROCEDURE [dbo].[USP_VIEW_PAYMENT]
(
@pPartyId int,
@pAccNum varchar(20),
@cGETDATA cursor varying output
)
AS
Begin
/* select es.*, tm.Description
from T_TRANSACTION_REQUEST tr, T_EMI_SCHEDULE es, M_TXNSTATUS_MASTER tm
where tr.Txn_Ref_Number=es.Txn_Ref_Number
and es.Status=tm.Status_ID
and tr.Party_ID=@pPartyId
and tr.Account_No=@pAccNum
Order by es.Txn_Ref_Number, es.Emi_Ref_Number
End*/
declare @vWhere as varchar(500)
DECLARE @VAR1 VARCHAR(50)
DECLARE cGETDATA CURSOR FOR
select Txn_Ref_Number from T_TRANSACTION_REQUEST where Party_ID=@pPartyId and Account_No=@pAccNum
OPEN cGETDATA
FETCH NEXT FROM cGETDATA INTO @VAR1
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;
declare @sQuery VARCHAR(MAX)
— SELECT DATA HERE
PRINT @VAR1
set @vWhere = ‘where Txn_Ref_Number = ‘ + ”” + @VAR1 + ””
set @sQuery = ‘SELECT Txn_Ref_Number as ”Transaction Reference Number”,Emi_Ref_Number as ”EMI No.”, Due_Date as ”Date”, Principle_Amt as ”Principle Amount”,
Int_Amt as ”Interest Amount”,Status as ”Status”
from dbo.T_EMI_SCHEDULE ‘ + @vWhere
EXEC(@sQuery)
PRINT @sQuery
FETCH NEXT FROM cGETDATA INTO @VAR1
END
CLOSE cGETDATA
DEALLOCATE cGETDATA
End
————————————————-
i want that “cGetData” which is of type cursor output in my java code… i have used registerOutParameter() method to reterive that but “unsupportedoperationexception” is thrown..
plz help..
and thanks in advance..
Hi Pinal,
Thanks alot, i had completed forgotten how to write cursor,
was looking for a simple example, and found yours.
Thanks
Menon
Note that many times the cursor can be replaced with simple set based approach
Hi Pinal Dave
I just want to say thanks, thanks to your blog because you and your blog helping me always.
Hi Im Deepak
I want to know can we pass the paremater in cursor loop
Eg:
DECLARE my_cursor CURSOR FOR
select distinct(floors) from Dealer where bh = @bh
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Event_ID
can you pls let me know
Yes did you try it?
Nice article,
Thanks
Hi Pinal,
This is R.Rajesh from Tamilnadu.Ur Articles are Excellent to beginners like me.Keep this long and serve to us like this.Thanks Alot.
Dear Sir
Thanks for helping me.I am really your big fan
its very simple and to the point example
hi ,
it is very useful……….
hi ,
very useful….
Thank you, I have always found information you present very useful and this one most of all. I just completed the first CURSOR statement I understood! You have made it possible for me to fetch data that was stored in such a way that it was not easily done. Thanks!
It is very useful for beginners of sql.
This is very usefull for beginners .
how to display grade in a student table on the basis of percentage of marks.
parametres used @name,@percentage,@grade