Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

learn more… | top users | synonyms

0
votes
1answer
25 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
0
votes
1answer
442 views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
1
vote
1answer
301 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
4
votes
1answer
53 views

Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them. The user performing the search is restricted as to what items they can see by ...
3
votes
2answers
212 views

Stored procedure returns -4 after catching exception

In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming ...
2
votes
1answer
112 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
1
vote
1answer
210 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
0
votes
1answer
34 views

MYSQL: weird bug with nested stored procedure

I have two stored procedures. The first one fetch language codes in my language table. CREATE PROCEDURE `updateCategoryNameAll` ( IN categorieNumber VARCHAR (10), IN categoryName VARCHAR (128) ) ...
0
votes
1answer
241 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
0
votes
2answers
1k views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
7
votes
7answers
6k views

Stored Procedures under Source Control, best practice

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
6
votes
1answer
11k views

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
4
votes
2answers
157 views

How can I tell what database/procedure is using a linked server?

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL ...
0
votes
0answers
10 views

Is it possible to call java API from mysql stored procedure?

I am using stored procedure to migrate data from one schema to another schema. I need to use some complex logic which is already implemented as java API. Is it possible to call java API from mysql ...
1
vote
2answers
333 views

Stored Procedure count and list the occurrence using like from another table

Actually this is my first procedure apart from basic "Hello world" procedure. I have two tables like this: cars users ------------------------- ...
5
votes
1answer
269 views

SQL Server passing variable to a string in a stored proc

I'm trying to concatenate a custom string with a variable that is an int. 'Investigation is pending for ['+ @investigationidout +'] '. When I hover mouse pointer over the first + sign it says: ...
1
vote
1answer
41 views

Best way of order processing

I would like to write a procedure for order processing Single Order can have multiple items, how can I process the order ? Consider below JSON is an example: { "Order": { ...
2
votes
2answers
37 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
1
vote
1answer
23 views

Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

I have the following stored function for calculating ELO ranking: CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT) RETURNS DOUBLE DETERMINISTIC BEGIN DECLARE wynik DOUBLE; SET wynik = ...
0
votes
1answer
36 views

Use a SP to Archive/Revision related records in several related tables?

I have a database for an application that has a complicated relational table structure. Is there a good way to create revisions of the data, or archive the data before a user makes changes? Ideally ...
0
votes
2answers
2k views

How to Solve ERROR 1064 (42000)?

DROP PROCEDURE IF EXISTS proc_x; DELIMITER # CREATE DEFINER=`root`@`localhost` PROCEDURE proc_x(sequel CHAR) proc_main:BEGIN SET @SQL = CONCAT(@sequel); PREPARE stmt FROM @SQL; EXECUTE stmt; ...
0
votes
1answer
2k views

“Access denied for user” error when creating a procedure

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK ...
-1
votes
0answers
32 views

Get the sum of Total Churn_MTD(Current+2015+<2015) [duplicate]

how to declare 8 variables and get the total Sum,as you can see from the picture i created the 3 store procedures but dont know how to do the ...
0
votes
1answer
29 views

Why running queries one by one yields correct results, but running lots of them in one chunk doesn't?

Let's say I create a simple table and a stored function to populate it: CREATE TABLE t ( id INT PRIMARY KEY, val VARCHAR(255) )// CREATE FUNCTION populateIfNotAlreadyIn (idArg INT, valArg ...
2
votes
1answer
52 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
0
votes
1answer
216 views

Access denied to user@localhost

As a root user i did this: GRANT DELETE, SELECT, INSERT, UPDATE, EXECUTE ON dbName.* TO 'user'@'localhost' IDENTIFIED BY 'apassword'; In 'dbName' i created a procedure 'proc_name' with definer set to ...
26
votes
4answers
12k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
1
vote
1answer
113 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
-1
votes
1answer
24 views

How to execute system command in mysql stored procedure? [closed]

Here is the code, delimiter // create procedure trig () begin declare cmd varchar(50); set cmd = "sh /home/yogaraj/Documents/Maastry/firefox.sh"; system cmd; end // delimiter ; But I ...
1
vote
3answers
627 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
0
votes
2answers
4k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
4
votes
1answer
31 views

Is there an alternative to “Create or replace procedure” in MySQL?

Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the ...
1
vote
1answer
116 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
1
vote
1answer
226 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
0
votes
2answers
176 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
2
votes
1answer
165 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
1
vote
3answers
30 views

MySql cursor is failing to retrieve all results

I am importing data from one table(say old_table). I have made a table(say new_table) in another database and i am using mysql stored procedure to populate it. I need to iterate through each record in ...
1
vote
0answers
40 views

Stored procedure not running on the same database when using different login

I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. ...
1
vote
0answers
12 views

MYSQL to PIVOT tables [duplicate]

SELECT person.firstnames, person.surname, crm_question.label, crm_answer.value FROM crm_interaction JOIN person ON person.id = crm_interaction.person_id JOIN ...
1
vote
1answer
35 views

Stored Procedure Error

I am trying to create this procedure in MySQL and show the error mentioned below. CREATE DEFINER=`root`@`%` PROCEDURE `synctables`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL ...
6
votes
2answers
94 views

Raise error if BCP command fails to dump data into a file

Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error ...
0
votes
0answers
16 views

Is there an alternative to delete a procedure inside a procedure?

For my Bachelor Thesis I try to automate some recurring tasks in MySQL. One task consists of the deletion of a specific stored procedure. Normally I would create a stored procedure which takes one ...
0
votes
0answers
18 views

Syncing DB's between local and live server?

I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below. There are two ...
5
votes
1answer
3k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
3
votes
1answer
113 views

procedure is deleting data instead of inserting

I have a procedure as the following: create procedure MoveDailyData @date1 bigint as declare @datenow bigint set @datenow = datediff(S,'01/01/1970',getdate()) if ...
4
votes
2answers
476 views

MySQL query returns unexpected results inside a stored procedure

This is my stored procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT) NO SQL COMMENT 'check if user is valid. return ...
0
votes
0answers
10 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
1
vote
2answers
75 views

Problem executing multiple procedures [closed]

I'm really new to SQL Server development and I learn everyday. Today I encountered a problem while executing multiple stored procedures in a query. I saw only one of both start. After a long research ...
0
votes
0answers
11 views

Is it possible to create a Stored Procedure inside a Stored Procedure in MySQL? [duplicate]

For provenance aspects, I want to keep track of the changes of a stored procedure of a MySQL database. When I want to update a stored procedure, I want to save the old procedure definition in a ...
2
votes
0answers
33 views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...