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

best practice for 'staging' relation in stored procedure: use temp table, or view?

When I write stored procedures that take relation A and output relation Z, I like to create 'staging' relations along the way, so I can inspect the state of the data after each step in the programming ...
1
vote
0answers
39 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
75 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 ...
0
votes
1answer
46 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
55 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 ...
0
votes
1answer
43 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
52 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 ...
0
votes
0answers
40 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
2answers
65 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 ...
1
vote
1answer
135 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 ...
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
46 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
27 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
0answers
67 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
22 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
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
2
votes
2answers
85 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
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 ...
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
57 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
57 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
148 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 ...
-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 ...
-1
votes
1answer
37 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 ...
2
votes
1answer
81 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
29 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
160 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 ...
1
vote
1answer
130 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 ...
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
31 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 ...
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 ...
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 ...
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
151 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
41 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 ...
1
vote
1answer
76 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 ...
0
votes
1answer
56 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 ...
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
75 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?
2
votes
3answers
128 views

How to make stored procedures more readable without harming performance

I come from OOP background, and I need to write and maintain huge stored procedures. Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable ...
0
votes
1answer
43 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
91 views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
2
votes
1answer
57 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
159 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
56 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
145 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
80 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
93 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 ...