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

Why is my PL/SQL procedure so slow?

I have this sub-procedure in a fairly large program, and it is taking hundreds of times longer than comparable sub-procedures. Is there any way I could improve efficiency here? None of this ...
0
votes
0answers
24 views

msdb.dbo.sp_start_job got stuck in stored procedure

I have a stored procedure USP_A with logic like following: ...... BEGIN TRY exec dbo.usp_Log 'Start to run job job1' Exec @intErrorCode = msdb.dbo.sp_start_job 'job1' IF @intErrorCode <> 0 ...
0
votes
0answers
18 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
17 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
43 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
17 views

how to embed the ALTER statement into Stored Procedure? [closed]

How to embed the DDL statement in to procedure ? I need to add two column in exiting table? Does anyone known means please help me with clear example?
0
votes
0answers
25 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
29 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
53 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
40 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
29 views

Executing a script via Powershell possibility

Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it ...
0
votes
1answer
66 views

How to create a dummy Linked Server in SQL Server 2012

I have a database in two different production servers. I want to keep the schemas consistent between the two. One has jobs that call stored procedures that reference a Linked Server. The other does ...
0
votes
0answers
14 views

Read show create table in mysql stored procedure

How can I read the output of following query and assign to a stored procedure's variable? SHOW CREATE TABLE users; Or, is there is an information schema from where I can draw the full table ...
0
votes
0answers
26 views

How to reset @@IDENTITY value inside a Sybase SP

Is there any way to reset the @@IDENTITY value inside a Sybase SP? I'm writing a SP which involves a transaction, so everytime I do an insert, to check data was correctly inserted, I assign ...
0
votes
1answer
16 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
32 views

Which technologies should I consider using for creating a calculated view?

I use SQLServer, but I suppose this question is general enough for any type of database. Suppose I wish to build an application that allows people to rate geographic regions on where they would like ...
0
votes
0answers
6 views

MySQL log shows excessive meta queries on SP calls [duplicate]

We access MySQL via JDBC -- a prepare statement, parameters binding then invoking the actual stored procedure. It appears that every execute() has an associated "overhead" of 4 meta-calls (per MySQL ...
0
votes
2answers
87 views

Sql Agent Jobs executes too slow

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure USE [xxxx] GO /****** ...
0
votes
0answers
57 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
32 views

IF statement inside stored procedure, seems to cause locks by side effects

We are running a MySQL 5.5. server with READ-COMMITTED transaction level to store and process a large time series dataset. We have a large time-series-table, into which we write batch updates every ...
0
votes
0answers
49 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
2answers
78 views

Executing sp in ssms much faster than executing in job step

My stored procedure takes 23 secs in ssms but takes 4 minutes when exec via job. Can you please help me in finding out. Below is SP code ALTER procedure [ops].[trailers_sitting_builder_mod] --WITH ...
0
votes
1answer
48 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
60 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
100 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
74 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 ...
0
votes
0answers
47 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
0answers
58 views

Can I change “Category_Id” & “Current_execution_status” value in SQL Server 2012 through stored procedure?

I have created one job in SQL Server 2012 through SQL Server agent, and that job is not running due to some security privileges. When I am seeing through below SQL query to that my created job ...
0
votes
1answer
33 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
41 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
30 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
32 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
0answers
35 views

Report not pulling records

Stored procedure working fine. Procedure contains 5 parameters and 30 columns. I am using this stored procedure in my SSRS report. When I click preview, it is asking for parameter values I just ...
0
votes
0answers
27 views

When will store procedure/functions support result set as return type

I have noticed that MySql doesn't support result-set on a store procedure and functions, which means return a table. So I can't do things like CALL sp_my_store_procedure() ORDER BY id ASC; Or ...
0
votes
1answer
171 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
200 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
1answer
151 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
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
105 views

Insert/Update SQL Server Stored Procedure

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and ...
0
votes
0answers
34 views

Stored Procedure Issue in Simple Select Statement

Here is my Fiddle Here is My Table : CREATE TABLE IF NOT EXISTS `vehicle` ( `AutoID` int(11) NOT NULL AUTO_INCREMENT, `VehicleCode` varchar(20) NOT NULL, `NumberSeats` int(3) NOT NULL, ...
0
votes
1answer
199 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
2answers
117 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 ------------------------- ...
0
votes
1answer
160 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
109 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
2answers
1k views

Using concat inside a stored procedure parameters

I have a stored procedure and function that accepts a varchar as a parameter and i have this code where i am using concat function in the parameters of such function and stored procedure. DELIMITER ...
0
votes
1answer
33 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
32 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
1answer
3k views

Executing try catch block in mysql stored procedure

In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure . I want my select query to be in try block and if it executes correctly I want other query to ...
0
votes
0answers
179 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
0answers
69 views

How to pass ouput parameter values in mysql procedure

I am new with mysql and i getting issues at time of using output parameters in mysql procedure. Scenario: I have two tables MyProcedure code: DELIMITER $$ CREATE ...