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

Recursive Conditional trigger for insert in table MYSQL

user[ user_id, parent_user_id] user_id parent_user_id 1 null 2 1 3 1 4 1 5 1 user_assigned_role [user_assigned_role_id, role_id, ...
1
vote
3answers
685 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 ...
0
votes
2answers
218 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
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 ...
0
votes
1answer
1k views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
-4
votes
0answers
26 views

Get the output of a stored procedure into a table [on hold]

Can anyone help me with a table that takes the output from a stored procedure. It should not be a temp table or table valued parameter. I am able to insert values to the table, but when I use select ...
8
votes
2answers
409 views

Create a “INTO” table with primary key

maybe for this community my problem is easy, but for me (a simple java programmer) it's a BIG problem. I have a Big DB with more and more data. So, the external db admin had create a job that show me ...
2
votes
1answer
24 views

Logical condition when declaring handler procedure

I have a procedure in MySQL that executes a handler for SQLEXCEPTION to read the errno code and insert it to a table. DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 ...
2
votes
1answer
261 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
5
votes
1answer
394 views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
0
votes
0answers
25 views

Should I use mysql stored procedure or regular inline statement?

So here is the problem: I have been currently working on a project that would be handling millions of data and thousands of records would be inserted as well as retrieved at the same time. Before ...
1
vote
1answer
32 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
3
votes
1answer
80 views

Most useful stored procedures for a new DBA? [closed]

I am a new DBA working with several more experienced DBA's. When I am scratching my head one of them says "oh just run SP_xx_xx", and sure enough that begins the path to solution. Of course they all ...
0
votes
1answer
29 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
0
votes
2answers
281 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
1
vote
1answer
179 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
1
vote
1answer
157 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
7
votes
2answers
258 views

Same code in multiple stored procedures

I recently joined a company, and I just noticed that many of the stored procedures have the same section of code repeated throughout. I noticed because I was tasked with changing a small section of ...
1
vote
2answers
171 views

ALTER DATABASE script preventing restore of MySQL database in another server

While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines. /*!50003 DROP FUNCTION IF EXISTS `f_my_function` */; ALTER ...
2
votes
1answer
41 views

howto get the current line number from an executing stored procedure

Is there a function or keyword that will allow me to get the current line number in a stored procedure? I know there's an undocumented LineNo function that allows you to set the line number, and ...
1
vote
2answers
922 views

AWS RDS MSSQL Extremely Slow compared to EC2 with MSSQL

Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format. ...
1
vote
1answer
346 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
-2
votes
1answer
71 views

Password protected Database Engine with Server features and stored procedure [closed]

I like MS Access password protect feature but it does not useful for server based software like as SQLite which has password protect but not useful for server. SQL Server is one of the best DB engine ...
1
vote
1answer
29 views

MySQL -> Loop through a table, running a stored procedure on each entry

I have a database with 'books' (short stories for children) and it would be extremely informative to have word counts of each word in the books. I figured out how to get the word count for each word ...
2
votes
1answer
162 views

MySQL stored procedure updates twice

I have this stored procedure: DELIMITER $$ CREATE PROCEDURE updateTrendContentStats(IN mongoid VARCHAR(255),IN numericid INT,IN points INT,IN ctype INT,IN activity INT,IN created INT) BEGIN DECLARE ...
1
vote
1answer
42 views

Create a stored-procedure to insert multiple rows on a junction table and other tables

I am new to Databases and Design and needs some help on a INSERT issue. I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need ...
5
votes
2answers
26k views

Granting Execute Permissions on Stored Procedures, Functions and Views

I am using a user which has read permission only. It basically has permission to Connect Execute However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
-3
votes
0answers
18 views

Object dependencies of stored procedure in SQL Server 2008

I need to have object dependencies of stored procedure in same sequence that objects are called in the procedure Can we export these dependencies to an Excel file?
0
votes
2answers
2k views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
0
votes
2answers
46 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
0
votes
1answer
108 views

MySQL - using result of Procedure in Trigger to Insert in new table

I'm guessing I'm doing something that's prohibited by MySQL, but can't see what. Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to ...
0
votes
1answer
294 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
0
votes
1answer
39 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
1
vote
2answers
23 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
0
votes
2answers
37 views

Is there a way to reference a column dynamically within a trigger?

I asked a similar question a few months back and I'm improving upon my solution! I have an Oracle database where I would like to trigger a procedure to do additional work on a column upon updating of ...
10
votes
6answers
38k 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 ...
11
votes
3answers
22k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
0
votes
0answers
39 views

Mulit-part identifier could be found - stored procedure

Hopefully I can get some sight into to what's going, Apologies for my lack of terminology and poor TSQL skills, I'm very much a developer that's inside an IDE and using ORM's. So I took the plunge to ...
0
votes
0answers
14 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
1
vote
2answers
392 views

Stored Procedure count and list the occurrence using like from another table

Actually this is my first procedure apart from basic "Hello world" procedure. I have two tables like this: cars users ------------------------- ...
3
votes
3answers
136 views

SQL Logins in db_owner role on database cannot create stored procedures

I started locking down permissions in our test environment by removing sysadmin permission from generic accounts. The first account, used by several developers, is a local SQL login. I gave the ...
4
votes
1answer
45 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
0
votes
1answer
133 views

Can I create a stored procedure in mysql using a txt file?

My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server: mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt The content of the .txt ...
4
votes
1answer
157 views

How can I speed up a stored procedure?

Here is an interview question I just had. I would like to know what a good answer to this would have been and what the different options would be. We have a stored procedure that is taking 26 ...
0
votes
2answers
6k views

How to return a result set from oracle procedure ?

How can i return a result set from a stored procedure in oracle ? suppose i want to return a table from a simple select statement such as this : Select * from tblTest I tried this but this seems ...
0
votes
0answers
12 views

sp_procoption seems to be failing on SQL 2005 instance

We have a SQL script that creates a Stored Procedure which is responsible for creating a SQL trace. On SQL Server 2008, the script runs fine, the SP gets created and the SP is configured to ...
0
votes
1answer
26 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
3
votes
1answer
65 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
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
1answer
34 views

Insufficient Prilivege for Function

I'm using a Function that I can't read, when I try to open it, I can't see the definition. I get following error Error : Insufficient privilege. Nevertheless, it is being executed by my app ...