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 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
I need two (2) examples of the following programs:
1. Procedure
2. Function
3. Cursors
4. Exceptions
I hope someone will help me…..
Tnx…
Read about them in SQL Server help file
It has example codes
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
Nice Demo…Thanks
hi
i have a gender table
sex
m
m
f
m
f
f
m
i want out put like this
sex
m
f
m
f
m
f
in order plz help me
hi
i have s salary table
salid salary
1 10
2 20
3 40
4 10
5 50
6 40
i want out put like this
id salary
2 20
4 50
note:-no need to display repated values in above column 10 and 40 is repated values except repated values i wnt to display remaining values
plz help me
This can be more simpler by this query
create table TestTable1 ( salid int , salary int)
insert into TestTable1 values ( 1 ,10)
insert into TestTable1 values (2 ,20)
insert into TestTable1 values (3 ,40)
insert into TestTable1 values (4 ,10)
insert into TestTable1 values (5 ,50)
insert into TestTable1 values (6 ,40)
select max(salid),salary from TestTable1 group by salary having COUNT(salary) = 1
Thanks
BM
# Pramod,
script 2 can be easily done using while loop, because I wanted to do it with only select statement I did script 2 in a different way.
Here are scripts for your two posts.
— Script 1:
create table TestTable1 ( salid int , salary int)
insert into TestTable1 values ( 1 ,10)
insert into TestTable1 values (2 ,20)
insert into TestTable1 values (3 ,40)
insert into TestTable1 values (4 ,10)
insert into TestTable1 values (5 ,50)
insert into TestTable1 values (6 ,40)
select A.Salid
,A.salary
from TestTable1 A JOIN (
select salary
,count(*) Counts
From TestTable1
group by salary
having count(*) < 2
) B
ON A.salary = B.salary
— Script 2
Create Table TestTable2 ( sex char(1))
insert into TestTable2 (sex) select ‘m’
insert into TestTable2 (sex) select ‘m’
insert into TestTable2 (sex) select ‘f’
insert into TestTable2 (sex) select ‘m’
insert into TestTable2 (sex) select ‘f’
insert into TestTable2 (sex) select ‘f’
insert into TestTable2 (sex) select ‘m’
select id = identity (int,1,2) ,sex into #example1 from TestTable2 where sex = ‘m’
select id = identity (int, 2,2) , sex into #example2 from TestTable2 where sex = ‘f’
select sex from (
select id, sex from #example1
union
select id, sex from #example2 )X
drop table #example1,#example2
Regards,
IM.
Hi Imran Mohammed,
Thankx a lot.
Regards,
Prasad Gopathi.
HI Friend Thank You very much, very useful for me and company friends,
Thank you very much
by
G.Srinivasan, M.Sc(cs)
Dear helper
please help me
i want to find out
max(sum(stMarks))
but it not work, please help me
Regards
jawad
select max(stMarks) as stMarks from
(
select some_col,sum(stMarks) as stMarks from your_table
group by some_col
) as t
I have a table that has a column with comments in it. These comments are associated with a orderNo. For example:
Orderno Comment
101 Hello,
101 How are you
I would like to have the comments appear on a single row. For example:
Orderno Comment
101 Hello, How are you
Please Help!
I have one table (LPODTL) with two columns(part_no,indt_no)
saved records structure is
part_no indt_no
P1 i1,i2
i want output like part_no+indt_no means(p1i1 in one row,p1i2 in second row )
plz help.