How can one call a stored procedure for each row in a table, where the columns of a row are input parameters to the sp ***without*** using a Cursor?

SQL Call Stored Procedure for each Row without using a cursor

I&#39;m completely new to [Python&#39;s sqlite3 module][1] (and SQL in general for that matter), and this just completely stumps me. The abundant lack of descriptions of [`cursor` objects][2] (rather, their necessity) also seems odd. 

This snippet of code is the preferred way of doing things:

    import sqlite3
    conn = sqlite3.connect(&quot;db.sqlite&quot;)
    c = conn.cursor()
    c.execute(&#39;&#39;&#39;insert into table &quot;users&quot; values (&quot;Jack Bauer&quot;, &quot;555-555-5555&quot;)&#39;&#39;&#39;)
    conn.commit()
    c.close()

This one isn&#39;t, even though it works just as well and without the (seemingly pointless) `cursor`:

    import sqlite3
    conn = sqlite3.connect(&quot;db.sqlite&quot;)
    conn.execute(&#39;&#39;&#39;insert into table &quot;users&quot; values (&quot;Jack Bauer&quot;, &quot;555-555-5555&quot;)&#39;&#39;&#39;)
    conn.commit()

Can anyone tell me why I need a `cursor`?
&lt;br /&gt;It just seems like pointless overhead. For every method in my script that accesses a database, I&#39;m supposed to create and destroy a `cursor`?
&lt;br /&gt;Why not just use the `connection` object?


  [1]: https://docs.python.org/3.6/library/sqlite3.html
  [2]: https://docs.python.org/3.6/library/sqlite3.html#cursor-objects

Why do you need to create a cursor when querying a sqlite database?

I can understand wanting to avoid having to use a cursor due to the overhead and inconvenience, but it looks like there&#39;s some serious cursor-phobia-mania going on where people are going to great lengths to avoid having to use one.

For example, one question asked how to do something obviously trivial with a cursor and the accepted answer proposed using a common table expression (CTE) recursive query with a recursive custom function, even though this limits the number of rows that could be processed to 32 (due to recursive function call limit in sql server). This strikes me as a terrible solution for system longevity, not to mention a tremendous effort just to avoid using a simple cursor.

What is the reason for this level of insane hatred? Has some &#39;noted authority&#39; issued a fatwa against cursors? Does some unspeakable evil lurk in the heart of cursors that corrupts the morals of children or something?

Wiki question, more interested in the answer than the rep.

Related Info:

[https://stackoverflow.com/questions/37029/sql-server-fast-forward-cursors](https://stackoverflow.com/questions/37029/sql-server-fast-forward-cursors)

EDIT: let me be more precise: I understand that **cursors should not be used instead of normal relational operations**; that is a no-brainer. What I don&#39;t understand is people going waaaaay out of their way to avoid cursors like they have cooties or something, even when a cursor is a simpler and/or more efficient solution. It&#39;s the irrational hatred that baffles me, not the obvious technical efficiencies.

Why do people hate SQL cursors so much?

How do I serialize pyodbc cursor output (from `.fetchone`, `.fetchmany` or `.fetchall`) as a Python dictionary?

I&#39;m using bottlepy and need to return dict so it can return it as JSON.

Output pyodbc cursor results as python dictionary

I have a cursor containing several columns from the row it brings back that I would like to process at once.  I notice most of the examples I&#39;ve seeing on how to use cursors show them assigning a particular column from the cursor to a scalar value one at a time, then moving to the next row, 

e.g.

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
           --Do Stuff with @name scalar value, then get next row from cursor
    
           FETCH NEXT FROM db_cursor INTO @name  
    END

What I want to know is if it&#39;s possible to do something like the following:

        OPEN db_cursor  
        FETCH NEXT FROM db_cursor; 
        
        WHILE @@FETCH_STATUS = 0  
        BEGIN  
               SET @myName = db_cursor.name;
               SET @myAge = db_cursor.age;
               SET @myFavoriteColor = db_cursor.favoriteColor;
               --Do stuff with scalar values

               FETCH NEXT FROM db_cursor; 
        END

Help is always appreciated.

Get Multiple Values in SQL Server Cursor

How do I store a selected field value into a variable from a query and use it in an update statement?

Here is my procedure:

I&#39;m writing a SQL Server 2005 T-SQL stored procedure which does the following:

1.  gets list of invoices id&#39;s from invoice table and stores to Cursor 
2.  Fetch invoice id from cursor -&gt; tmp_key variable 
3.  foreach tmp_key finds invoice client primary contact id from customer table
4.  updates the client contact key with primary contact id 
5.  close cursor

Here is my code: 

    DECLARE @tmp_key int
    DECLARE @get_invckey cursor 
    
    set @get_invckey = CURSOR FOR 
    	select invckey from tarinvoice where confirmtocntctkey is null and tranno like &#39;%115876&#39;
    
    OPEN @get_invckey 
    
    FETCH NEXT FROM @get_invckey into @tmp_key
    
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
    	SELECT c.PrimaryCntctKey as PrimaryContactKey
    	from tarcustomer c, tarinvoice i
    	where i.custkey = c.custkey and i.invckey = @tmp_key
    
    	UPDATE tarinvoice set confirmtocntctkey = PrimaryContactKey where invckey = @tmp_key
    	FETCH NEXT FROM @get_invckey INTO @tmp_key
    END 
    
    CLOSE @get_invckey
    DEALLOCATE @get_invckey

How do I store the PrimaryContactKey and use it again in the set clause of the following update statement?  Do I create a cursor variable or just another local variable with an int type?

How to assign a select result to a variable?

I have a routine that runs different queries against an SQLite database many times per second. After a while I would get the error 

`&quot;android.database.CursorWindowAllocationException: - Cursor window allocation of 2048 kb failed. # Open Cursors = &quot;` appear in LogCat.

 I had the app log memory usage, and indeed when usage reaches a certain limit the I get this error, implying it runs out. My intuition tells me that the database engine is creating a NEW buffer (CursorWindow) every time I run a query, and even though .close() the cursors, neither the garbage collector nor `SQLiteDatabase.releaseMemory()` are quick enough at freeing memory. I think the solution may lie in &quot;forcing&quot; the database to always write into the same buffer, and not create new ones, but I have been unable to find a way to do this. I have tried instantiating my own CursorWindow, and tried setting SQLiteCursor to it, but to no avail.

&#191;Any ideas?

EDIT: re example code request from @GrahamBorland:

    public static CursorWindow cursorWindow = new CursorWindow(&quot;cursorWindow&quot;); 
    public static SQLiteCursor sqlCursor;
    public static void getItemsVisibleArea(GeoPoint mapCenter, int latSpan, int lonSpan) {
    query = &quot;SELECT * FROM Items&quot;; //would be more complex in real code
    sqlCursor = (SQLiteCursor)db.rawQuery(query, null);
    sqlCursor.setWindow(cursorWindow);
    }

Ideally I would like to be able to `.setWindow()` before giving a new query, and have the data put into the same `CursorWindow` everytime I get new data. 

SQLite Android Database Cursor window allocation of 2048 kb failed

Is there anyway to loop through a table variable in T-SQL?

    DECLARE @table1 TABLE ( col1 int )  
    INSERT into @table1 SELECT col1 FROM table2

I use cursors as well, but cursors seem less flexible than table variables.

    DECLARE cursor1 CURSOR  
        FOR SELECT col1 FROM table2  
    OPEN cursor1  
    FETCH NEXT FROM cursor1

I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.

Any help is greatly appreciated.

Can I loop through a table variable in T-SQL?

I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether.  Effectively, I want to implement this:

    $result = mysql_query(&quot;SELECT something FROM somewhere WHERE some stuff&quot;);
    while ($row = mysql_fetch_assoc($result)) {
        // check values of certain fields, decide to perform more queries, or not
        // tack it all into the returning result set
    }

Only, I want it only in MySQL, so it can be called as a procedure.  I know that for triggers, there is the `FOR EACH ROW ...` syntax, but I can&#39;t find mention of anything like this for use outside of the `CREATE TRIGGER ...` syntax.  I have read through some of the looping mechanisms in MySQL, but so far all I can imagine is that I would be implementing something like this:

    SET @S = 1;
    LOOP
        SELECT * FROM somewhere WHERE some_conditions LIMIT @S, 1
        -- IF NO RESULTS THEN
        LEAVE
        -- DO SOMETHING
        SET @S = @S + 1;
    END LOOP
    
Although even this is somewhat hazy in my mind. 

For reference, though I don&#39;t think it&#39;s necessarily relevant, the initial query will be joining four tables together to form a model of hierarchal permissions, and then based on how high up the chain a specific permission is, it will retrieve additional information about the children to which that permission should be inherited.

Looping Over Result Sets in MySQL

I have a table with huge amount of data. I&#39;d like to add extra column `id` and use it as a primary key. What is the better way to fill this column with values from one `1` to `row count` 

Currently I&#39;m using cursor and updating rows one by one. It takes hours. Is there a way to do that quicker?

Thank you

SQL Server: how to add new identity column and populate column with ids?

I knew of some performance reasons back in the SQL 7 days, but do the same issues still exist in SQL Server 2005? If I have a resultset in a stored procedure that I want to act upon individually, are cursors still a bad choice? If so, why?

Why is it considered bad practice to use cursors in SQL Server?

Would anyone know how I can view what a cursor has in it during debugging so that I can determine the functionality of my database helper?

It keeps acting like it&#39;s returning data, but then when I attempt to use the `cursor.isNull(0)` method, I keep getting `NullPointerException` thrown and not being able to see what the cursor has in it while stepping through the execution is really frustrating me.

Any help would be extremely appreciated.

Thanks.

Viewing an Android database cursor

I am using custom adapter extending cursor adapter for displaying data in listview, to display particular phone number i have passed the id to a method in database class but it is showing 

    errorandroid.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0 

while placing debugger in the the method it is not going after the line

    num = cursor.getString(cursor.getColumnIndex(&quot;ContactNumber&quot;));

Can any one help me to solve it.
This is the code:

    public String getNumberFromId(int id) 
	{
		String num;
		db= this.getReadableDatabase();
		Cursor cursor = db.query(scheduletable, new String[] { &quot;ContactNumber&quot; },&quot;_id=&quot;+id, null, null, null, null);
		cursor.moveToFirst();
		num = cursor.getString(cursor.getColumnIndex(&quot;ContactNumber&quot;)); 
		cursor.close();
		db.close();
		return num;
	}

android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0

I have a dynamic SQL statement I&#39;ve created in a stored procedure. I need to iterate over the results using a cursor. I&#39;m having a hard time figuring out the right syntax. Here&#39;s what I&#39;m doing.

    SELECT @SQLStatement = &#39;SELECT userId FROM users&#39;

    DECLARE @UserId

    DECLARE users_cursor CURSOR FOR
    EXECUTE @SQLStatment --Fails here. Doesn&#39;t like this

    OPEN users_cursor
    FETCH NEXT FROM users_cursor
    INTO @UserId

    WHILE @@FETCH_STATUS = 0
    BEGIN

    EXEC asp_DoSomethingStoredProc @UserId

    END
    CLOSE users_cursor
    DEALLOCATE users_cursor

What&#39;s the right way to do this?


Using a cursor with dynamic SQL in a stored procedure

I have a table layout that I want to populate with the result from a database query. I use a select all and the query returns four rows of data. 

I use this code to populate the TextViews inside the table rows.

```java
Cursor c = null;
c = dh.getAlternative2();
startManagingCursor(c);
// the desired columns to be bound
String[] columns = new String[] {DataHelper.KEY_ALT};
// the XML defined views which the data will be bound to
int[] to = new int[] { R.id.name_entry};

SimpleCursorAdapter mAdapter = new SimpleCursorAdapter(this, 
		 R.layout.list_example_entry, c, columns, to);
this.setListAdapter(mAdapter);
```

I want to be able to separate the four different values of KEY_ALT, and choose where they go. I want them to populate four different TextViews instead of one in my example above.

How can I iterate through the resulting cursor?

Iterate through rows from Sqlite-query

I have this trigger:

    CREATE TRIGGER CHECKINGMAXQTYDAYSVACANCY
    	ON TDINCI
    AFTER INSERT 
    AS
    	DECLARE
    		@incidentCode int,
    		@dateStart datetime,
    		@dateEnd datetime,
    		@daysAccumulated int,
    		@maxDaysAvailable int
    
    	set @daysAccumulated = 0;
    
    	select @incidentCode = CO_INCI from inserted;
    	select @maxDaysAvailable = IN_DIAS_GANA from TCINCI
    		where CO_INCI = @incidentCode;
    
    	declare detailsCursor CURSOR FOR
    		select FE_INIC, FE_FINA from TDINCI
    		where CO_INCI = @incidentCode;
    
    	open detailsCursor;
    	
    	if CURSOR_STATUS(&#39;variable&#39;, &#39;detailsCursor&#39;) &gt;= 0
    	begin
    		fetch next from detailsCursor
    			into @dateStart, @dateEnd;
    
    		while @@FETCH_STATUS = 0
    		begin
    			set @daysAccumulated = @daysAccumulated + (DATEDIFF(DAY, @dateStart, @dateEnd) + 1);
    
    			fetch next from detailsCursor
    			into @dateStart, @dateEnd;
    		end
    		close detailsCursor;
    		deallocate detailsCursor;
    	end
    	IF(@maxDaysAvailable &gt; @daysAccumulated)
    	BEGIN
    		RAISERROR (&#39;No se pueden ingresar mas dias de los programados en la cabecera de incidencias.&#39;, 16, 1);
    		ROLLBACK TRANSACTION;
    		RETURN 
    	END
    GO

When I do a Insert to the table TDINCI 

    INSERT INTO TDINCI 
    VALUES (1, &#39;20150101&#39;, &#39;20150115&#39;, &#39;2015-2015&#39;)

I get an error: 

&gt; A cursor with the name &#39;detailsCursor&#39; already exists.

I open

    open detailsCursor;

and close the cursor.

    close detailsCursor;
    deallocate detailsCursor;

Maybe there is something with the scope of cursor that I don&#39;t manage? Thanks in advance.

Why do I get &quot;A cursor with the name already exists&quot;?

It is based on the interview question that I faced.

Very short definition can be

&gt; It can be used to manipulate the rows
&gt; returned by a query.

Besides the use of the cursor (Points are listed [here][1] on MSDN), I have a question in my mind that if we can perform all the operations using query or stored procedure (if I&#39;m not wrong, Like we can use Transact-SQL for ms-sql), is there any concrete point that we should use cursor?


  [1]: http://msdn.microsoft.com/en-us/library/ms191179.aspx

All Database Cursor Solutions on Gang of Coders

Total of 17 Database Cursor Solutions