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
hi this is rajasekaran
This is very usefull to me. Thanks for demonistration
Thanx Praveena
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 ,
irfan here
Hi pinal,
cursor example is excellent …thanks a lot…
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
What did you mean by it?
The number of lines?
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
…………
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?
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…
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.
[...] 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
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.
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
You dont need a cursor
It is as simple as
select col1+’,'+col2 from your_table
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.
very simple examples on
1.procedures
2.functions
3. cursors
4.packages
5.triggers
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…..
Sumardika…
You should fetch first record before entering in to the loop. Next fetch statement should come after select @PhoneNum = @PhoneNum + (@telno + ‘ ‘) statement
That should return you good results
Good example.. Thanks
very simple and good example for beginners…thanks a lot…..
Hi,
Is there any way to define RecordSet in Sql 2005.
I want to generate variable for each and every field in the table, my table has 35 fields, its tedious job to declare every field. I want to define Cursor for same field and need to manipulate field record and want to store all the fields in temp table.
By using –
Select top 0 * into #temp from
I able to generate temp table with same table structure.
Kindly let me know if any method to generate recordset.
eg. in Informix we can do
define record like .*
Thanks
create procedure [dbo].[shankar](@parameter nvarchar(100))
as
declare @PhoneNum varchar(100)
DECLARE @eid nvarchar
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT emp_id
FROM empold
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @eid
WHILE @@FETCH_STATUS = 0
BEGIN
if ( @@fetch_status = 0)
begin
select @PhoneNum = @PhoneNum + (@eid )
end
FETCH NEXT
FROM @getProductID INTO @eid
END
return @PhoneNum
CLOSE @getProductID
DEALLOCATE @getProductID
i am getting output
The ‘shankar’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
please help me
Thanks a lot…This is the script I have always used for Cursors….
firstly, what is the reason for fetching the record before the while loop, we can do in the loop also?
That’s the way while-loop works. First you fetch something and then you evaluate the comparison clause to see if you need to step out of the loop (or not to get in there in the first place).
Do-while-loop works other way around. First you enter the loop, then you fetch something and finally you evaluate and check if you need to bail out.
It’s much simpler when you do things like that even when SQL does not have pure do-while construct.
Thanks pinal
This is exactly what i want
Thanks again
Brijesh Parikh
Require example for
1. Procedure
2. Function
3. Cursors
4. Exceptions
A verry good and simple examlple
and easy to understand
thanx for that
Thanks . Really useful .
Simple and Amazing
This is very usefull for me
Thanks
Hi ,
Can anyone guid me regarding database tunning and performance improvement.
Thanks
Sunil
Hi ,
Can anyone guide me regarding database tunning and performance improvement.
Thanks
Sunil
i Have to create two tables as follow
tbl1
student_ID subject_ID Mark
————————————-
1 1 50
1 2 67
1 3 80
2 1 24
2 2 57
2 3 60
tbl2
subject_ID Subject
——————————-
1 English
2 Maths
3 Science
i Want the Result as Follows
student_ID English Maths Science
___________________________
1 50 67 80
2 24 57 60
Is It Possible?
Its Very Urgent
Pls Mail Me
@Ramesh
SELECT
tbl1.student_ID,
SUM(CASE tbl2.subject WHEN ‘English’ THEN tbl1.Mark END) English,
SUM(CASE tbl2.subject WHEN ‘Maths’ THEN tbl1.Mark END) Maths,
SUM(CASE tbl2.subject WHEN ‘Science’ THEN tbl1.Mark END) Science
FROM
tbl1,
tbl2
WHERE
tbl2.subject_ID = tbl1.subject_ID
GROUP BY
tbl1.student_ID;
SELECT
tbl1.student_ID,
–SUM(CASE table2.subject WHEN ‘English’ THEN Table1.Marks END) English,
–SUM(CASE table2.subject WHEN ‘Maths’ THEN Table1.Marks END) Maths,
–SUM(CASE table2.subject WHEN ‘Science’ THEN Table1.Marks END) Science
sum(CASE tbl2.subject_ID when ‘english’ then tbl1.marks end) enlish,
sum(CASE tbl2.subject_ID when ‘Maths’ then tbl1.marks end) maths,
sum(CASE tbl2.subject_ID when ‘science’ then tbl1.marks end) science
FROM
tbl1,
tbl2
WHERE
tbl2.subject_ID = tbl1.subject_ID
GROUP BY
tbl1.student_ID
Its Working……
I am Very happy to ur immediate solution
Thank You
and i want another one code for as same.
the subjects are runtime.
we dont know the name of the subject
what can i do now?
@Ramesh
COLUMNs cannot be added at runtime, unless you are using dynamic SQL.
If the amount of subjects is not known until runtime, you can build your query at runtime and use dynamic SQL.
You need to use Dynamic Cross-tab
Refer this post hot to do it dynamically
Version 2000
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
Version 2005
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
It really helped me a lot, your blog is so helpfull
Dear Sir,
Very useful example, the cursor working fine.
Thanks
Ansari
Thanks you very much !
Good example, it’s helpfull :)
Phil
nice for a quick readonly cursor
declare @Question varchar(100)
declare @Answer varchar(100)
declare faq_cursor cursor fast_forward for
select question, answer
from faq
open faq_cursor
fetch next from faq_cursor
into @Question, @Answer
while @@FETCH_STATUS = 0
begin
fetch next from faq_cursor
into @Question, @Answer
end
close faq_cursor
deallocate faq_cursor
Hi ,i wrote this query but it didn’t work ,plz guide me :(( what’s it’s problem?
DECLARE
@Contact_name nvarchar(50),
@Product_name nvarchar(50),
@CustomerID nvarchar(50),
@Row_number nvarchar(50)
DECLARE cursor_Product CURSOR FOR
SELECT PurchasedQuantity1.ContactName,PurchasedQuantity1.ProductName,PurchasedQuantity1.ProductPurchasedQuantity
FROM
(SELECT Orders.CustomerID,Customers.ContactName, SUM([Order Details].Quantity) AS ProductPurchasedQuantity,ProductName
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.CustomerID,Customers.ContactName, [Order Details].ProductID,ProductName) AS PurchasedQuantity1
INNER JOIN
(SELECT CustomerID, MAX(ProductPurchasedQuantity) MaxQuantityPurchased
FROM
(SELECT Orders.CustomerID, SUM([Order Details].Quantity) AS ProductPurchasedQuantity
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID,[Order Details].ProductID) AS PurchasedQuantity2
Group by CustomerID) AS MaxQuantity
ON PurchasedQuantity1.CustomerID = MaxQuantity.CustomerID AND PurchasedQuantity1.ProductPurchasedQuantity = MaxQuantity.MaxQuantityPurchased
Order by ContactName
OPEN cursor_Product FETCH NEXT FROM cursor_Product INTO
@Contact_name,
@Product_name,
@CustomerID
set @Row_number =0
WHILE @@FETCH_STATUS = 0
BEGIN
set @Row_number=@Row_number + 1;
PRINT ‘Row NO Customer ID ContactName FAvorite Product Name ‘ +
‘
‘ +
‘========= ========== ======== ===================’ +
‘
‘ +
@Row_number+’ ‘+ @CustomerID + ‘ ‘+ @Contact_name + ‘ ‘+@Product_name
END
CLOSE cursor_Product
DEALLOCATE cursor_Product
To Rose
you must add this code before end of while circle:
FETCH NEXT FROM cursor_Product INTO
@Contact_name,
@Product_name,
@CustomerID
hi, this is vishu
thax for giving me this sol”
hi prasad you can use using distinct command to view the un duplicated records
regards,
shalu
Distinct will just remove any duplicates and not return data that are duplicated
hi pinal,
i’m very excited about your blog……
how will use the sql geometry data type in my real time give me a simple example
regards,
shalu
Search for Spatial data type article in this site
Simple & outstanding article.
AWESOME EXAMPLE!!!!!
thanx so much, saved me a LOT of time
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
diff bet result set,data set and data table in sql
Hi,
I have written a cursor in MS SQL server and was able to execute it also. But i have an issue where it’s not displaying the results in the order what i have written it’s displaying randomly. First I am selecting the records from Info table and then from sinfo table.
Example:
DECLARE emails CURSOR FOR select distinct LTRIM(RTRIM(useremail)) from Info
UNION
select DISTINCT LTRIM(RTRIM(useremail)) from SInfo
SET NOCOUNT ON
DECLARE @email varchar(100)
OPEN emails
FETCH NEXT from emails
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT from emails into @email
select username, password, email from info where useremail = @email
union
select username, password, email from Sinfo where useremail = @email
END
CLOSE emails
DEALLOCATE emails
Any suggestions would be much appreciated.
Thanks
Hi,
Any suggestions on my issue?
Thanks
Hi pinal
give me solution of two tables compair for reconcillation
Thank’s
Kishor
[...] of Database Simple Use of Cursor to Print All Stored Procedures of Database Including Schema Simple Example of Cursor Cursor to Kill All Process in [...]
Great Article that helps me lot but i just want to know can i select multiple values in cursor for
Hi All,
I have an array of emails and i want to split every email and check every email from database and its correspondence Status.
Please Help!
Very nice example of Cursors thank u so much Pinal
Regards & ThanxX
Arif Shaik
Very nice example of Cursors thank u so much Pinal
Regards & ThanxX
Arif Shaik
nice one
Great Article that helps me lot
thank u pinal
Hi Pinal
Can u give an example of selecting top few rows from each group where we are getting the result by joining two or more tables.can we give an alias name to the joining of two tables, if yes please let me know how, i shall be thankful.
Thanks
Taruna
Based on what I have understood from your query I have done something…
Create table t_Employee
( EmployeeID Int,
EmployeeName nVarChar(20)
)
Create table t_Deduction
( DeductionID Int,
Description nVarChar(20)
)
Create table t_EmployeeDeductions
( EmployeeID Int,
DeductionID Int
)
INSERT TABLE t_Employee
Select 1,’ABC’
UNION ALL
Select 2,’DEF’
UNION ALL
Select 3,’GHI’
UNION ALL
Select 4,’CAT’
UNION ALL
Select 5,’ABC’
INSERT TABLE t_Deduction
Select 1,’PF’
UNION ALL
Select 2,’Phone Bill’
UNION ALL
Select 3,’Insurance’
INSERT TABLE t_EmployeeDeduction
Select 1,1
UNION ALL
Select 1,2
UNION ALL
Select 1,1
UNION ALL
Select 1,2
Select top 2 ED.*
from t_Employee AS Emp
INNER JOIN
t_EmployeeDeductions ED
ON Emp.EmployeeID = ED.EmployeeID
Now if you have any question please feel free to revert…
Regards
Malaya
Emil
when i am creating an SSIS, where I need to do a merge join between Excel source data and OLEDB source data(data from SQL table),ofcourse after sorting, it is giving me the following errors not on merge join but on retrieving data from the OLEDB source itself as the data is huge-in crores, so tell me the solution if u have one, wud b thankful, the reason why i am doing merge join is i have data in the excel file out of which some records are there in SQL database and i want to know what data is there and what is not , the data is in the related tables . Also i want the output containing the columns of excel as well as the columns (fields) of the tables in a single excel sheet so that the other persons or should i say the users of the data can get to know what data is useful and they want to compare.
i want to match on 5 fields, say firstname,lastname,city,state and the first 5 characters of the street address.how can i use LTRIM,RTRIM,LEFT and UPPER string functions on the data i have imported from excel?
Sorry, so many questions, but i really need help.
Thanks
Tanu
Emil
when i am creating an SSIS, where I need to do a merge join between Excel source data and OLEDB source data(data from SQL table),ofcourse after sorting, it is giving me the following errors not on merge join but on retrieving data from the OLEDB source itself as the data is huge-in crores, so tell me the solution if u have one, wud b thankful, the reason why i am doing merge join is i have data in the excel file out of which some records are there in SQL database and i want to know what data is there and what is not , the data is in the related tables . Also i want the output containing the columns of excel as well as the columns (fields) of the tables in a single excel sheet so that the other persons or should i say the users of the data can get to know what data is useful and they want to compare.
i want to match on 5 fields, say firstname,lastname,city,state and the first 5 characters of the street address.how can i use LTRIM,RTRIM,LEFT and UPPER string functions on the data i have imported from excel?
Sorry, so many questions, but i really need help.
Thanks
Tanu
Error: 0×80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0×80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A93.tmp” to length 3473188. There was insufficient disk space.
Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A94.tmp” to length 3473188. There was insufficient disk space.
Error: 0×80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0×80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A95.tmp” to length 3473188. There was insufficient disk space.
Error: 0xC004704A at Data Flow Task, DTS.Pipeline: The buffer manager cannot extend the file “C:\DOCUME~1\tarunm\LOCALS~1\Temp\a\DTS1A96.tmp” to length 3473188. There was insufficient disk space.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Information: 0x4004800D at Data Flow Task, DTS.Pipeline: The buffer manager failed a memory allocation call for 3473184 bytes, but was unable to swap out any buffers to relieve memory pressure. 1 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
Information: 0x4004800D at Data Flow Task, DTS.Pipeline: The buffer manager failed a memory allocation call for 3473184 bytes, but was unable to swap out any buffers to relieve memory pressure. 7 buffers were considered and 3 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
Error: 0xC0047012 at Data Flow Task, DTS.Pipeline: A buffer failed while allocating 3473184 bytes.
Error: 0xC0047012 at Data Flow Task, DTS.Pipeline: A buffer failed while allocating 3473184 bytes.
Error: 0xC0047011 at Data Flow Task, DTS.Pipeline: The system reports 44 percent memory load. There are 8589111296 bytes of physical memory with 4764954624 bytes free. There are 2147352576 bytes of virtual memory with 104652800 bytes free. The paging file has 12660408320 bytes with 8538820608 bytes free.
Error: 0xC0047011 at Data Flow Task, DTS.Pipeline: The system reports 44 percent memory load. There are 8589111296 bytes of physical memory with 4764971008 bytes free. There are 2147352576 bytes of virtual memory with 104652800 bytes free. The paging file has 12660408320 bytes with 8538845184 bytes free.
Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
Error: 0xC0047048 at Data Flow Task, DTS.Pipeline: The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission.
Error: 0xC020821A at Data Flow Task, Sort 1 [796]: The Sort transformation cannot queue a work item to its thread pool. There is not enough memory available.
Error: 0xC020821A at Data Flow Task, Sort 1 [796]: The Sort transformation cannot queue a work item to its thread pool. There is not enough memory available.
Warning: 0x8007000E at Data Flow Task, Sort 1 [796]: Not enough storage is available to complete this operation.
Error: 0xC0208296 at Data Flow Task, Sort 1 [796]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component “Sort 1″ (796) failed with error code 0×80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread1″ has exited with error code 0×80004005.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread “WorkThread3″ received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread “WorkThread2″ received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread2″ has exited with error code 0xC0047039.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “WorkThread3″ has exited with error code 0xC0047039.
Error: 0xC02020C4 at Data Flow Task, OLE DB Source [187]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component “OLE DB Source” (187) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread “SourceThread1″ has exited with error code 0xC0047038.
Information: 0×40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0×40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: “component “Excel Destination” (4131)” wrote 0 rows.
Task failed: Data Flow Task
Warning: 0×80019002 at Package1: The Execution method succeeded, but the number of errors raised (32) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package “Package1.dtsx” finished: Failure.
The program ‘[4820] Package1.dtsx: DTS’ has exited with code 0 (0×0).
Hi Malaya
u are right but i dont need that solution as my problem is i am getting the data from three related tables.say, i am grouping on a field from Table A and against every group i am calculating sum of values from a field which is in table B and also i am sorting on this sum field and now i get say 20 -30 records for each group, but i want to have top 3-3 from each group.
I dont want to store the intermediate dat in any of temp tables.
I just want a single query.
If u can help, i really appreciate.
Thanks
Taruna
Refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx
very good article
Thank you!! very very helpful!!
Nice one for beginners.
Awesome! code for beginners, simple and really works :D thanks a lot dude.
Thank you. it’s userfull
good example…
pinal plz share SSIS architecture
Some indentation would be nice. Leaving your page and googling for something clearer.
Very good example.. Was of gr8 help. Thanks
Thanks, nice example
I want to know what will happen for the below scenario.
I have done some testing and found that it is a problem.
Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.
For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+ in the same table then the records inserted newly should be 4. so the total records for salary > 1000 should be 8 after the loop ends.
But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.
Please suggest how to make sure Rows selected for cursor for the first time do not change with example.
Thanks in advance
You need to post some sample data with expected result for better calrity
Very good example it is i found for a cursor..
Hi sir,
i want to know what is the difference between a procedure, function and cursor theoritically…
thanks in advance.
Have a look at SQL Server help file. If you don’t unserstand ask here
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable “@cursor”.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable “@cursor”.
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable “@cursor”.
this was the msg which i was getting at the time of execution
Plese give your syntex pramod.e
[...] Simple Example of Cursor [...]
Thanks for teaching me cursor very simply…..
hi
i want to select the tables which are seeded from database creation.can any one help to create it using cursors
seed tables means the tables which have the default data when a database is created
please tell me its urgent
what ‘s the use of Insensitive keyword in cursor ?