Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
168
questions
0
votes
1answer
13 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
48 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
58 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
26 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
171 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
72 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
60 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
77 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
0answers
20 views
Calculating order rank after union
I'm trying to run a query such as this:
select id, rank() over(order by last_name, age, id) as position
from
(
select id, first_name, last_name, age from public.user
union
select '925c26bf-5971-...
0
votes
1answer
44 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
851 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
472 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
22 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 ...
0
votes
1answer
23 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
48 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 ...
0
votes
1answer
145 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
41 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
42 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
88 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
36 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
66 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
856 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 ...
5
votes
3answers
769 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
32 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
342 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
632 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
40 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
51 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
604 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
2k 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
5k 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
65 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 ...
1
vote
1answer
44 views
How to select info about indexes with specified columns from all databases in the instance using TSQL?
This query selects all the indexes - its names, fragmentation, and some other parameters from the current database.
select
idx.[name] as [index_name],
sc.[name] as [schema_name],
obj.[...
1
vote
0answers
158 views
Oracle Max Open Cursors - What does table_ mean?
We're encountering a lot of maximum open cursors recently so we ran a query to check for the maximum open cursors.
The query to check for open cursors looks something like this
select * from v$...
1
vote
0answers
749 views
HANA SQL procedure Iteration
Here is my requirement for a SAP HANA 2.0 system. 1) Identify users with not having SAML flag enabled from "SYS."USERS" table. Fields that I need from this table are USER_NAME,EXTERNAL_IDENTITY,...
3
votes
2answers
1k views
Cursor causes SSMS to crash
I have a cursor that generates one record of JSON text from a group of tables. The cursor has been making SSMS crash. The script runs for a time then SSMS fails. Below is the code that I have ...
0
votes
1answer
401 views
Getting two or more column values through a Cursor SQL Server
I have two tables like this:
Table Experiment (I will just use one roadID (rID) for now)
rID | latBegin |longBegin |latEnd |longEnd
041 | 23.875464|-80.456798|30.549879|-83.5465521
041 | 33....
1
vote
2answers
339 views
Stored Procedure return unwanted cursor result
Context
I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one.
...
2
votes
1answer
345 views
Can't reduce cost of query plan and get rid of Key Lookup because of cursor
I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere .
I have read here that it has ...
1
vote
1answer
64 views
Severity 20 - Fatal Error in LOCAL CURSOR after FETCH
This week our database on a SQL2014 SP2 CU11 server started throwing fatal errors (severity 20), on the FETCH statement of code, which could be reduced to:
DECLARE @dummy int
DECLARE CurName CURSOR ...
1
vote
0answers
301 views
Why does this SQL Server execution plan contain a snapshot cursor for a select statement?
Here is the situation. I have a fairly simple query. It returns ~22k rows:
select * from tbl_A
When I run this query in SSMS (2017 7.6), I get a fairly simple plan:
SELECT <= Clustered Index ...
3
votes
0answers
225 views
In SQL Server is a FAST_FORWARD cursor DYNAMIC by default? [duplicate]
I've been reading the DECLARE CURSOR (Transact-SQL) Books Online article. It states:
FAST_FORWARD:
Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.
and
...
2
votes
2answers
639 views
Need to run select statement across all databases
I am trying to run a query like the one below across multiple databases
select ParamValue from Table1 where paramname = 'SYSTEM_LICENSE'
Each DB is named something like the one below and some DB ...
3
votes
2answers
2k views
MySQL - UUID/created_at cursor based pagination?
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
1
vote
2answers
2k views
script all triggers in a database
I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.
Every time I need to reinitialise those database I need to script ...
1
vote
0answers
28 views
The actual utilization of Ref Cursor is at Database level not at the Database Client level. Is this statement correct?
From my understanding, the purpose of a ref cursor is to pass a Result Set among procedures/functions(at the DB level) through a "pointer" without passing the actual Result Set which can consume a lot ...
2
votes
2answers
613 views
sp_cursoropen chooses terrible execution plan
If I execute my (simple) query directly in SQL Server Management Studio...
SELECT auftrag_prod_soll.ID
FROM auftrag_prod_soll
WHERE auftrag_prod_soll.auftrag_produktion = 51621
AND ...
0
votes
1answer
1k views
MySQL Error 1329 - Empty Cursor Issue
I am experiencing an issue with a stored procedure I wrote some time ago that has recently stopped working. The SP populates a cursor and runs through a loop processing the data in another SP.
The ...
0
votes
1answer
48 views
Mysql database table cursor
i have written the function below containing a cursor while executing it an error appears . what' wrong please
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR FOR
SELECT * ...
