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
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
57 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
133 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
420 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
98 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
51 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
146 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
84 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
41 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
134 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
175 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
108 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
36 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
200 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
120 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
279 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
66 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
46 views

Statement ignored - PL/SQL Procedure

create or replace PROCEDURE UNIQUE_MATCH ( PHYSICIANID IN NUMBER , FNAME IN VARCHAR2 , LNAME IN VARCHAR2 , POSTALCODE IN VARCHAR2 , SOURCETABLE IN VARCHAR2 ) AS ALREADY_HAS NUMBER; BEGIN ...
0
votes
0answers
136 views

MySql stored procedures are not atomic (JDBC)?

I have a stored procedure with one UPDATE and one INSERT in its body. I call the SP with a single executeQuery(...) wrapped within a try-with-resources. The DB connection is set (by default) to ...
0
votes
0answers
199 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
0answers
31 views

How to Create a Recommended Test Planning in SQL Server

Goal: Make lots of different test for a specific stored procedure named 'sp_test' in order to review if you are retrieving the same data based on data input. Purpose: If you have made lots of ...
0
votes
1answer
68 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
167 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
0answers
196 views

Data Mock up for my table

I want to mock up data for a table. So I need to generate a million sample records for the table? Table structure is like this: CREATE TABLE Employees( EmployeeID int NOT NULL, FirstName ...
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
77 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
214 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
98 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
213 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
349 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 ...
0
votes
1answer
257 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 ...
0
votes
0answers
86 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
0
votes
1answer
71 views

“show procedure status” displaying the non existing databases stored procedures in mysql server

i have taken a mysql server backup using mysqldump and imported the backup file in another mysql server.Import failed because of disk full issue. Only 11 databases are created out of 15 . when i issue ...
0
votes
1answer
79 views

No records in Mysql.func table even though there are stored functions in server

When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . ...
0
votes
0answers
48 views

MySQL: routines removed but still in information_schema

I have a MySQL server with a lot of stored procedures (around 150). When I tried to list the routines with PHPMyAdmin it requires a lot (around 2-3 minutes) in order to load all of them. So I started ...
0
votes
1answer
94 views

Is there any way to perform atomic updates of stored procedures in MySQL?

I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588). In our project ...
0
votes
2answers
2k 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 ...
0
votes
0answers
415 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0
votes
0answers
22 views

i always get o for my result.why is it so?

CREATE DEFINER=`root`@`localhost` PROCEDURE `roomAvailbility`( IN checkindate date, IN checkoutdate date, out roomtype char(20)) BEGIN select count(*) INTO roomtype from room_1401557b ...
0
votes
1answer
478 views

create multi valued variable inside mysql stored procedure

I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement. CREATE PROCEDURE ...
0
votes
1answer
291 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
1answer
364 views

Execute grant for MySQL stored procedure revoked when stored procedure is altered

I have created the following stored procedure in a MySQL database (MySQL 5.5). DELIMITER $$ CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`() BEGIN "code here" END$$ ...
0
votes
1answer
46 views

Call to Function in Oracle says it's not declared

When I call the next stored-function in oracle: CREATE OR REPLACE FUNCTION SOLICITAR_DISPONIBILIDAD (parqueo IN NUMBER, semestre IN NUMBER, año IN NUMBER, jornada IN NUMBER) RETURN INTEGER IS ...
0
votes
0answers
53 views

User or Role is Missing

I'm using a stored procedure in oracle 11g r2 but when I call it from java it says that I'm missing the user or role name when it executes this sql sentence: no_dml := 'CREATE USER ' || no_carne || ' ...
0
votes
0answers
504 views

Service Broker received an error message on this conversation

I will first have to say, I am still trying to wrap my brain around all the components of the Service Broker and how they work together, so it's very likely I'm missing something obvious... it's just ...
0
votes
1answer
169 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
0answers
35 views

Replication overview

My boss wants me to elaborate a comprehensive report about synchronisation/replication. I know that replication can be done with the DBMS (we are using SQL Server 2012), but he wants something manual ...
0
votes
1answer
427 views

reading input parameter values in stored procedures

I'm using the current version of MariaDB 10.0.12 . I created a stored procedure as specified in the accepted answer for the following question: ...