SQL SERVER – Simple Example of Cursor

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_IDFROM AccountsOPEN @getAccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDWHILE @@FETCH_STATUS = 0BEGIN
PRINT @AccountIDFETCH NEXTFROM @getAccountID INTO @AccountIDEND
CLOSE @getAccountIDDEALLOCATE @getAccountID
Reference: Pinal Dave (http://www.SQLAuthority.com), BOL

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.

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)) + ‘%’)

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.

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….

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:

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 .*

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

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)

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!

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.

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.

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?

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: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0x80070070 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: 0x80070070 at Data Flow Task, DTS.Pipeline: There is not enough space on the disk.
Error: 0x80070070 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 0x80004005. 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 0x80004005.
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: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 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: 0x80019002 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 (0x0).

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.

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.

Dear sir ,
Please help me ,
How i get the complete level of Binary tree ,Ex. Suppose in binary tree it complete the pair then its level is 1 , it its child complete the pair both child then parent node complete the level 2 and child node complete the level 1 same as apply for every node .
The main thing is the i create a prco in which pass the node id get the completed level of passing node

EXEC spGetLevel(1)
Ans is 2 means total pair is 3 1 for patent node and 2 pair for child node

If the flag in the Table 1 is set to “Y” the trigger or cursor should check what were the amounts ordered in Table 2 and add these amounts back to the appropriate products in Table 3. There is an option that there are several products ordered under one order id therefore I suppose I need a cursor.
I would be gratefull for your help.
Thank you,

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.