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
0answers
29 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
2
votes
1answer
66 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
0
votes
1answer
35 views

Execute stored procedure in another server from sql agent job

I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2 select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]') Or EXEC ...
1
vote
1answer
185 views

SQL SERVER : CANNOT GRANT EXECUTE ON TO STORED PROCEDURE

When i try 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
1answer
41 views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
1
vote
2answers
2k 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
2answers
54 views

Stored Procedure Consolidation

I recently started working on a project using stored procedures from data vs. ORM's I haven't seen this project's procedure layout before and was wondering if it's ok / what would be better. Code ...
1
vote
2answers
165 views

Is it possible to define package procedure/function outside of package body block (for Oracle)?

Is it possible to define package procedure/function outside of package body block (for Oracle)? Simplified syntax for defining package body is: create or replace package body <package_name> IS ...
0
votes
1answer
41 views

Is this application creating stored procedures on the fly? [duplicate]

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue. When looking at *sys.dm_exec_sql_text*, I ...
0
votes
1answer
226 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 ...
2
votes
2answers
83 views

Filter Stored Procedure Result (Informix)

IBM Informix Dynamic Server Version 11.50.UC5XA. I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored ...
0
votes
1answer
87 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
2answers
259 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
1
vote
1answer
122 views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
0
votes
1answer
49 views

Rollback parent procedure on failure in MySQL

I have the following two procedures on my server: CREATE DEFINER=`someone`@`localhost` PROCEDURE `NewMagnet`( IN `nick` VARCHAR(32), IN `tth` CHAR(39), IN `name` TINYTEXT, IN ...
2
votes
2answers
64 views

How to Log Debug Information About a Stored Procedure

We have a group of fairly complex stored procedures that process a transaction queue on a pretty much round the clock schedule. Currently these scripts use a number of print statements to document ...
0
votes
0answers
37 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
2
votes
0answers
28 views

Why does returning a custom data type appear to cause a memory leak in my PL/Perl stored procedure?

I have recently encountered what appears to be a memory leak with a particular long-running postgres process, which runs a stored procedure. I am able to reproduce the same behavior with a very ...
1
vote
1answer
92 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 ...
0
votes
0answers
43 views

Execution time is different between normal query and query inside a stored procedure

I have following simplified query WITH Report1 AS ( SELECT SUM(t.X), SUM(t.Y) FROM Table1 t WHERE t.Date1 > :dateStart and t.Date1 < :dateFinish ) SELECT * FROM Report1 This query ...
0
votes
1answer
25 views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
1
vote
1answer
158 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
1
vote
0answers
60 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
0
votes
1answer
21 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 ...
-2
votes
1answer
88 views

Stored procedure syntax error [closed]

I am writing a procedure, independently the queries run fine with no errors.. But, when I run it as a procedure I get errors. Given below is my procedure create or replace PROCEDURE xyz(inuserid ...
0
votes
4answers
34 views

Oracle How to find Debug Compiled Stored Procedures/Functions

How can I get list of stored procedures/Function which are compiled for debug? I do not see relevant column in following tables. DBA_PROCEDURES DBA_OBJECTS
14
votes
1answer
853 views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
0
votes
2answers
69 views

Known vulnerabilities for when a guest executes a stored procedure on SQL Server

I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How ...
1
vote
1answer
126 views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
0
votes
0answers
38 views

Dynamic Query creation by looping through Query-Result?

after my first question regarding pivoting a table (here) got great answers I'm now struggling with how to adapt this to variable table sizes. I need something like a "FOR EACH IN" which creates a ...
2
votes
1answer
87 views

Do SPROCs have a “signature”, and how is that defined?

I have a stored procedure which is called by a third-party tool. I believe it to be using ODBC, but it is essentially a black box. The DBMS is Sql Server 2012. When the stored procedure is a simple ...
0
votes
1answer
52 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
3answers
56 views

Inner Join Table (Mapping table)

this is first time i am try mapping table in mysql i write this procedure but not work ? whats wrong ? DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`( ...
5
votes
1answer
147 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
-1
votes
1answer
35 views

delete in stored procedure [closed]

i am write this stored procedure to delete one row DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT) BEGIN DELETE FROM students WHERE ...
4
votes
1answer
2k views

Passing an array or record to a stored procedure in PostgreSQL

I have a task to pass arrays, records and in some cases array of records as a parameter to the stored procedures in PostgreSQL. Any ideas?
2
votes
1answer
78 views

How to generate time in milliseconds in PostgreSQL stored procedure?

I have been reading the Postgres documentation about timestamps, but I am still a little bit confused. I need to store moments in times, measured in milliseconds, between the current time and ...
0
votes
1answer
28 views

An SQL Server 2008 R2 based program fails when updating stored procedures?

I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure. Is there any place in SQL Server 2008 R2 / Windows where I could see an error message ...
1
vote
2answers
144 views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
2
votes
1answer
25 views

Running members of a job subset at different intervals

So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for ...
1
vote
0answers
29 views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
4
votes
1answer
128 views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
3
votes
1answer
37 views

Searching all Stored Procedures for an update of a specific column

I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a ...
0
votes
2answers
34 views

How to fill a tabe automatically with a procedure in MySQL

I have a very big table for users as below: CREATE TABLE `karbar` ( `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ...
1
vote
1answer
141 views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
0
votes
0answers
40 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
0
votes
1answer
55 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
1answer
74 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 ...
2
votes
1answer
64 views

PL/SQL doesnt return rows

Please help me debug this: Working: When I execute: set serveroutput on declare middle_name varchar2(30); begin middle_name := '0000000231'; for x in ( select ...
3
votes
1answer
74 views

Determine the currently executing statement in a long running PL/pgSQL stored procedure

I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?