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
155 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
1answer
196 views

Simultaneous READ COMMITTED transactions updates a select for update columns

I have a READ COMMITTED transaction in which I am trying to update a row and increment a column value till it reaches a bucket value contained in another column. Please see the code below CREATE ...
0
votes
1answer
94 views

Working with variables in stored proc

I want to create a meta table on the fly using a stored pdocedure and passing a table prefix as "in" parameter and then build the final table name somehow. My code looks like this: SET @table_ = ...
0
votes
1answer
105 views

How can I limit a user to only be able to run procs in a schema, which access another db?

In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything ...
0
votes
1answer
98 views

ORA-01722 Error when running stored procedure

I am trying to run a stored procedure however when I run it with an a valid Number it gives me an ORA-01722 error. I checked to make sure that the columns in the where clause where the parameter is ...
0
votes
1answer
3k views

SQL Server : execute stored procedure from another stored procedure

I have two stored procedures. Proc1 executes a SQL statement and results a data table as a result set. I need to use the datatable in a select SQL that resides in proc2 as a datasource. like, ...
0
votes
1answer
91 views

Call a stored procedure for every select result

I have an stored procedure to delete from a long series of tables. It uses one main ID as parameter. I have to delete a bunch of rows based on a view that show that lot of tables. I want to apply the ...
0
votes
2answers
108 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
407 views

“Incorrect Syntax near S” in Stored Procedure [closed]

I'm creating a SQL Server stored procedure and getting error Incorrect Syntax near S I'm not able to find the exact reason of the error. CREATE PROCEDURE RPT_IVR_CALL_SUMMARY ...
0
votes
1answer
859 views

Need help with my MySQL PROCEDURE #1193 - Unknown system variable

I've this simple table: CREATE TABLE IF NOT EXISTS `testing` ( `testing_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `meter` INT NOT NULL, `price` DECIMAL(12,2) NOT NULL, ...
0
votes
1answer
1k views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
0
votes
1answer
177 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
2answers
706 views

Sql Server Stored procedure exicution taken long time

I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and ...
0
votes
1answer
54 views

Improving lots of Left Joins ( SQL Server 2008 - procedure )

This query is running with 8 minutes to complete. I'm trying to help the programmer with this task. I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us. ...
0
votes
1answer
128 views

Modifying a result set in a Stored Procedure, then returning that modified set

I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the ...
0
votes
1answer
254 views

Using dynamic database name for DML in trigger

I am using SQL Server 2008 in my application. Here I need to do database operations between multiple databases. The complexity is database names are user defined except one masster database. I can ...
0
votes
1answer
214 views

How do I report progress of a long running transaction in MySQL?

I have this transaction in a stored procedure: ... START TRANSACTION; WHILE @Codes > 0 DO EXECUTE qry_rand_stmt; EXECUTE qry_insert_stmt; SET @Codes = @Codes - ROW_COUNT(); END ...
0
votes
1answer
151 views

Need help in putting the single quote and constant timestamp in plsql

Please help me in appending the Single quotes and constant timestamp to my query output. Given below is the expected output from my param file. Note: Query is executed through sqlplus Expected ...
0
votes
2answers
700 views

Reviewing Stored Procedure template for handling transaction?

I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction. I have a stored procedure to log error in catch block in a table. How can I improve my ...
0
votes
1answer
2k views

Stored procedure to compare two columns from different tables and make the insert

I have two tables: Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer) Timesheet with columns fld_id (integer), fld_allocated_time (integer), ...
0
votes
2answers
3k 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
27 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 = ...
0
votes
1answer
93 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
41 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, ...
0
votes
1answer
41 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 | ...
0
votes
1answer
460 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
1answer
792 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
1answer
139 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, ...
0
votes
1answer
313 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 ...
0
votes
1answer
2k 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 ...
0
votes
1answer
1k views

How to deal with async calls to a stored procedure that updates data

I have asynchronous calls from Entity Framework to a stored procedure. The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls. The ...
0
votes
1answer
96 views

Create SP for inserting data from a table to a table

I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the ...
0
votes
1answer
9k views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
1answer
122 views

Error while creating procedure

This is my procedure: DELIMITER $$ CREATE PROCEDURE sp_test_final6() BEGIN INSERT INTO ...
0
votes
1answer
240 views

Table Valued Parameter not pulling the correct data for my sp?

http://sqlfiddle.com/#!3/b2643/5/1 http://sqlfiddle.com/#!3/b2643/5/0 This link above has the sample database tables I created on SQLfiddle. I have 3 main tables in my stored procedure: ...
0
votes
1answer
883 views

Stored Procedure with ROW instead of a list of parameters

i have a table with 15 columns, and trying to create a stored procedure in PL SQL that will only insert a row in a table using the INSERT statement. i would like to know if it is possible to declare ...
0
votes
1answer
208 views

How can I use package classes and call packages from Oracle11gr2 stored procedure in certain schema?

When a stored procedure begins with 4 create or replace procedure user0.import 5 is 6 f1 utl_file.file_type; compiler throws an error Error(6,6): PLS-00201: identifier 'UTL_FILE' must be ...
0
votes
1answer
299 views

SQL Server Stored Procedure - Does Raiserror affect performance

Does RAISERROR in a SQL Server stored procedure affect performance? Would I be better off just having a try-catch wrapper around the whole procedure and just catch the errors in the database access ...
0
votes
1answer
393 views

I am not able to create this DB2 SP. What is the issue with it? Not getting it [closed]

I am not able to create this DB2 SP. What is the issue with it? I get the following error: Category Timestamp Duration Message Line Position Error 18/12/2013 09:43:46 ...
0
votes
1answer
2k views

Move data from one table to another

How do I retrieve data from one MySQL table and store the data into another database table on the same server. I have tried this code but keep getting errors: CREATE PROCEDURE `ProfusionTest` () ...
0
votes
1answer
147 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
845 views

MySQL: all stored procedures deleted?

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be ...
0
votes
1answer
93 views

how to Update Value in local tabel when job Finished

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs ...
0
votes
2answers
516 views

MySQL stored procedure create database by month

I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to Query main database and move records by month to another database located on the same system. This ...
0
votes
1answer
754 views

Stored procedure not working

I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error: ERROR 1303 (2F003): Can't create a PROCEDURE from within another ...
0
votes
1answer
447 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
0
votes
1answer
761 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
1answer
46 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
1answer
68 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
0
votes
2answers
175 views

Understanding the use of Sql command parameters

I am using MySql database and I have a stored procedure to insert data, like this- CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`( IN id varchar(10), IN pwd varchar(10), IN fname ...