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
3answers
3k 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
3k views

How to check parameter value existence in a procedure?

The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking: ...
2
votes
2answers
4k views

Event Scheduler: No data - zero rows fetched, selected, or processed?

MySQL 5.5.28 The error log keeps getting this error: 121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r ows fetched, selected, or ...
2
votes
1answer
177 views

How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare: I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck. I have ...
2
votes
2answers
1k views

Error on Oracle calling external procedures

I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it. the C source(libcprog.c) is like this: int exec_hmmftg ...
2
votes
1answer
813 views

Stored Procedure doesn't show result in SQL Server

I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
2
votes
2answers
6k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
1answer
285 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. ...
2
votes
1answer
52 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
2
votes
1answer
43 views

Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

MySQL does not support declaring constants which can be used between many stored procedures. For me, it seems that a deterministic no sql function can be used as an alternative for a constant. ...
2
votes
2answers
4k views

sp_send_dbmail stored procedure send with attachment

I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the ...
2
votes
1answer
85 views

MySQL stored procedure user privilege

I have a query regarding user permission on a stored procedure. If a user that does not have permissions to create tables in a database, but it does have permissions to create and execute stored ...
2
votes
1answer
514 views

Stored procedure with dynamic sql. Is it isolation safe by default?

I would appreciate confirmation on the answer I found digging through T-SQL's documentation. I have a stored procedure that references a view twice. Once in the procedure and then a second time when ...
2
votes
1answer
2k 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 ...
2
votes
1answer
477 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 ...
2
votes
1answer
6k 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
31 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 ...
2
votes
1answer
148 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 ...
2
votes
1answer
3k views

“Use Database” command inside a stored procedure

I would like to dynamically use a database inside a stored procedure but the use database_name command is not supported inside procedures. What alternatives are there?
2
votes
1answer
69 views

Is it possible to determine what part of a stored procedure is currently executing?

Say I have a stored procedure like this (assume that the table schemas are fine): CREATE PROCEDURE p_MyProc AS INSERT MyTable SELECT Col1 FROM Table1 INSERT MyTable SELECT Col2 FROM Table2 INSERT ...
2
votes
1answer
2k views

How to schedule a weekly backup of a database only if it has been changed since last backup

I´m new to SQL Server administration. I´d like to create automatically backup database every Sunday night, but only if there was any change in previous week (if not, backup is unnecessary). Is there ...
2
votes
1answer
217 views

Why evaluates “IS NULL” in SSMS interactive mode to “TRUE” but the same statement being executed in a SP to “FALSE”? [closed]

Given DECLARE @IN__CUST_NO INT DECLARE @OUT__USER_ID INT SET @IN__CUST_NO = 1000006660 CASE 1: IS NULL evaluates TRUE Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy ...
2
votes
1answer
487 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
2
votes
1answer
51 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
2
votes
2answers
179 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
2
votes
1answer
210 views

Encryption/Decryption with StoredProcedure/View

I'm working on a project that uses EntityFramework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first ...
2
votes
1answer
130 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
2
votes
1answer
703 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 ...
2
votes
1answer
120 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
2
votes
2answers
2k views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
2
votes
1answer
143 views

How can I pass object to procedure from trigger? [closed]

I am using mysql 5.6. After insert or update, inside the trigger I am calling procedure, So how can i pass NEW and OLD object from trigger to procedure ?
2
votes
1answer
3k views

How to update a table with a stored procedure with XML parameter in SQL 2008?

I have the following Stored Procedure query. This query is incomplete. I need help completing this query. Problem: In this SP, I am receiving a well-formed XML string into the SP. My goal is to ...
2
votes
1answer
886 views

How to ignore sql errors in stored procedure ? (Not handle)

I have a list of records I want to process and the cursor iterates through them from a table. The procedure just exits when it hits an insert statement and it fails. Is there a way I can ignore the ...
2
votes
1answer
451 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
2
votes
1answer
2k 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 ...
2
votes
1answer
2k views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
2
votes
1answer
274 views

Query returning unused stored procedures

When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries. When I run the following query, the results show the ...
2
votes
1answer
4k views

Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing ...
2
votes
1answer
218 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
2
votes
1answer
211 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 ...
2
votes
1answer
2k 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
2answers
147 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, ...
2
votes
1answer
14k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...
2
votes
1answer
85 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 ...
2
votes
1answer
2k views

Oracle Packages, Procedures and Functions Not Respecting Roles

When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
2
votes
2answers
976 views

How do I save functions to individual files in PostgreSQL?

I maintain a legacy application that uses a PostgreSQL database. The application is heavily dependent on stored procedures (aka functions). I want to save these functions to files named after the ...
2
votes
1answer
3k views

How to modify a stored procedure using SQLCMD for MS SQL?

Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio. use testbase go create procedure testtable_pricesmaller @pricelimit money as select * from ...
2
votes
1answer
709 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
2
votes
1answer
310 views

stored procedure times out while subsequent runs take 1/6 the time

So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other ...