Tag Info

New answers tagged

-1

The problem is, when you try to EXECUTE your DYNAMIC query the query returns NULL when there's not found rows. Test: mysql> CALL drop_tables_like( -> '6', #myint INT, -> 'onare', #pattern VARCHAR(255), -> 'PRUEBA'); #db VARCHAR(255)) +--------+ | sqlSTR | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows ...


-1

Use DECLARE CONTINUE HANDLER FOR SQLSTATE '1064' use link https://dev.mysql.com/doc/refman/5.0/en/declare-handler.html @Vietyank Your procedure working fine.The message showing is not an error it is an warning. Example: I have created a Database Test1; mysql>Create Database Test1; ...


0

You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this. A quick stored procedure should do the job: DROP PROCEDURE IF EXISTS proc_loop_test; CREATE PROCEDURE proc_loop_test() BEGIN DECLARE int_val INT DEFAULT 0; test_loop : LOOP IF (int_val = 10) THEN LEAVE test_loop; END IF; SET ...


1

Had a situation where a production environment had DB schema updated The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ...


0

One ad hoc improvement: poor: for root in all_fitting_roots LOOP FITTING := FITTING + 1; END LOOP; better: SELECT COUNT(DISTINCT ROOT_ID) into FITTING FROM ( SELECT UNIQUE_ID, ROOT_ID, FIRST_NAME, LAST_NAME, POSTAL_CODE, FSA FROM (SELECT p.*, CONNECT_BY_ROOT UNIQUE_ID AS ROOT_ID FROM UNIQUE_PHYSICIAN p START WITH PARENT_PHYS_ID IS ...


4

From here: (My emphasis below) The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


1

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)) You missed one more closed parenthesis in your statement CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))) 2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them. example :First ...


3

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


2

I found this: SOURCE There is great reason why you should never call stored procedures from within triggers. Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive ...


1

You are trying to output avg_sal.salary which is not defined. Use alias in the cursor body : .... cursor c1 is select avg(e.salary) AS salary ... Side notes. It seems that parameter passed (ddept_id ) is not used anywhere inside the procedure except printing. Also, it's been long time since JOIN syntax was introduced and it's much more readable ...


0

I think you're going about this the wrong way. First off, I don't think you need a procedure/function to solve your problem. Second, I definitely don't see how a cursor should come in to it. Try the following window query using the OVER() clause: SELECT avg(e.salary) OVER(PARTITION BY d.dname) FROM emp e, works w, dept d WHERE e.eid=w.eid and d.did = ...


0

If the table tMaster have the same pid inserted but is not in the table trendContentMaster it will update 20*(rows) depending how many times pid is inserted in tMaster table. This should work for you: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats( IN mongoid VARCHAR(255), IN numericid INT, IN points INT, IN ctype INT, IN ...


2

There is the DO command for ad-hoc plpgsql execution. But you cannot pass parameters or return rows directly to / from a DO command. BTW, strictly speaking, Postgres does not have stored procedures. Just functions providing very similar functionality.


1

I'm trying some ways to make something that could work for you but MySQL has limitations as I see. I'm trying to make a SP that you could read all the information of the procedures from a table. Example: The SP: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`( IN eProcedureID int(11), IN eUser VARCHAR(50), ...


2

That is not a supported syntax as a stored procedure can return multiple result sets or do other data manipulations and return nothing. You would need to convert your stored procedure to a table valued function or insert the results into a temp table.


0

create table #tmp1 ( name varchar(100), email varchar(10),phone varchar(10)) insert INTO #tmp1 EXEC [sp_Test] select * from #tmp1


4

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc. Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO .... This particular example must be written in PL/PgSQL using EXECUTE because it ...


2

CREATE TYPE [tvf_id] AS TABLE ( [id] [int] NULL ,[OrdCol] [INT] NOT NULL ) GO CREATE PROCEDURE [sp_Test] @pID tvf_id READONLY as begin set nocount on SELECT a.* FROM datatable a inner join @pID b on a.id = b.id ORDER BY b.OrdCol ASC end GO -------------------------------------------------------- DECLARE @data tvf_id ...


-1

cc needs INDEX(pap, stati) (or in the opposite order). Please provide SHOW CREATE TABLE.


2

Configure Database Mail Create a sql agent job to run below t-sql (you dont need a stored procedure for what you are asking, but below code can be easily converted into a stored procedure --> I will leave that up to you - hint: just look for Create procedure as ..) ---- first check if there are any rows returned ... as we dont want to send empty email .... ...


0

You're trying to concat an int to a string in the print statements. print N'Monday = ' + @Monday; If you're desired output is Monday = 123 as a string, the @Monday variable will need to be converted to a string type, like so: print N'Monday = ' & CONVERT(NVARCHAR(10),@Monday); There is also no need for the alias column in your selects if you are ...


0

In addition to Shawn Melton, because I can not comment yet, you can update your statistics regularly. This will generate a new execution plan. After that recompile your stored procedure with sp_recompile. Here is a link for a great script: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


-1

While I respect the submitter, I humbly disagree with the answer provided and not for "religious reasons". In other words, I believe there is no facility that Microsoft has provided which decreases the need for the guidance to use stored procedures. Any guidance provided to a developer which favors the use of raw text SQL queries must be filled with many ...


6

I often use the following query taken from http://www.sommarskog.se/query-plan-mysteries.html#otherreasons to return the sniffed values. Another way would be to open up the plan in SQL Sentry Plan Explorer (both paid and free versions available) and select the parameters tab. The XML method and plan explorer method will also show the runtime value assuming ...


3

If you have the execution plan you can view it as xml, and search for compiled parameter values For example, this is an actual snippet of an execution plan from one of the queries in my plan cache: <ParameterList> <ColumnReference Column="@P69" ParameterCompiledValue="'2015-06-10 00:00:00.0000000'" /> <ColumnReference ...


0

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column. use AdventureWorks2012; declare @ConstraintName varchar(max) set @ConstraintName = 'CK_Product_ProductLine' --<--- Change here with your constraint Name if exists ( select 1 from sys.objects ...


0

Try permitting TRUNCATE on the table to the user who is not root. https://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html DROP, DELETE, EXECUTE are necessary.


1

Run this in MySQL SET GLOBAL event_scheduler = ON; Add this to my.cnf so the scheduler remains enabled upon mysql restart [mysqld] event_scheduler = ON Start the Event on the Next Hour, Make it Recurring Every Hour, Adjusting WHERE clause CREATE EVENT hourly_temp_avg ON SCHEDULE EVERY HOUR STARTS DATE(NOW()) + INTERVAL (HOUR(NOW())+1) HOUR ...


0

Here's 2 approaches. This approach assumes that the combination (name, city) needs to be unique: INSERT INTO New ( SELECT name, city FROM tbl1 ) UNION DISTINCT ( SELECT name, city FROM tbl2 ) UNION DISTINCT ( SELECT name, city FROM tbl3 ); In this approach, your New table needs to have a UNIQUE KEY (or PRIMARY KEY) that contains all ...



Top 50 recent answers are included