Questions tagged [set-based]
51 questions
0
votes
1 answer
Set based calculation for events table, exceptuating an event
I have a table which contains events for change the state of a maintenance service. This is a sample of data:
id date event
234 2009-04-22 10:00:00 Service Request
234 2009-04-22 10:02:00 Service Approbation
234 2009-04-22 10:03:00 Service…

Jhonny D. Cano -Leftware-
- 17,663
- 14
- 81
- 103
0
votes
0 answers
replacing the cursors with set based
I have sql script with a lot of cursors which take too long to execute.I need to replace them with set based operations. I can't understand how can replace them and having the same results.Here is part of the code:
SET @CurAddress = Cursor static…
0
votes
0 answers
Convert Cursors to Set-Based
I need some help. I need to convert this code with cursors to set-based operations. I can't understand how I can make the set-based code do the same as with cursors.
DECLARE Cursor static local FOR
SELECT Cid,Abrev FROM EntityType
OPEN Cur
FETCH…
0
votes
1 answer
how to transfer this iterative solution to a set-based solution
/*
Stored Procedure
April 30, 2021
Mohamad Chaker
*/
USE CIS111_BookStoreMC
GO
--drop the procedure
IF OBJECT_ID('spAssetInfo') IS NOT NULL
DROP PROC spAssetInfo
GO
--Create the stored procedure
CREATE PROC spAssetInfo
AS
--Create a temporary…

Mohamad Chaker
- 11
- 2
0
votes
2 answers
DB stored procedure operation design - set based vs cursor based
I'm told that when designing stored procedures, set based operations scale better than cursor based ones.
Can someone give a succinct explanation of why this is?

hawkeye
- 34,745
- 30
- 150
- 304
0
votes
0 answers
SQL Server Set-Based Update With Multiple Matches
I found this question, which presents the same situation. Trying to do a set-based update, and the table to be updated matchesmultiple rows in the table being used for update values. A minimal example is below:
CREATE TABLE #tempUpdates (ID INT,…

Jeffrey Van Laethem
- 2,601
- 1
- 20
- 30
0
votes
1 answer
Is it possible to replace cursor in SQL when the query is hierarchical?
I have three tables in MsSQL to store data.
First one is the Children contains the following rows
ID,
Name,
Age,
FatherID,
SchoolID
The second one contains the data of their fathers, like this
FatherID,
Name,
Age
The third one is the School with…

Newbie1001
- 131
- 11
0
votes
1 answer
Can a SQL Select statement be converted to a Set-Based select?
I have an SP that creates and runs a SQL query. The end result is used for some reporting. This query though takes a long time to run (greater than 30 seconds is a long time and sometimes goes for over a minute).
I have read a little about…

Mike
- 1,853
- 3
- 45
- 75
0
votes
1 answer
Update trigger does not update for all rows in a set based update query
I have the tables Cities and Countries with the following triggers:
When I update any City, it should insert a row into the Audit table saying 'The City with ID 'any ID' has been modified'. Cities table has a foreign key relationship with Countries…

MAK
- 1,915
- 4
- 20
- 44
0
votes
2 answers
SQL display ID of any customer who has never placed an order, using set-based operations only
Couldn't find anything on this, using set-based operations.
Not sure if I'm on the right track, but I have 2 guesses on how to do this, but I'm not sure if I'm even close, or if MINUS is the right set-based operation to use:
SELECT customerid
FROM…

Jaluvshuskies
- 35
- 1
- 7
0
votes
1 answer
Converting Foxpro program to MySQL stored procedure
I am in a process on converting a legacy system to web app using Ruby on Rails and MySQL.
There are few places that I'm stuck at while converting the data layer to MySQL procedures.
Giving a scenario below;
FUNCTION first_function
SELE Table1
REPL…

vishless
- 829
- 1
- 5
- 28
0
votes
2 answers
Removing While Loop in String Search Query
I have a query that takes one record from #itemtemp, and locates all entries from ReportCSharp that match, inserting those matches into the #link_table. At present, the query runs in 7.5 min, which appears slow for iterating over only 1458 records…

Sauron
- 6,399
- 14
- 71
- 136
0
votes
2 answers
iterative executing stored procedure with a set based approach
I have an issue where I am trying to replace the following code with a different solution. Currently I am using a cursor but it is running to slowly. I am under the understanding that iterative solutions can only be completed with cursors or while…

user2009859
- 5
- 2
0
votes
1 answer
MySQL: The fastest way to split a table based on a condition
I have two tables:
1) is a list of all parameter-ids and the info to which set of parameters the parameter-id belongs
2) is data that includes some of the parameter-ids, and some additional data such as timestamp and values.
I'm designing a…

Flo Win
- 154
- 10
0
votes
1 answer
Low performance set based storedprocedure
I am using following set based code in sql to calculate the customers bill but it takes 3 Min to performs the operation on 40000 records !!! let me know what is the problem ???
with cte…

franchesco totti
- 582
- 1
- 7
- 28