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
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
# 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.
[...] SQL SERVER – Simple Example of Cursor [...]
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
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.
Hi prasad,
Here is another solution to your gender table question (2005v required):
select identity (int,1,1) id, s
into #t
from (
select ‘m’ s union all
select ‘m’ s union all
select ‘f’ s union all
select ‘m’ s union all
select ‘f’ s union all
select ‘f’ s union all
select ‘m’ s) t;
with cte as (select id – row_number() over (partition by s order by id) diff, s from #t)
select s from cte group by diff, s;
drop table #t;
Regards,
Blass
Hi prasad,
Here is another solution to your gender table question (2005v required):
select identity (int,1,1) id, s
into #t
from (
select ‘m’ s union all
select ‘m’ s union all
select ‘f’ s union all
select ‘m’ s union all
select ‘f’ s union all
select ‘f’ s union all
select ‘m’ s) t;
with cte as (select id, id – row_number() over (partition by s order by id) diff, s from #t)
select s from cte group by diff, s order by max(id);
drop table #t;
Regards,
Blass
I NEED SIMPLE CURSOR FUNTION TO CONCAT THE TWO FEILDS
In regards to Mir’s question (I know, a long time ago, but it might help someone), I have created a complete working sample:
SET NOCOUNT ON;
DECLARE @OrderComments Table(OrderNo INT, Comment VARCHAR(256))
INSERT INTO @OrderComments VALUES(101, ‘Hello,’)
INSERT INTO @OrderComments VALUES(101, ‘my’)
INSERT INTO @OrderComments VALUES(101, ‘friends.’)
INSERT INTO @OrderComments VALUES(102, ‘This is another’)
INSERT INTO @OrderComments VALUES(102, ‘comment’)
DECLARE @orderNo INT
DECLARE @comments VARCHAR(4000)
DECLARE @comment VARCHAR(256)
DECLARE orderList CURSOR FOR
SELECT DISTINCT OrderNo FROM @OrderComments
OPEN orderList
FETCH NEXT FROM orderList INTO @orderNo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @comments = ”
DECLARE commentList CURSOR FOR
SELECT Comment FROM @OrderComments WHERE OrderNo = @orderNo
OPEN commentList
FETCH NEXT FROM commentList INTO @comment
WHILE @@FETCH_STATUS = 0
BEGIN
SET @comments = @comments + ‘ ‘ + @comment
FETCH NEXT FROM commentList INTO @comment
END
PRINT CAST(@orderNo AS VARCHAR(50)) + ‘: ‘ + @comments
CLOSE commentList
DEALLOCATE commentList
FETCH NEXT FROM orderList INTO @orderNo
END
CLOSE orderList
DEALLOCATE orderList
Give me the example for cursor
what is the use of cursor?
thank u
How can i insert a new column between twl column?
using SQL SERVER 2005
please tel me the query
thank u
How can i insert a new column between two columns?
using SQL SERVER 2005
@Subramaniam
Sir, Please have some patience. You asked the same question at-least 5 times in this blog. I understand this issue could be urgent but please ask your question once and have patience, your question will be answered.
Now My Response to your question.
There is no straight forward method to do this insert a new column between two existing column in a table.
Method1: Use SQL Server Management Studio (SSMS, SQL Server 2005).
Step1: Go to Object Explorer
Step2: Expand Databases
Step3: Expand Tables
Step4: Right Click Table Name Click Design /Modify.
Step5: Right Click on Table structure and click Insert Column, Check the position where you want to Insert.
Step6: Click Save.
Note: Before you Save, make sure you check, Generate Change Script (This will be one of the option in SSMS header). This will show you SQL Script that SQL Server will execute to make this change.
Method2: Drop and recreate table, In the new table script add the new column.
~ IM.
I have doubt in pivot concept
I created one table and select that table using pivot concept
but i cant use where condition,i need only I semester result or II semester result but its show in both semester if we are use where condition mean its should be work but i cant do where condition….any one help me….
CREATE TABLE TestOne
(
CourseName varchar(100),
Semester varchar(100),
StudentNumber varchar(100),
StudentName varchar(100),
Date datetime,
AttendanceStatus varchar(20)
)
insert into TestOne values(‘BA’,'I Semester’,'BC985233′,’RAJA’,'2009-07-08′,’P')
insert into TestOne values(‘BA’,'I Semester’,'AC232233′,’Sathish’,'2009-07-08′,’A')
insert into TestOne values(‘BA’,'I Semester’,'BC985233′,’RAJA’,'2009-07-10′,’A')
insert into TestOne values(‘BA’,'I Semester’,'AC232233′,’Sathish’,'2009-07-10′,’P')
insert into TestOne values(‘BA’,'I Semester’,'BC985233′,’RAJA’,'2009-07-15′,’P')
insert into TestOne values(‘BA’,'I Semester’,'AC232233′,’Sathish’,'2009-07-15′,’A')
insert into TestOne values(‘BA’,'I Semester’,'BC985233′,’RAJA’,'2009-07-20′,’P')
insert into TestOne values(‘BA’,'I Semester’,'AC232233′,’Sathish’,'2009-07-20′,’P')
insert into TestOne values(‘BA’,'II Semester’,'BC985233′,’RAJA’,'2009-07-25′,’A')
insert into TestOne values(‘BA’,'II Semester’,'AC232233′,’Sathish’,'2009-07-25′,’P')
insert into TestOne values(‘BA’,'II Semester’,'BC985233′,’RAJA’,'2009-07-30′,’P')
insert into TestOne values(‘BA’,'II Semester’,'AC232233′,’Sathish’,'2009-07-30′,’A')
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
DECLARE @query1 VARCHAR(4000)
SELECT @listCol = STUFF(( SELECT DISTINCT ‘],[' + convert(nvarchar,date,111) FROM TestOne
ORDER BY '],[' + convert(nvarchar,date,111) FOR XML PATH('')), 1, 2, '') + ']‘
–print @listCol
SET @query =
‘SELECT * FROM
(SELECT StudentName,StudentNumber,CourseName,Semester,[Date]
, AttendanceStatus
FROM TestOne) src
PIVOT (max(AttendanceStatus) FOR [Date]
IN (‘+@listCol+’)) AS pvt’
EXECUTE (@query)
HI
Thanks its GR8
prasad
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
HERE is Query
select * from test where val in (select val from test group by val having count(val)=1)
@mahendra
Just add an aggregate to salid. Because it is always one value, using an aggregate won’t change anything. Also, the HAVING can be just COUNT(*). No reason to mention salary, the GROUP BY on salary takes care of that:
WITH
salary(salid, salary)
AS
(
SELECT 1, 10 UNION ALL
SELECT 2, 20 UNION ALL
SELECT 3, 40 UNION ALL
SELECT 4, 10 UNION ALL
SELECT 5, 50 UNION ALL
SELECT 6, 40
)
SELECT
MAX(salid),
salary
FROM
salary
GROUP BY
salary
HAVING
COUNT(*) = 1
very simple and good example for beginners…thanks a lot…..