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
48 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
46 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
70 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
44 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
212 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
105 views

Procedure permission issues with a SQL state query

We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side. ...
0
votes
1answer
790 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
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 = ...
0
votes
1answer
33 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
35 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
29 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
112 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
234 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
60 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
173 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
570 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
400 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
2answers
434 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
0
votes
1answer
71 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
2answers
959 views

create a stored procedure if it doesnt exist using a ddl in db2

My requirements are, I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it. What I tried is, IF EXISTS (select procname into ...
0
votes
1answer
598 views

“Access denied for user” error when creating a procedure

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK ...
0
votes
1answer
140 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
250 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
133 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
172 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
227 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
1k 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
473 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
88 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
357 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
377 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
233 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
454 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
42 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
40 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
7k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
0
votes
2answers
156 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 ...
0
votes
1answer
559 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
2answers
81 views

SQL Server Stored Procs not parameterized, leading to high compilations?

We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL. However, I noticed that in SQL Profiler our calls look like: exec cid.dbo.uspCIDLog ...
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
421 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
5 views

Executing stored procedures asynchronously using tasks in SSIS

I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using ...
0
votes
0answers
10 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
16 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
30 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
22 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
17 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
38 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
20 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 ...