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

2
votes
3answers
859 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
0
votes
1answer
27 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
28 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
1k 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 ...
1
vote
1answer
115 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
2
votes
2answers
210 views

Select from stored procedure

Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute ...
1
vote
1answer
268 views

MySQL: IF statement check is a table exists

I run in the following issue and can't figure out how to resolve it. delimiter # create procedure pr1() begin declare v1 double default 0; start transaction; ...
0
votes
1answer
24 views

How can I get all rows of data not included in a TVP

I am trying to do the following: declare @domains DomainNames; declare @CompanyId int declare @CompanyName varchar(255); declare @deletedList DomainNames; set @CompanyId = 239; set @CompanyName = ...
1
vote
1answer
62 views

How can I get a local variable that's a list in a stored procedure?

I have some code in C# I'm trying to move to a stored procedure - Sql Azure does not like multiple calls. So everything needs to get moved to a proc. Here's the C# code: List<string> ...
0
votes
0answers
62 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
36 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
2answers
53 views

How can i branch based on a columns value in oracle?

I am trying to have a dynamic inner join..on in an Oracle database. For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for ...
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
2answers
304 views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...
1
vote
1answer
124 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
0
votes
1answer
89 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 ...
-1
votes
1answer
100 views

Very Slow MySql Stored Procedure

I'm working with a web app running on MySQL. It uses stored procedures for all database-related tasks. The original web app and the database were designed by another developer; I'm only maintaining it ...
0
votes
1answer
25 views

Is there a benefit to using FOR SHARE in this PostgreSql simple select?

In the following PgSql stored function: CREATE OR REPLACE FUNCTION get_offer_from_id( offer_id bigint) RETURNS json AS $$ DECLARE res "entity_data_result"; r "get_offer_data"; ...
0
votes
1answer
33 views

Using 2 order by in procedure [closed]

I need to use 2 order by in my procedure. When I use only the second order by is working and not the first one. What mistake am I doing? Here is my procedure BEGIN SELECT sd.ShopID, ...
3
votes
1answer
148 views

When to use With Recompile

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc.. Sometimes there are cases that it may help, but OPTION(RECOMPILE) ...
0
votes
2answers
51 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
-1
votes
1answer
38 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
0
votes
2answers
95 views

What do cursors do internally?

I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR... The client saw this SP ran for more than a day so, my manager assigned this to me. Upon searching, I used table ...
0
votes
1answer
27 views

I cant figure out the error probably with the date field in this mysql stored procedure

This is my empDetails table I am trying to create a stored procedure on. mysql> describe empDetails; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
1
vote
2answers
278 views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
0
votes
2answers
177 views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
0answers
32 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
85 views

How to retrieve stored procedures from raw data

I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't ...
0
votes
0answers
27 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
84 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 ...
1
vote
1answer
113 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` ...
0
votes
1answer
154 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
1answer
901 views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
1answer
187 views

How to reduce the run time of stored procedure by getting rid of cursors?

I have a task of reducing the run time of a stored procedure that runs for hours. Description of task: The cursor takes a set of values @Master and @Slave obtained from a query(5000+ rows), finds ...
0
votes
2answers
116 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
3answers
74 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 ...
1
vote
1answer
313 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
1
vote
1answer
583 views

How to write a procedure /job to kill all session running for more than 5mins in Oracle?

How to write a procedure /job to kill all session running for more than 5mins in Oracle? So, I query v$session and find out that for a particular user, program pair if the session has been active for ...
0
votes
1answer
213 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
2answers
127 views

Does anyone use modules in DB2 LUW?

Our environment is currently DB2 9.7 Fix Pack 4 on AIX. I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then ...
0
votes
1answer
50 views

RaiseError on SQL Server stored procedures

ALTER PROCEDURE sp1 as begin IF (@PersonId=0) RAISERROR(N'Error already raised. See previous errors.', 16, 1); break end I want something like the following. If no value in the variable, ...
5
votes
1answer
574 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
0
votes
1answer
155 views

MySQL: update field with “x hours xx minutes”using int from another field

We have two fields that hold time data totalTime (int): number of minutes (eg 243) totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes') We have a lot of different formats ...
3
votes
2answers
111 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
2
votes
1answer
41 views

MySQL Stored Procedure Requiring SUPER

I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user ...
0
votes
0answers
34 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
466 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 ...
1
vote
2answers
120 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
0answers
78 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
1answer
340 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...