Tag Info

Hot answers tagged

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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 .... ...


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.


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 ...


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 ...


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.


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 ...


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 ...


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 ...


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 ...


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), ...


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 ...


1

You can try to modify the testproc in the following way. I have tryed to be more simple as possible: DELIMITER $$ CREATE PROCEDURE `testproc`() BEGIN START TRANSACTION; select 'try to acquire lock'; SELECT CurrentSize, BucketSize into @cursize, @bucketsize FROM TestTable WHERE id = 1 FOR UPDATE; select 'acquired lock'; -- only one thread is ...



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