I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.
Simple Example of Cursor using AdventureWorks Database is listed here.
USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID =Â CURSORÂ FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
69 Comments. Leave new
Hi dave, could you write about database benchmarks; TPC-C and TPC-E?
Pinal,
Thanks lot for such helpful blog. I found lots of nuts and bolts on SQL server from your blog.
I am new to SQL Server but started working on real time project. In your example of Cursor in Adventureworks database, you defined a cursor variable ( as i believe). Is that possible to define variable like that to hold multi-column value.
I would appreciate any kind of response.
regards
You can’t do that. You may need to make use of table variable
declare @t table(col1 int, col2 char(1),….)
I tested your code on sql server 2005, it is not working.
First, define cursor without @ symbol. I am not sure why. Thanks for response. — Coway
use northwind
go
declare QuanCsr cursor for
select quantity from mytable00
declare @qty int
declare @sqlstatus int
open QuanCsr
fetch next from QuanCsr into @qty
while @@fetch_status = 0
begin
print @qty
fetch next from QuanCsr into @qty
select error = @@error — error is equvalent to column
select sqlstatus = @@fetch_status — sqlstatus is equvalent to column
end
print ‘sql status ‘ + convert(varchar, @sqlstatus)
close QuanCsr
deallocate QuanCsr
how can we see the list of cursor ?
Refer this blog post and see if that answers it
There are two methods
Hi dave
here i want immediate solutions for followings using cursors
master table :- items
sno items
—————–
1 pen
2 pencil
3 box
:- sno is primary key
here another table name is :- trantables
sno nos rate total type
————————————————-
1 5 7 35 issue
1 2 6 12 receipt
1 1 7 7 issue
using cursor
first we get record in master table rows,
check using sno and type
items.sno=trantables.sno and trantables.type=’issue’
if its ok give sum(nos) and sum(total)
———————————————————————–
items.sno=trantables.sno and trantables.type=’receipt’
if its ok give sum(nos) and sum(total)
here final solution : this one i want using cusor
————————————————————–
sno items issuenos issuetotal receiptnos receipttotal
—————————————————————————–
1 pen 6 42 2 12
@sulai
you have two ways to execute it, every time change the values of @var1 and @var2 , or create a stored procedure and just pass the parameters. I have mentioned both,
CREATE TABLE ITEM (SNO INT , ITEMS VARCHAR( 20 ) )
INSERT INTO ITEM
SELECT 1 , ‘PEN’ UNION ALL
SELECT 2 , ‘PENCIL’ UNION ALL
SELECT 3 , ‘BOX’
CREATE TABLE TRANTABLES ( SNO INT , NOS INT , RATE INT , TOTAL AS NOS*RATE , TYPE VARCHAR(20))
INSERT INTO TRANTABLES (SNO , NOS, RATE , TYPE)
SELECT 1, 5 , 7 , ‘ISSUE’ UNION ALL
SELECT 1, 2 ,6 , ‘RECEIPT’UNION ALL
SELECT 1 ,1 ,7 , ‘ISSUE’
–CREATE PROC USP_CHECKING @SNO INT , @TRAN VARCHAR(20) — — if you want to make a stored procedure
–AS
— if you dont want to make a stored proc, start from here
DECLARE @VAR1 INT
DECLARE @VAR2 VARCHAR(20)
DECLARE @VAR3 VARCHAR(20)
DECLARE @VAR4 INT
DECLARE @VAR5 INT
DECLARE @CUR CURSOR
SET @CUR = CURSOR FOR
SELECT DISTINCT SNO , TYPE FROM TRANTABLES
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR1 , @VAR2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @VAR3 = ITEMS FROM ITEM WHERE SNO = @VAR1
SELECT @VAR4 = SUM (NOS) FROM TRANTABLES WHERE TYPE = @VAR2 AND SNO = @VAR1
SELECT @VAR5 = SUM (TOTAL) FROM TRANTABLES WHERE TYPE = @VAR2 AND SNO = @VAR1
IF @VAR1 = 1 AND @VAR2 = ‘ISSUE’ — comment this if you want to automate the process
–IF @VAR1 = @SNO AND @VAR2 = @TRAN — If you want to use this method, comment above line
SELECT @VAR1 SNO , @VAR3 ITEMS, @VAR4 ISSUENOS, @VAR5 ISSUETOTAL, SUM(NOS) RECIEPTNOS, SUM(TOTAL) RECIPTTOTAL
FROM TRANTABLES
WHERE TYPE @VAR2
FETCH NEXT
FROM @CUR INTO @VAR1 , @VAR2
END
CLOSE @CUR
DEALLOCATE @CUR
GO
— AFTER YOU CREATE THIS STORED PROCEDURE EXECUTE THIS PROCEDURE
— EXEC USP_CHECKING 1, ISSUE
Hope this helps.
yeah very superb examble it’s very use full
Thanks,
Raja.R
All,
I am using Microsoft SQL 2005 and am trying to create a cursor that will take data from several records and concatenate that data into one record.
For example:
I have three attributes (OrderYear, SeqNumber, Item) from a single table (table name: TSGItems) that I am working with…
The data in TSGItems table looks like this:
OrderYear SeqNumber Item
2008 10001 Senior IRS Lien
2008 10001 IRS Lien
2008 10002 Senior IRS Lien
2008 10003 IRS Lien
2008 10003 Senior IRS Lien
2008 10003 Jr. Lien
2008 10004 HOA Lien
In addition to the concatenation, I am trying to manipulate the data as follows:
1) I would like to order the items alphabetically.
2) I would like to put an “and” before the last concatenation when there are more than 1 Item for each Orderyear and SeqNumber.
3) I would like to put in a “,” between each concatenation when there are 3 or more Item for each Orderyear and SeqNumber (except in between the last two concatenation.
4) I would like this data inserted into a temp table called #TSGItems
When I do a select statement on #TSGItems, I would like the data shown as follow:
OrderYear SeqNumber Item
2008 10001 IRS Lien and Senior IRS Lien
2008 10002 Senior IRS Lien
2008 10003 Jr. Lien, IRS Lien, and Senior IRS Lien
2008 10004 HOA Lien
I thank you for taking the time to look at and attempt to solve my challenge.
-Anthony
@Anthony
Let me tell you I am not a developer and I dont have much developing skills… I am sure there must be another good way of writing sql code for what you have asked…
Below is my code, This does exactly what you described in your post.
CREATE TABLE TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
INSERT INTO TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
SELECT ‘2008’ ,10001 , ‘Senior IRS Lien’ UNION ALL
SELECT ‘2008’ , 10001 , ‘IRS Lien’ UNION ALL
SELECT ‘2008’ , 10002 , ‘Senior IRS Lien’ UNION ALL
SELECT ‘2008’ ,10003, ‘IRS Lien’ UNION ALL
SELECT ‘2008’ ,10003 , ‘Senior IRS Lien’ UNION ALL
SELECT ‘2008’ ,10003, ‘Jr. Lien’ UNION ALL
SELECT ‘2008’ ,10004, ‘HOA Lien’
We created a table TGSITEMS and we inserted all the values which you mentioned in your post.
Then I created two more temporary tables #TGSITEMS and #TGSITEMS1… and with the help of While loop and cursor I got your result.
CREATE TABLE #TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
GO
CREATE TABLE #TGSITEMS1 ( ID INT IDENTITY, ITEM VARCHAR(MAX))
GO
DECLARE @VAR INT
DECLARE @CUR CURSOR
DECLARE @VAR1 INT
DECLARE @CMD VARCHAR(1000)
DECLARE @CMD1 VARCHAR(1000)
SET @CUR = CURSOR FOR
SELECT DISTINCT SEQNUMBER
FROM TGSITEMS
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #TGSITEMS1
INSERT INTO #TGSITEMS1 (ITEM) SELECT ITEM FROM TGSITEMS WHERE SEQNUMBER = @VAR ORDER BY ITEM
SET @VAR1 = 1
SET @CMD1 = ”
WHILE @VAR1 <= ( SELECT COUNT(*) FROM #TGSITEMS1 )
BEGIN
SELECT @CMD = ITEM FROM #TGSITEMS1 WHERE ID = @VAR1
IF @VAR1 <(SELECT COUNT(*) FROM #TGSITEMS1) -1
SET @CMD1 = @CMD1 + @CMD +’ , ‘
ELSE IF @VAR1 = (SELECT COUNT(*) FROM #TGSITEMS1 )-1
SET @CMD1 = @CMD1 + @CMD
ELSE IF (SELECT COUNT(*) FROM #TGSITEMS1 ) = 1
SET @CMD1 = @CMD
ELSE
SET @CMD1 = @CMD1 + ‘ and ‘ + @CMD
SET @VAR1 = @VAR1 + 1
END
INSERT INTO #TGSITEMS SELECT DISTINCT ORDERYEAR , SEQNUMBER, @CMD1 FROM TGSITEMS WHERE SEQNUMBER = @VAR
FETCH NEXT
FROM @CUR INTO @VAR
END
CLOSE @CUR
DEALLOCATE @CUR
GO
SELECT DATENAME ( YY, ORDERYEAR)ORDERDATE , SEQNUMBER, ITEM FROM #TGSITEMS
GO
TRUNCATE TABLE #TGSITEMS
GO
Let me tell you this is highly performance consuming process… its upto you if you want to use it…
Hope this helps.
Thanks
Imran.
Hello,
i’m not a programmer but i have an urgent need to have a piece of code to update a table on demand.i need to flag x number of records with the same state code based on a look up table which has two columns State, TotalNumber.
please help!!!
Thank you Imran,
I really appreciate you taking the time to address this challenge. I have been trying to move away from MS Access and am new to the SQL environment. I agree, what I am asking for is very demanding, but I feel reverting to Access is a step backward. I cannot wait to try the coded at work when I return on Monday.
Cheers
-Anthony
Imran,
When I tweaked your script in a few places it worked perfectly!!!! What more, I was able to apply the script to another project and got that one to work too. Thanks again for all your help.
Anthony
Hi Dave
I have a little problem in that I need to devise a SSIS Package that will look in a folder and concatenate the data files priior to loading. The filenames are of the pattern:-
abcXXX21072009.txt
abcXXX21072008.txt
I need these two put into a new file named (say) Test1.txt
abcZZZ21072008.txt
abcZZZ21072008.txt
and these put into a new file named (say) Test2.txt
would welcome some advice please
Many thanks
Jim
I need to create a diagnostic package using cursors using this logic
1.Loop through and load clean data from source table destination table i.e while the @@fetch_status =0
2.if the @@Fetch_status 0,add the current row to Error table
3.Restart the cursor at the next record to avoid reloading the same records
My brain is blowing out about this :( .I’ll probably add as the responses pour in
Thank you
What is your criteria about clean?
If you post it here, some one may give you a set-based code when you dont need a loop
Thanks Dave …
I have Learned so many things from your blog …
Hi,
I have a similar issue as others who have posted.
I am writing a stored proc to search for a group of products on a quote. I am returning 2 fields: quantity and productid. I then want to update the quantity by a parameter passed when the proc is called.
What I have right now is not working:
ALTER PROCEDURE [dbo].[xxxxxx_main_quantity] (@quoteid uniqueidentifier, @new_maintenanceyears int)
AS
declare @n_quantity decimal(18,5); /*this is to identify which quote we are looking for*/
declare @quotedetailid uniqueidentifier; /*this is to identify which quote product we are looking for*/
declare o_curs cursor for
select q.quantity*@new_maintenanceyears as newquantity, q.quotedetailid
from filteredquotedetail q
left outer join filteredProductPriceLevel p on q.productid = p.productid
left outer join filteredProduct f on p.productid = f.productid and p.pricelevelid = f.pricelevelid
where q.quoteid = @quoteid and p.PriceLevelIDName = ‘Default’
and f.new_itemclassname = ‘1’
BEGIN
/*Open cursor and fetch first row of results*/
open o_curs
fetch o_curs into @n_quantity, @quotedetailid
/*If no rows, return immediately*/
if (@@fetch_status = -1)
begin
close o_curs
return
end
/*Loop through each row of query results*/
while (@@fetch_status = 0)
begin
update filteredquotedetail
set quantity = @n_quantity
where quotedetailid = @quotedetailid
end
/*Done with results; close cursor and return*/
close o_curs
END
sir
can i insert values into tables through cursor
Hi Pinal,
I work as a .NET Developer in US. I have been following your posts since i started development. Thanks for the support since then..:). I have a problem joining a table and result set. Here is the problem. I have only single column table with data type int and it is the primary key and I have filled in the values 0 to 23(whole day hours).
Temporary table:
tHours( hour int primary key).
I am performing a select query from the other tables getting the values with columns
dayhour(int)
personalnumber(varchar)
date(timestamp)
balance(double)
So what i trying to do is insert zero value for hour for which there is no balance
Ex:
0 2323232 10/29/2008 56
1 2323232 10/29/2008 0
..
..
23 2323232 10/29/2008 48
I pulling out my hair since 3 days of how to do this…
Your help is much appreciated!
Thanks
i used your code pinaldave , i would like to thank you very much , my suggestion when you used this code ,fill all values into table ex from 1 to 10 then run the cursor if you need serial number
Hi Pinal how can i use if else inside the Cursor?
I am trying to insert records into a table with seqn #
My code is not doing what it is suppose to be doing.
drop table #relationtable
create table #relationtable
(
id varchar(10),
Relation_type varchar(20),
co_id varchar(10),
target_relation_type varchar(20),
updated_by varchar(15),
seqn int
)
insert into #relationtable (id, Relation_type,co_id,target_relation_type,updated_by,seqn)
select id,’CONTACT_I’ as relation_type, co_id as target_ID,’CONTACT_C’ as Target_relation_type,
‘SCHANDRA’ as upated_by ,” as SEQN from name
where id in (‘184918′,’181641′,’116787’)
DECLARE @ID char(11),
@seqn int
set @seqn = 1
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT id
FROM #relationtable
OPEN c1
FETCH NEXT FROM c1
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
update #relationtable
set seqn = @seqn
set @seqn = @seqn + 1
FETCH NEXT FROM c1
INTO @ID
END
CLOSE c1
DEALLOCATE c1
select * from #relationtable
I was expecing to see Seqn # as 1,2,3 instead it is set to 3 on all the records.
Could you please help?
Thanks
Shashi
how should i use cursor for make a dynamic query. like
declare @test_temp_count_one cursor
declare @test_temp_query_one nvarchar(1000)
declare @VAR_DIVIDE1 varchar(20)
declare @var_table varchar(100)
declare @hold_temp_value varchar(20)
set @VAR_DIVIDE1 = 10
set @var_table = ‘table13’
set @hold_temp_value = 20
set @test_temp_query_one = ‘ SET
@test_temp_count_one = (SELECT TOP ‘ + @VAR_DIVIDE1 + ‘ id FROM ‘+ @var_table +’ WHERE ID > ‘ + @hold_temp_value + ‘ ORDER BY ID ) ; OPEN @test_temp_count_one ‘
exec (@test_temp_query_one)
when i execute this code , i found an error……
error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable ‘@test_temp_count_one’.
can anybody help me…….
thanks in advance