Questions tagged [cursors]

SQL Cursor : Database Object That Provisions Access to Records in a Database

Filter by
Sorted by
Tagged with
1
vote
1answer
62 views

Inserting Data using Cursor but check if the data already in the table

I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, ...
0
votes
1answer
41 views

Replicate multiple times a set of rows with unique column in a table

I wish to replicate a set of rows in a table but with handling unique column in SQL Server 17. The table is: CREATE TABLE test1 ( id int CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED IDENTITY, ...
0
votes
0answers
16 views

Can you use a SELECT … FOR UPDATE cursor in a stored procedure to enforce row-level locking?

From https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/re37.html: CREATE PROCEDURE needy_bonus( ) BEGIN DECLARE v_employee_id INT; DECLARE v_salary ...
0
votes
1answer
31 views

Perl Script to call Oracles stored procedure with input parameter and OUTPUT parameter ,CURSOR

I am finding difficulty in fetching the records from stored procedure(oracle) by invoking it through perl script. SP takes 1 input and ouput parameter is cursor --------Here is the perl script_-----...
1
vote
2answers
57 views

Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?

I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to ...
0
votes
1answer
45 views

Preserve capitalization of column names/keys returned by SYS_REFCURSOR in Oracle

I am using a SYS_REFCURSOR to return JSON data to an ORDS endpoint. I need the case of the returned column names/JSON keys to be preserved for use in my node application. I have tried the following: ...
1
vote
1answer
27 views

How to improve performance merging multiple table via cursor

I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query BEGIN DECLARE done INT ...
-1
votes
1answer
29 views

Stored Proc hangs when no rows exist in cursor? [closed]

I'm on 8.0.19 if that matters. Here is my test proc: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `testNullProc`() main:BEGIN DECLARE fileID INTEGER; DECLARE finished INTEGER ...
1
vote
0answers
24 views

How to write Procedure to fetch data from other table?

drop procedure if exists findClosestTimeStamp; delimiter $$ create procedure findClosestTimeStamp() begin declare tt1 time; declare dt1 date; declare view1 varchar(30); declare done int ...
0
votes
1answer
17 views

Set variable inside cursor for to use in select statement [closed]

This is my query to create procedure, but its displaying error like : 'DECLARE curBalRows CURSOR FOR select if( What I'm missing here? DELIMITER $$ drop procedure if exists `...
0
votes
1answer
61 views

MySQL - Pass input parameter into Cursor query

Is it possible to pass input parameter into Cursor SELECT statement WHERE clause? For some reason I think it isn't working. I'm trying to pass _TAG and _ITEM_NAME into where clause. DECLARE ...
0
votes
1answer
41 views

Get availability group and listener of the current DB (dynamic SQL)

I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
0
votes
0answers
36 views

relation “critere1” does not exist error , cursor problem

I have the following error : relation "critere1" does not exist CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 43 and here is my PL/pgSQL : DECLARE CRITERE1 CURSOR FOR ...
0
votes
0answers
22 views

Is it possible for a FOR LOOP cursor execute operations asynchronously?

I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the ...
0
votes
1answer
31 views

MySQL cursor always exits out of loop

The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop. Anything wrong here? I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it ...
2
votes
1answer
107 views

Cursoring over sys.databases skips databases

I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem: I have the following code to perform database backups. ...
0
votes
2answers
65 views

Overview of leaked/unclosed cursors/statements on DB2 LUW server?

We're encountering a "too many open cursors issue" on DB2/LUW. Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors? We're doing ...
2
votes
1answer
19 views

Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?

In this example: DECLARE @KeyField INT, @ValueField INT DECLARE CursorName CURSOR FAST_FORWARD FOR SELECT T1.KeyField, T1.ValueField FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.ValueField = ...
0
votes
1answer
25 views

Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows

I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...
0
votes
2answers
141 views

How to drop a procedure across a lot of databases (SQL Server 2016)

I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
-2
votes
1answer
64 views

SQL Server: Use Implicit or Explicit Cursor? [closed]

SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHILE (1=1) BEGIN EXEC 'drop view ' + Table_Name END or DECLARE my_cursor CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES OPEN ...
0
votes
1answer
116 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
7
votes
1answer
178 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
0
votes
1answer
571 views

Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server

I get this error when I execute this cursor. Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared. But ...
1
vote
1answer
100 views

SQL Server - Cursors - Missing Afterthought - Safeguard against infinite looping in case of missing FETCH NEXT

It was found that this code mistake caused an infinite loop on PROD: DECLARE @BatchID INT DECLARE MyCursor CURSOR FOR SELECT BatchID = ... OPEN MyCursor FETCH NEXT FROM MyCursor INTO @BatchID ...
0
votes
0answers
270 views

Must declare the scalar variable in cursor SQL SERVER

I have this problem with this code. I don't know why, because before set values.I declare a variable. Moreover, How i can execute every results that cursor give Error message: Msg 137, Level 15, ...
0
votes
1answer
45 views

Insert top 253 values where value in select distinct tradernames

I am trying to insert top 253 values for each trader from a temp traders table into temp results table. I am using following code to do it. CREATE TABLE #trad ( Trad varchar(50)) IF @trad is ...
6
votes
2answers
1k views

Infinite loop in CURSOR

I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an ...
-1
votes
1answer
1k views

Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle

Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query just with the aim of practicing 'Cursor's . I have three tables with below structures : Student:(Student_id , ...
0
votes
2answers
24 views

finding duplicates and updating a column with values according with number of duplicates using mysql stored procedure

I am using Mysql 10.1.29-MariaDB Database to create a new table. What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For example,...
0
votes
1answer
29 views

sp_BlitzCache and cursors

I'm running sp_BlitzCache (Brent Ozar) to get some information about database performance. The program that "attacks" this database uses cursors everywhere, so sp_BlitzCache gives me several lines ...
0
votes
1answer
340 views

In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!. The problem is that I have a cursor that is crossed, but believe ...
1
vote
1answer
241 views

Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

Hi everyone hope you're doing well. I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not ...
0
votes
2answers
43 views

Split the results of a query into parts

I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor: An extra 4% for the first quarter with the highest salary. An extra 6% ...
3
votes
2answers
55 views

How do I extend the character limit of a string in management studio

The SQL below works to an extend however when I view the output in SSMS tables with a lot of columns appear to be getting cut of in that the string that is generated by the cursor only appears to go ...
-1
votes
1answer
168 views

T SQL Cursor is only updating row with same value [closed]

I'm using MS SQL Server and I have the below table 'tmp_AVG_Weekly_Sales': RowID SKU Shop Week Avg_Value LAMBDA PMF Value 1 ABC 200 2 1 2 0.13 ...
0
votes
2answers
79 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
0
votes
1answer
68 views

Job with a cursor is abnormally slow [closed]

I have a query that takes more than 9 hours to complete. Looking at the execution plan, I find this high-cost operator (query plan link): How can I improve this job?
4
votes
1answer
861 views

How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

When I want to write to or alter particular cell of an excel file/table using python I'll use pandas read_csv then alter value in particular cell and write back to file with to_csv. But writing back ...
6
votes
3answers
1k views

Cursor Replacement for Newbies

I'd like to know what the general replacement is for a cursor. The general implementation of a cursor I see out and about is DECLARE @variable INT, @sqlstr NVARCHAR(MAX) DECLARE cursor_name CURSOR ...
1
vote
2answers
40 views

Cursor-SQL Help- Need another Column for Dynamic SQL

My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown') ...
4
votes
0answers
583 views

Combine multiple stored procedures into one report? [closed]

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is ...
1
vote
2answers
742 views

Cursor never stops

I appear to have made an error in this cursor and I can't seem to figure out what I have done wrong. I've confirmed that the select pulls back 2 rows. But when I pass it into the cursor to pick ...
-1
votes
1answer
116 views

SQL Server _ Cursor in Dynamic SQL and UPDATE CASE WHEN THEN

I would like to extract the rows that meet the condition in the CASE WHEN condition and put them in the updated table. But the trick is to create a new row every time the condition is met in one of ...
0
votes
1answer
43 views

SQL Server - Cursor and Dynamic SQL

this query is working but not giving me the output I want. I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But ...
0
votes
1answer
61 views

Arithmetic overflow for Variable in Openrowset

I try to create a table to store the used storage Information for every Database. Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information. ...
2
votes
2answers
729 views

Pass data from a table row into a create login statement using dynamicSQL [closed]

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more. We've done NO work concerning cursors/...
0
votes
1answer
3k views

if exists, update else insert, with cursors in stored procedures only returns 1 row

I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular ...
2
votes
2answers
7k views

Using cursor to update if exists and insert if not

I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the ...
0
votes
1answer
94 views

SQL job ignoring IF condition

I have a job with a few steps(that run reports information) and i added a query i wrote to the last step. Sometimes the job fails because of factors i can not control (application issues) and my query ...