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
123 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
329 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
6k 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
2answers
949 views

Stored procedure big where clause or multiple procedures

Is it better to have one big query or N small ones? For example to select all the customers with multiple parameters: select id, name, firstname, city, birthday, ... from ...
1
vote
1answer
67 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
218 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
494 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
704 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
2answers
307 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
1
vote
3answers
3k 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
418 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
395 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
4k 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
2k 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
485 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
378 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
63 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
5k 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
7k 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
592 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
741 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
668 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
35 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
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
2answers
370 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
222 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
256 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
2k 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
516 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
357 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
7k 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
26 views

MySQL -> Loop through a table, running a stored procedure on each entry

I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books. I figured out how to get the word count for each word ...
1
vote
1answer
40 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
1
vote
1answer
61 views

Oracle: Query gives different answer when running from procedure

I guess my problem is somehow related with grant permissions. I have an "ADMIN" user, I grant it "dba" permissions like this: grant dba to admin Now in the "ADMIN" schema I have 2 tables: The ...
1
vote
1answer
31 views

SQL Server - Analyzing Multiple Stored Procedure Statistics

I'm trying to analyze 1000+ stored procedures and I need to find out the: Elapsed Time in Sec (AVG based on # of iterations) CPU (AVG based on # of iterations) Logical Reads At first I'm using SQL ...
1
vote
1answer
45 views

How to rebind a package inside a Stored Procedure (DB2)

I have a few Stored Procedures for my application (DB2 V 9.7.3). I need to rebind the packages corresponding to the Stored Procedures. I wish to get having them recompiled inside a Stored Procedure by ...
1
vote
1answer
51 views

Best way of order processing

I would like to write a procedure for order processing Single Order can have multiple items, how can I process the order ? Consider below JSON is an example: { "Order": { ...
1
vote
1answer
51 views

Stored Procedure Error

I am trying to create this procedure in MySQL and show the error mentioned below. CREATE DEFINER=`root`@`%` PROCEDURE `synctables`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL ...
1
vote
3answers
34 views

MySql cursor is failing to retrieve all results

I am importing data from one table(say old_table). I have made a table(say new_table) in another database and i am using mysql stored procedure to populate it. I need to iterate through each record in ...
1
vote
1answer
55 views

Stored Procedures execution permissions

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example: create procedure Database_Deployment_Proc -- This ...
1
vote
1answer
57 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
1
vote
1answer
67 views

Postgres : Using WITH … AS insert into to return non-inserted column

I'm creating an import job which uploads to a temporary table and then splits the data into two separate tables, joined by a key. What I'm doing is more complicated than the following but this simple ...
1
vote
1answer
228 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
206 views

msdb.dbo.sp_start_job got stuck in stored procedure

I have a stored procedure USP_A with logic like following: ...... BEGIN TRY exec dbo.usp_Log 'Start to run job job1' Exec @intErrorCode = msdb.dbo.sp_start_job 'job1' IF @intErrorCode <> 0 ...
1
vote
1answer
37 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
483 views

How to create a dummy Linked Server in SQL Server 2012

I have a database in two different production servers. I want to keep the schemas consistent between the two. One has jobs that call stored procedures that reference a Linked Server. The other does ...
1
vote
2answers
829 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
3k 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 ...