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

1
vote
1answer
810 views

How can I trigger this stored procedure on insert?

I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
1
vote
1answer
1k views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
1
vote
1answer
4k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
1
vote
2answers
2k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
1
vote
1answer
280 views

What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?

I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it. the system will have a asp.net UI, web services middle tier and SQL2005 back end. ...
1
vote
1answer
55 views

Setting query to run as a stored procedure and email the results

I have the following query: select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections' , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL ...
1
vote
1answer
38 views

Using NOCOUNT to Improve Procedure Performance

I am looking to improve the performance of a certain procedure and I wanted to begin with the insertion of the SET NOCOUNT ON. I've read a couple of articles on the subject: Aaron Bertrand SET ...
1
vote
1answer
72 views

Unable to update table after creating an After update trigger

I have a table like this /*Table: pageprivilege*/ ------------------------ /*Column Information*/ ---------------------- FIELD TYPE COLLATION NULL KEY DEFAULT Extra ...
1
vote
3answers
374 views

How to store a query result into a variable? SQL Server 2008

I have two tables and I want to store the result query of my stored procedure into a variable. UserTable: UserID int primary key identity CredentialsID int AccountType nvarchar(15) ModifiedDate date ...
1
vote
1answer
58 views

Executed a Stored Procedure in the Production Server and the following warning message is shown [closed]

I executed a stored procedure on the production server; and though it shows that query has been successfully completed it doesn't allow me to close SQL Server Management Studio and the below warning ...
1
vote
2answers
136 views

Is attaching a database on the same server under a different database name possible?

I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server. ...
1
vote
1answer
257 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 ...
1
vote
1answer
514 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
1k 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 ...
1
vote
2answers
211 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
1answer
635 views

Table Variable and Database Scope

I am a junior DBA, and I am still confused by database scope. Now I am learning about local/global temp table and table variables. I saw an example as following: declare @T1 as table ( col1 ...
1
vote
3answers
1k views

How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this ...
1
vote
2answers
292 views

SQL Server Login to access tables of three different database from single database

I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which ...
1
vote
1answer
289 views

Table variable not Recognized in stored procedure

The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. ...
1
vote
1answer
2k views

Stored Procedure to evaluate column if condition is true execute another stored procedure

I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case ...
1
vote
1answer
1k views

Is there anyway to make this stored procedure thread safe?

I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each ...
1
vote
1answer
293 views

MySQL: Why am I getting this warning?

I am calling the stored procedure below, and get this warning, is this warning a false positive or is my query unsafe? 1 queries executed, 1 success, 0 errors, 1 warnings Query: call ...
1
vote
1answer
289 views

Raise error from Stored PROC in SQL Server PDW

I need to do some validation inside a stored procedure before I continue processing but SQL Server PDW (SQL Server 2008 R2) does not support RAISEERROR inside stored procedures. Is there any other ...
1
vote
2answers
46 views

Oracle Stored Procedures / schema maintenace but still keep service online, how?

I got a service running on a classic 3 tier model: 2 web servers and 2 application servers connecting to a single Oracle database instance. Most of the business logic are written as stored procedures ...
1
vote
1answer
3k views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
1
vote
2answers
5k views

How to create a stored procedure with several delete in(select) statements?

Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the ...
1
vote
2answers
119 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
1
vote
2answers
485 views

How to call Oracle's stored procedure from DB2 stored procedure?

I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure. I have a scenario where a trigger on a DB2 table will invoke a DB2 ...
1
vote
2answers
535 views

Stored Procedure in MySQL

I am new to MySQL Stored Procedure, in my first attempt I got the following issues, Stored Procedure: Delimiter $$ create procedure accountstatus ( IN inmode varchar(27), IN ...
1
vote
1answer
531 views

Stored procedure and prepared statements

I have 2 stored procedures: CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500)) BEGIN IF (param_CRITERIA <> "") THEN SET @QUERY1 = concat('SELECT user_name,count(*) as count ...
1
vote
2answers
33 views

A query for a latest version of data

MySQL. Let there is a table with fields startdate and kind. Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
1
vote
2answers
7k views

How to Drop Tables using a variable in SQL Server?

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table. WHY?: In my Access Database I am running a ...
1
vote
1answer
2k views

Concatenation of setof type or setof record

I use Postgresql 9.1 with Ubuntu 12.04. In a plpgsql function I try to concatenate setof type returned from another function. the type pair_id_value in question is created with create type ...
1
vote
1answer
643 views

Cannot use python3 as stored procedure language in posgresql database

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
1
vote
1answer
81 views

stored procedure examples [closed]

I am wondering if there are examples of open source apps that are written in stored procedures? I read a lot about them in textbooks and would like to see how they are used in practice beyond ...
1
vote
2answers
280 views

MySQL: Adding Routines to a Slave

While setting up the MySQL replication, I took the mysqldump (without--opt --routinesoptions) unaware of the fact that in mysqldump, --routines it is NOT enabled by default. Questions Is there any ...
1
vote
2answers
3k views

“can't return a result set in the given context” error when calling stored procedure - differences between mysql 5.0 and 5.5?

I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table) When I'am calling CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent'); ...
1
vote
1answer
212 views

MySQL user defined rollback procedure

I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more ...
1
vote
1answer
220 views

Binding errors with dependent stored procedures

We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
1
vote
1answer
1k views

Another BCP Export [Fail]ure

All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs DECLARE ...
1
vote
1answer
1k views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
1answer
466 views

use of signal for insert statement in mysql stored procedure

I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure: DROP PROCEDURE IF EXISTS `spItem`$$ CREATE PROCEDURE `spItem`( IN ...
1
vote
1answer
299 views

Oracle, Stored and External procedures

Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures. Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...
1
vote
1answer
2k views

What is the Best Way to detect if server is a Master inside a Stored Procedure?

I'm want to create a stored procedure/event to do some maintenance tasks. My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be ...
1
vote
1answer
6k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
1
vote
1answer
116 views

Most efficient way to insert rows into a temp table in a stored procedure

I am working in SQL Server 2008. I have a stored procedure with 5 queries inside of it that select rows from very large fact tables (on the order of millions of rows) into another table (call it ...
1
vote
1answer
51 views

Alter of stored procedure and execution performance

I would like to understand how an alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a ...
1
vote
1answer
34 views

Is there another/better way to create and maintain large amounts of procedures?

So I've decided to take a step back, and scrutinize the way I implement procedures on my databases. I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a ...
1
vote
2answers
134 views

How to update a multiple rows all at once with a procedure.?

Im trying to do a procedure to update an (for now) empty column recently added. In this new column, i will put the values of the result obtained by a join query. To explain it better, i have this ...
1
vote
2answers
730 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 ...