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
1answer
201 views

How to call stored procedure inside trigger?

I have made stored procedure that will update product quantity as per product id. That works fine for me. Following is code : DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE ...
5
votes
2answers
229 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
0
votes
1answer
96 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 ...
1
vote
1answer
293 views

can I Run a Stored Procedure across all SQL servers and return 1 html report

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 ...
0
votes
1answer
115 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
171 views

Memory is not released after the execution stored procedure followed by a process end

I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after ...
0
votes
1answer
532 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` () ...
4
votes
1answer
1k views

Move values from one table to another one via stored procedure in sql server 2008

I have two tables Login (userid, username, password) audit_login (userid, Transaction_Date, Status (success or failed) Now I want create a stored procedure in which, if anyone wants to login then ...
1
vote
1answer
2k views

Stored Procedure Performance using Temporary Tables

I have the following Stored Procedure I would like to improve the performance of DELIMITER $$ CREATE PROCEDURE NEXT_UNIT_OF_WORK ( IN batchId INT, IN size INT ) BEGIN start transaction; ...
1
vote
0answers
106 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
0
votes
2answers
137 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
0answers
105 views

db2 cannot execute C-function for Reason code 4

I've implemented a function as C code for db2. The library name is called libmyfoo.so and is stored id /usr/local/lib as declared in the External declaration. When I try to execute this function I get ...
1
vote
2answers
26 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
155 views

How to run stored procedure in another database on the same server using a different login?

I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined. I have a stored procedure in A, running as ua, that needs to run a stored ...
0
votes
2answers
757 views

Grant permission to execute stored procedure without granting access to underlying table - Oracle

I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else. I see from here how to add execute permissions ...
0
votes
3answers
559 views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
1
vote
3answers
245 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
0
votes
0answers
172 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
0
votes
1answer
3k views

Setting user permissions for different SQL Server schemas

I need to limit access to a particular user, but they still need to be able to see the data in tables owned by dbo. I'm trying to do the following: dbo schema functions as it would normally, has ...
5
votes
1answer
95 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
1
vote
1answer
143 views

How to improve performance of SP with many optional filtering parameters?

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem ...
1
vote
1answer
230 views

Cannot grant execute to stored procedure

When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below: Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5 Arithmetic overflow error ...
0
votes
2answers
76 views

Stored Procedure - MySQL

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to ...
0
votes
1answer
116 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
1answer
51 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
0answers
241 views

Does Netezza have functionality that is equivalent to a trigger?

Does Netezza have a feature that is similar to a trigger in SQL Server or Oracle? Right now we are using Netezza as a data warehousing database, but we would like to perform some audits and ...
2
votes
3answers
696 views

Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

After reading 'Defining an SQL procedure' section of DB2 UDB for iSeries SQL Programming Concepts V5R2 and 'Returning result sets from stored procedures' section of i5/OS Information Center, Version 5 ...
2
votes
1answer
406 views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
0
votes
1answer
223 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
880 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 ...
0
votes
0answers
132 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
0
votes
1answer
95 views

Procedure and Automation in Mysql

i have a table with these columns and the values are coming in these tables from a csv file at a regular interval of n minutes ( by cron the load infile command) : ID (PK) status first ...
0
votes
0answers
40 views

Does MySQL have built in support for procedural programming languages made in Python, PHP, Java, etc?

If I understand correctly, Postgresql has built in support so you can write procedures in different languages. But when I look at MySQL I only see a native language, it's own thing. Does MySQL have ...
-1
votes
1answer
30 views

prepare stmt is not working

set @sqlt = concat("select", @rowname_date," from syslog_data.syslogs_index_1 "); prepare stmt from @sqlt; execute stmt; deallocate prepare stmt; gives me a eeror 1064 in the prepare statement? Any ...
0
votes
0answers
31 views

how to use information table's result in a select statement

SELECT COLUMN_NAME into columnname_date FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='syslog_data' AND `TABLE_NAME`='syslogs_index_1' AND ordinal_position = forder_date+2; When I ...
0
votes
0answers
37 views

Permissions different between test and live databases

We have 2 database Servers - Test and Live. We restore the live database back to test periodically. We have noticed that a SQL User has access to execute a stored proc on live but not on the test ...
0
votes
2answers
1k 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
1answer
553 views

MySQL possibility to create global routines (stored procedures and/or functions)

Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database. When I tried to create a routine from console (without prior ...
1
vote
0answers
41 views

Restore database error

I am trying to restore a database I get this error Restore FIlELIST is terminated abnormally I tried adding a space in logicalName but could not solve it.I am using the below mentioned stored ...
1
vote
0answers
968 views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
2
votes
1answer
1k views

Stored Procedure which accept xml file as input

<?xml version="1.0"?> <catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> ...
2
votes
0answers
90 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
1
vote
2answers
72 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, ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
0
votes
0answers
20 views

How can I prove that the following T-SQL is bad for performance? [duplicate]

I have recently inherited a codebase with a large amount of stored procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
1
vote
2answers
4k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
1
vote
1answer
223 views

Counting the number of calls of a stored procedure

I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data). How can I let a counter ...
4
votes
1answer
190 views

Plan cache memory: parameterized SQL vs stored procedures

In making a case to disallow parameterized SQL in my company's development environment the lead developer related a story about how the last time they used parameterized SQL the server had major ...
-3
votes
1answer
91 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
0
votes
1answer
85 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 ...