New answers tagged stored-procedures
CREATE TEMPORARY TABLE IF NOT EXISTS temp (Months VARCHAR(50),Sequence INT) AS ( SELECT CONCAT(MONTHNAME(m1),' ',YEAR(m1)) AS Months,CONVERT(m1,DATE) AS Sequence FROM ( SELECT ('2014-01-01' - INTERVAL DAYOFMONTH('2014-01-01')-1 DAY) +INTERVAL m MONTH AS m1 FROM ( SELECT @rownum:=@rownum+1 AS m FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) ...
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'; ...
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 ...
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 ')))),' ',' ...
You can have stored procedures execute as the definer instead of the invoker: CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END; See: Access Control for Stored Programs and Views
I would code as follows: SELECT (some values) FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND; DELETE FROM mydb.mytable WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL 0 SECOND AND m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL ...
Try this I believe the first part already evaluates to 00:00:00. So I just added the seconds in. WHERE m_dt BETWEEN (CURDATE() - INTERVAL 60 DAY) AND (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))
How about: select @@hostname into your_variable;
It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.
Drop the table if it exists before the next run. ... begin drop table if exists test; create temp table test(up_link double precision, time_stamp timestamp without time zone) on commit drop; a := endIndex - startIndex; ... Or, as @dezso suggests, you could create the table only if it doesn't exist. ... begin create temp table IF NOT EXISTS test(up_link ...
You can't embed DDL in a stored procedure like this. Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.
inet_client_addr() is a system information function. It is located in the schema pg_catalog like other built-in functions (except for additional modules). pg_catalog is automatically part of the search_path. Per documentation: In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables ...
Must be postgres user or other Superuser for mine. Only need create schema privilege for Erwin's answer. start tests: alter FUNCTION INET_CLIENT_ADDR() rename to _inet_client_addr; CREATE OR REPLACE FUNCTION inet_client_addr() returns inet language sql stable cost 1 as ' select ''192.168.0.1''::inet; '; select inet_client_addr(); end tests alter ...
Thanks for migrating my question. I solved the problem. It seems that the tool I used to migrate my DB from EC2 to RDS added a bunch of weird indexes which caused the performance of the DB to tank. Once I removed any and all elements of the DB with 'azure' in their name my performance returned to normal! The tool was extremely useful but if anyone ...
You should specify the length of the varchar column e.g. varchar(2000).
Perhaps you could add a counter to the loop so that every 100 rows, you run SLEEP for one second. I will add that code: DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_UsersLoop`$$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_UsersLoop`( ) BEGIN DECLARE v_BatchCount,v_BatchLimit INTEGER; DECLARE v_user_id INTEGER DEFAULT 0 ; DECLARE v_officialjobtitle ...
Top 50 recent answers are included