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
45 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
0
votes
1answer
22 views

Scripting execute permissions on stored procedure

My goal is to write a script which: Creates a stored procedure Grants execution permissions to a particular user on that new stored procedure On this SO question the recommendation is to add GRANT ...
0
votes
1answer
29 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
0answers
17 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 ...
0
votes
1answer
40 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
0answers
24 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
49 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 ...
0
votes
0answers
17 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 information_schema....
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 ...
0
votes
0answers
175 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to "...
0
votes
0answers
27 views

calling multiple procedures from my application Vs calling procedures inside another procedure

I have a scenario of saving user details into my database(MySQL 5.1); i have three tables for saving user details; user_data,user_contact,user_address, so if i insert one user data i have to check for ...
0
votes
0answers
49 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
0
votes
1answer
48 views

Slow query with JOIN

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN: JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange) Without ...
0
votes
0answers
65 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = '`tituloOferta`';...
0
votes
1answer
18 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
0
votes
0answers
29 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
69 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
0
votes
1answer
34 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...
0
votes
0answers
122 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
16 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
0
votes
0answers
90 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
0answers
121 views

Postgres asynchronous procedure execution?

I have an ETL script entirely contained within a Postgres 9.3 procedure. The script takes several hours to process, which is fine for our purposes. What I haven't been able to find out is if there ...
0
votes
0answers
34 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
0
votes
3answers
85 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...
0
votes
0answers
61 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
0
votes
1answer
152 views

Can I create a stored procedure in mysql using a txt file?

My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server: mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt The content of the .txt ...
0
votes
2answers
498 views

SQL Server : create table within stored procedure

I have a stored procedure that will be run by a user with public permissions. When operational, it will be called from an Excel spreadsheet and the results presented on a given tab. The procedure has ...
0
votes
0answers
106 views

Method to economically insert Bill of Materials BOM items into a Stock Table?

I’m new to the wonderful world of database design and I’m currently experimenting with an implementation for my little brother relating to Model Train Sets. [I will be using SQL Server 2012 / Asp.Net ...
0
votes
0answers
54 views

stored procedure to display error messages & success when a sp_refreshview is ran

How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" ...
0
votes
0answers
159 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
0
votes
0answers
88 views

call a stored procedure from sql window in PHPMyAdmin

I have a simple stored procedure that selects rows from a table. In phpMyAdmin, in the list of my procedures, if I click the Execute option, it executes properly, returning the desired rows, and ...
0
votes
1answer
42 views

How to send an email with embedded values

I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100. ...
0
votes
1answer
1k 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. ...
0
votes
0answers
146 views

How to generate a JSON file from stored procedure(SQL) result

I want to get the data from DB using stored procedures in the form of a JSON file. In simple words, my output should be a JSON file which should be the result of data in DB (collected by a stored ...
0
votes
0answers
192 views

How can I associate different not done continuation blocks with two cursors in the same scope?

If I have two cursors declared in the same block, how can I associate different not done continuation handler actions that are independent of one another and only execute with the cursor they're ...
0
votes
1answer
111 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
0
votes
0answers
38 views

Converting Legacy SQL Server 2005 Stored Procedure to Transaction based for parallelism [closed]

My task is to convert a legacy stored procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the stored procedure is designed ...
0
votes
0answers
214 views

optimizing paging in a stored procedure

I have the following SQL command which is built inside a stored procedure according to input parameters: SELECT DISTINCT * FROM ( SELECT DISTINCT TOP @displayLength * FROM ( ...
0
votes
0answers
134 views

Stored procedure return -1

Normally, when I execute a SP, it returns a value of zero (0), but I have a SP is returning a -1 and can't figure out why. Does the -1 indicate an error? If I copy all the SP code into a separate ...
0
votes
2answers
294 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
0answers
73 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
0
votes
0answers
213 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
0
votes
1answer
70 views

trouble creating a stored procedure to print out values for each day of week

I am having difficulty in understanding how to get this working correctly. I am trying to have create a stored procedure that is run automatically on a sunday and will get the data for the previous ...
0
votes
3answers
174 views

Merge MySQL tables without data repetition

My situation is this: I have several DIFFERENT tables in my MySQL database with the same name columns. Such tables can contain (or not) the same data. What I need to do is a kind of "merge" between ...
0
votes
1answer
17 views

Help with stored procedure and update table

I am trying to write a stored procedure that has parameters and updates a table with the supplied title and middleName for the person having the businessEnityID. Create Procedure sp_PersonEditor @...
0
votes
0answers
78 views

How can I replace the cursor in the below query?

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the ...
0
votes
0answers
224 views

Hide Inconsequential Result Sets From Stored Proc

How does one mask resultsets in multi-layered stored proc calls? Due to stored proc reuse, I have an issue where a top level stored procedure calls multiple stored procedures (directly) and in-tern ...
0
votes
1answer
105 views

SQL Server stored procedure to insert/ update which has date

The source which has date in dd-MMM-yy format , have to convert it yyyy-mm-dd hh:mm:ss:SS and insert/update into the Database using stored procedure.New to stored procedure I am managed to write a ...
0
votes
2answers
230 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 ...
0
votes
0answers
365 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...