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
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
Version 2005
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