Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
188
questions
0
votes
1answer
23 views
Creating a dynamic db2 reorg script with in-built delay for concurrency
So I'm new to db2, and some of the syntax and other execution restrictions give me trouble. My DB is using DB2 v11.1.2.2. I've created a reorg script which calls the REORGCHK_TB_STATS and ...
0
votes
1answer
60 views
How do I get DBeaver to do a DB2 UPDATE via Cursor
The following query works when run from the console via db2 command. But fails when running via DBeaver.
BEGIN NOT ATOMIC
FOR R AS C CURSOR WITH HOLD FOR
SELECT ROW_NUMBER() OVER() AS INDEX, ID
...
0
votes
0answers
28 views
How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor
Please refer to the following schema at SQL Fiddle when reading the use case below.
In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
0
votes
4answers
36 views
use a string array without CREATE privileges
I have a set of tables where I need to determine if a particular set of about 1000 row IDs are present in one specific field in each table.
select 'Accounts', row_id Acct_id, pr_postn_id
from schema....
1
vote
2answers
116 views
How can I efficiently delete records when a counter reaches zero in PostgreSQL?
Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
1
vote
1answer
119 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
42 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
18 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 ...
1
vote
2answers
180 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
66 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
96 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
50 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
29 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
23 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
375 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
109 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
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
121 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 ...
4
votes
1answer
163 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
98 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
20 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
31 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
298 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
68 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
223 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 (...
8
votes
1answer
193 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
893 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
125 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
435 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
2k 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
25 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
35 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
552 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
318 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
44 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
76 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
263 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
163 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
867 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
43 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
707 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
818 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
198 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
75 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
819 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/...
