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

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...
0
votes
1answer
422 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
0
votes
0answers
47 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
23 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
22 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
68 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
27 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
18 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
19 views

Procedure or function 'sp_InsertImages' expects parameter '@Img_path', which was not supplied [migrated]

i have passed img_path in my code nd procedure but it show me error please anyone help me. my procedure is : create proc sp_InsertImages ( @Img_path nvarchar(max), @product_id numeric(18,0), ...
0
votes
0answers
48 views

Optimising an SP - Left Join against several big tables

I have stumbled upon performance bottleneck due to LEFT JOIN against several big tables (approx. 6 million rows of data, consisting of 10 columns). Any suggestions on how to optimize this SP is ...
0
votes
0answers
26 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
21 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
39 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
29 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
22 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
23 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
1answer
57 views

MySQL Workbench not showing stored procedures that I have created

I have a simple stored procedure that I have just created. I have applied this script to my database This script shows up in the my list of stored procedure However, when I restart MySQL ...
0
votes
0answers
21 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
20 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
22 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
13 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
59 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
116 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
91 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
79 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
32 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
127 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
86 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
135 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
90 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
669 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
29 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
30 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
2k 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
121 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
51 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 ...
0
votes
0answers
13 views

Validate hierarchy of two tables

I have two tables in my mysql database: Questions question_id Answers answer_id parent_question_id child_question_id Basically, I'd like to pass in a question_id, and have all child ...
0
votes
1answer
105 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
33 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
0answers
35 views

MYSQL Stored Procedure Problem. Need Help

MySQL Stored procedure: Delimiter $$ DROP PROCEDURE IF EXISTS spGetCompanyNames $$ Create procedure spGetCompanyNames (IN Employer nvarchar(75), Out Employer_Namee nVarchar(75)) begin Select ...
0
votes
0answers
115 views

prevent sql injection inside stored procedure

I finally found a solution for the following question: reading input parameter values in stored procedures However I'm not sure, whether this solution is resistant to SQL injections. I'm using ...
0
votes
1answer
200 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: ...
0
votes
3answers
139 views

List all tables that a specific login has permissions [duplicate]

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
0
votes
1answer
396 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
0answers
37 views

Function performance issue

This one is puzzling me since yesterday and maybe someone could shed some light on this. We've got this table that records GPS data. We're currently doing some overloading tests to see how the ...
0
votes
0answers
550 views

Can a stored procedure OUTPUT parameter return a table as output?

I would like to know whether we can use a stored procedure's OUTPUT parameter to a return table as result set? I would appreciate any practical example using it OUTPUT parameters in stored ...
0
votes
0answers
107 views

set error message on out variable in mysql stored procedure

I have a stored procedure: fill_DATA (serial varchar(20), OUT oError) I am doing an Insert operation here. so what I want is to SET value of oError for any error message in case the query fails to ...
0
votes
0answers
38 views

how to insert into multiple columns from one column in sql [duplicate]

suppose i have a column called [SpendPlanned] and [SpendGME] with data corresponding to its monthid column in such a fashion below say in 'x' table: i have only two columns[SpendPlanned] and ...
0
votes
0answers
2k views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...