Tag Info

Hot answers tagged

4

Making use of Jeff Moden's Tally-Ho! CSV splitter from here: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values ...


2

So, I found a workaround... First I made a proxy function on which the first proxy function will run : CREATE OR REPLACE FUNCTION p_count_comm( in_id_alias INTEGER ) RETURNS INTEGER AS $func$ BEGIN RETURN (SELECT sum(tmp_nb_commentaire) out_nb_commentaire FROM count_comm(in_id_alias)); END; $func$ LANGUAGE plpgsql; And I modified my first ...


2

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows. I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem). CREATE ...


2

Start out with getting all the procedures from sys.procedures and then use EXEC sp_helptext on each to load the text of the procedures. Search for text to add, add if needed, load the updated text to a variable and execute dynamically.


1

I have a single MySQL query that do all the heavy lifting SELECT TimeDisplay FROM ( SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'', IF(dy=1,'1 day ',CONCAT(dy,' days '))), IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))), IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))), IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ...


1

You could just use the SQL statement: SELECT sum(out_nb_comm) AS sum_out_nb_comm FROM count_comm($in_id_alias); Or, if you want to wrap it in a function, a simple SQL function does the job: CREATE OR REPLACE FUNCTION sum_count_comm(in_id_alias int) RETURNS int AS $func$ SELECT sum(out_nb_comm)::int FROM count_comm($1)); $func$ LANGUAGE sql;


1

Stored Procedures are physically stored in the mysql.proc table as a MyISAM table Simply copy /var/lib/mysql/mysql/proc.frm /var/lib/mysql/mysql/proc.MYD /var/lib/mysql/mysql/proc.MYI If the new installation is the same major version of mysql, copying should do it for you It would be safer to go back to the old setup, start mysql, and dump the stored ...


1

I won't be able to answer all your questions, but for what it's worth: SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs. Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, ...


1

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...


1

If you want to raise error and exit from the procedure, you may want to try return. Example below. alter PROCEDURE sp1 @PersonID int as begin IF (@PersonId is null) --you can check 0, if that is the intention begin RAISERROR(N'Error already raised. See previous errors.', 16, 1); return; end; print '@PersonId is not null'; ...


1

You want to increase the speed of your cursors? Wrap them in a transaction. If you are processing millions of records and don't want/need them all in one transaction, you can commit it on occasion to reduce resources. I did this with a cursor that took an hour to run (this is an extreme case) and afterword it ran in 1 1/2 minutes. I know that does not ...


1

Generally, aborting database queries and DML is not a good idea, and as @Raj already pointed out, killing sessions is also bad idea – actually it's even worse. Your sessions may be running some transactions which consume undo space (in the undo tablespace of the user), hold locks on tables and occupy space (e.g. for row sorting) in the temporary tablespace. ...



Only top voted, non community-wiki answers of a minimum length are eligible