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
3answers
659 views

How can I drop a stored procedure whose name is in a variable?

Given a variable that contains a stored procedure name: DECLARE @stored_procedure_name sysname; SET @stored_procedure_name = 'some_stored_procedure_name'; How can I drop the stored procedure?
4
votes
1answer
677 views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...
-1
votes
1answer
186 views

Syntax Error in MySQL Stored Procedure [closed]

I facing the issue in executing a SQL Stored Procedure and I got the error message as showing syntax error in my statement, but I couldn't find that, Stored Procedure -- Routine DDL -- Note: ...
1
vote
1answer
956 views

How to insert in table from remote stored procedure without creating a distributed transaction?

How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an ...
1
vote
2answers
333 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 ...
4
votes
6answers
10k views

Don't use a transaction for Stored Procedure

I have a stored procedure that runs a few commands. I don't want these commands to be wrapped in the transaction of the stored procedure. If the 4th command fails, I want the 1st, 2nd and 3rd ones to ...
0
votes
2answers
320 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 ...
1
vote
1answer
226 views

Inserting filtered data from one table to another

I need to find the number of employees who are not in all departments. I have one more table T2 in which same department column is present. Now I want to insert only those values of department which ...
1
vote
2answers
901 views

MySQL Workbench Alter Procedure

i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right ...
3
votes
1answer
3k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
-2
votes
1answer
222 views

Errors keeping stored procedures through restore from backup [closed]

We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...
14
votes
1answer
3k 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
665 views

create table with date name

I am writing a simple procedure to backup a table- CREATE PROCEDURE daily_backup() BEGIN DECLARE given_date VARCHAR(25); SET given_date = now(); CREATE TABLE given_date LIKE db1.table1; ...
2
votes
1answer
113 views

MySQL: Securing Access using Stored Procedures

I'm new to using MySQL. I'd like to use stored procedures to help secure my database when accessing it from a web service (i.e., grant only proc execute to the web service account). What permissions ...
5
votes
2answers
1k views

Frequency distributions segmented by a field

Some background: I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
0
votes
2answers
2k views

MySQL Continue Handler Problems inside a Function

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I ...
5
votes
6answers
819 views

Find procedures that haven't been called in <n> days

We are deleting old stored procedures and tables. How can I know what procedures haven't been called recently? dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return ...
1
vote
1answer
85 views

Stored Procedure Not working as Expected

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row. DELIMITER // CREATE PROCEDURE search() BEGIN SELECT * FROM ...
3
votes
1answer
449 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
0
votes
1answer
262 views

Stored procedure not working

I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error: ERROR 1303 (2F003): Can't create a PROCEDURE from within another ...
1
vote
2answers
236 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 ...
3
votes
1answer
1k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
3
votes
1answer
48 views

SP with Encryption Read/Writes Stats

If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
0
votes
0answers
64 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
1
vote
1answer
290 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 ...
-2
votes
1answer
385 views

Use select query in stored procedure

I have some questions about stored procedures. Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast or if ...
3
votes
3answers
3k views

How can I search the full text of a stored procedure for a value?

I use the following script to search the text of all stored procedures when I want to find specific values. SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION ...
0
votes
0answers
218 views

DBA_SCHEDULER_Job is not giving any result

I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering. select job_name,status from dba_scheduler_jobs; ...
1
vote
2answers
932 views

Same Parameter in MySQL Stored Procedure

How do I cache dynamic query from store procedure? Right now I have created my store procedure like this : CREATE PROCEDURE usp_MyProcedure ( IN UserID INT, .... ) BEGIN SET @sqlQuery = ...
3
votes
0answers
241 views

SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows

So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part: ALTER PROCEDURE ...
2
votes
2answers
546 views

The multi-part identifier “xxx” could not be bound

The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external); HAVING (SELECT COUNT(*) FROM ( SELECT * FROM ...
3
votes
3answers
4k views

How to create a mysql stored procedure through linux terminal

In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures. I understood the command delimiter for only the mysql client(client side ...
0
votes
1answer
199 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
1
vote
1answer
274 views

Trigger not firing when deleting a record from stored procedure MySql

Bare with me, as I'm fairly new to stored procedures / triggers. I have the following Stored Procedure: DELIMITER $$ CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
1
vote
1answer
832 views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
2answers
625 views

From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0. Now I am using features of SQL Server like: Common Table Expressions (I have a table ...
0
votes
1answer
252 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
2
votes
2answers
936 views

Executing a table type parameter stored procedure or function within a select clause

Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
0
votes
2answers
31 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 ...
-2
votes
1answer
35 views

Following procedure generates an error and I cannot find the reason [closed]

create or replace procedure buyunits is mnav number; mpno varchar2(100); mtransactioncharge number; mservicecharge number; maccno number; cursor ecursor is select accno from sipholder where ...
1
vote
1answer
731 views

MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host

We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication. "'Column count of mysql.proc is ...
0
votes
1answer
114 views

Different Results between stored proc and udf

I am getting different results between my stored proc and my SVF. The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, ...
5
votes
1answer
160 views

Best practice for copying tables from server to server

Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB) ...
1
vote
2answers
2k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
1
vote
2answers
459 views

PostgreSQL stored procedure which can return one of two custom rowtypes

I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this: CREATE OR REPLACE FUNCTION ...
3
votes
1answer
307 views

PLSQL : DBMS Jobs - parallelization?

I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs? for rec in select column from table1 loop execute stored_procedure( rec.column ); end ...
3
votes
1answer
957 views

Is it possible to use Oracle XMLTABLE and %type column type selector?

I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE. I want to know how can i specify a field type according to a table column type when passing ...
0
votes
0answers
326 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
2answers
3k views

How to Drop Tables using a variable in SQL Server?

DETAILS: I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table. WHY?: In my Access Database I am running a ...
1
vote
1answer
6k 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> ...