I have a set of date ranges consisting of both partially and fully overlapping dates, like this: Using T-SQL, I would like to create a new set of data, per user, with eliminated overlapping data, extending ranges and removing redundant data where needed, resulting in something like this: Cursors are fine if n…
Tag: stored-procedures
Return multiple fields as a record in PostgreSQL with PL/pgSQL
I am writing a SP, using PL/pgSQL. I want to return a record, comprised of fields from several different tables. Could look something like this: CREATE OR REPLACE FUNCTION get_object_fields(name text)…
What is dynamic SQL?
I just asked an SQL related question, and the first answer was: “This is a situation where dynamic SQL is the way to go.” As I had never heard of dynamic SQL before, I immediately searched this …
How to use a value from one stored procedure in another?
I have the following statement in a stored procedure: DECLARE @Count INT EXEC @Count = GetItemCount 123 SELECT @Count Which calls another stored procedure with the following statement inside: SELECT …
Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
How can I get row count values in MySQL as @@ROWCOUNT does in mssql? Answer For SELECTs you can use the FOUND_ROWS construct (documented here): which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would’ve been witho…
Reset or Update Row Position Integer in Database Table
I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this R…
SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a ‘running total’ into a third temp table which is created using CREATE. Then this r…
Dynamic order direction
I writing a SP that accepts as parameters column to sort and direction. I don’t want to use dynamic SQL. The problem is with setting the direction parameter. This is the partial code: Answer You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make on…
What SQL would I need to use to list all the stored procedures on an Oracle database?
What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I’d like two queries: list all stored procedures by name list the code of a stored procedure, given …
Is it possible to execute a stored procedure over a set without using a cursor?
I would like to execute a stored procedure over each row in a set without using a cursor with something like this: SELECT EXEC dbo.Sproc @Param1 = Table1.id FROM Table1 I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I’d like to use a stored procedure if possibl…