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
1answer
3k views

Heterogeneous queries error using stored procedure

Stored procedure ALTER PROCEDURE Proc_DayShift AS insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SET ANSI_WARNINGS OFF ...
1
vote
1answer
143 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
1
vote
0answers
41 views

Stored procedure not running on the same database when using different login

I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. ...
1
vote
0answers
12 views

MYSQL to PIVOT tables [duplicate]

SELECT person.firstnames, person.surname, crm_question.label, crm_answer.value FROM crm_interaction JOIN person ON person.id = crm_interaction.person_id JOIN ...
1
vote
1answer
82 views

all tables used by a stored procedure and all sps that use a particular table - recursive cte

I am working on a script that gives me a list of tables that use a specific stored procedure. It also works on all tables used by a stored procedure. on the example below, all stored procedures that ...
1
vote
1answer
81 views

MySQL 5.6 - Creating an event inside a stored procedure

I am trying to create a procedure which create an event in MySQL. For example, something similar to this: delimiter // CREATE PROCEDURE schedule_my_db_job() BEGIN CREATE EVENT MY_DB_JOB ...
1
vote
1answer
69 views

How to create a stored procedure to update database after table edit

In a C# website application with a database, I set a gridView table so that I could edit the values in the table by row, but it appears that I need a stored procedure to do that. Below is the stored ...
1
vote
0answers
46 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get ...
1
vote
1answer
220 views

mySql while loop ending after a insert

I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements ...
1
vote
2answers
249 views

SQL Server : create table within stored procedure

I have a stored procedure that will be run by a user with public permissions. When operational, it will be called from an Excel spreadsheet and the results presented on a given tab. The procedure has ...
1
vote
1answer
34 views

Store Procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
1
vote
0answers
94 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
1
vote
2answers
53 views

Object Reference Not Set … Debugging Stored Procedure in Visual Studio

I'm working on updating a stored procedure that is called to update a user. This stored procedure needs to also create a description of what was updated for auditing purposes. However when debugging ...
1
vote
1answer
49 views

How can i write a stored procedure which will perform a calculation and store the result in a table?

I have two tables: CREATE TABLE `siteArea` ( `length` decimal(6,2) NOT NULL, `breadth` decimal(6,2) NOT NULL, `Area` decimal(6,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE ...
1
vote
0answers
27 views

Oracle 11g XE - Giving granting privileges to a stored procedure

I need to use a stored procedure to give certain privilges over a view to users. However I don't know how to give the procedure the necessary privileges to grant users privileges.
1
vote
1answer
114 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 ...
1
vote
1answer
175 views

mysql trigger / stored procedure - update another table row by row

I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this. this will be a little long, be patient please. First of all, I am trying ...
1
vote
1answer
117 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
40 views

Procedure to read from crontab like table and populate another one periodically

Postgres 9.3 Debian 7 I'm working on a mobile game project (poker like) where an admin user can register a recurrent tournament. As it's recurrent, I modeled it crontab like as follows. ...
1
vote
1answer
213 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
0answers
11 views

Adding new sproc to the exisiting database using connectors?

The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy. The question arose when I've tried to find out the way of adding new ...
1
vote
0answers
227 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
1
vote
2answers
103 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 ...
1
vote
0answers
73 views

Parameter Sniffing Question [closed]

My question is: If we employ a plan guide, why would we still experience the effects of parameter sniffing (ie: Where the query returns results at different speeds, 1s, 1s, 1s, 15m, 2s, 2s, etc..) ...
1
vote
1answer
41 views

How can I setup a Login which can run an automated Database Refresh without giving away too much priv?

I would like to re-ask and extend the question posed in this thread if I may: SQL Server permissions to create, restore, delete only some databases I am also attempting to setup an 'isolated' ...
1
vote
0answers
49 views

Read show create table in mysql stored procedure

How can I read the output of following query and assign to a stored procedure's variable? SHOW CREATE TABLE users; Or, is there is an information schema from where I can draw the full table ...
1
vote
0answers
257 views

Return both Result set AND a warning from a stored procedure

In Data Studio when you call SYSPROC.ADMIN_CMD() directly and it returns a warning, the results panel shows both the warning and the result set: CALL SYSPROC.ADMIN_CMD('EXPORT TO ...
1
vote
0answers
495 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
52 views

When will store procedure/functions support result set as return type

I have noticed that MySql doesn't support result-set on a store procedure and functions, which means return a table. So I can't do things like CALL sp_my_store_procedure() ORDER BY id ASC; Or ...
1
vote
0answers
49 views

Is there a way to call sp_spaceused in a WHILE loop?

A legacy Sybase script moves records from production to archive in 1000-row batches, but it does not check for space exhaustion on the archive. Is there a way, maybe by calling sp_spaceused, to ...
1
vote
0answers
32 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
1
vote
2answers
54 views

What is the correct query set up to access the columns in table being passed as parameter?

I am trying to use table-valued parameter to bulk upsert some data into my table. I have the following stored procedure. This setup seems to agree with the intellisense. I am not getting any errors ...
1
vote
2answers
302 views

call mysql function by string name

Can i call a mysql stored function from another mysql stored function or stored procedure, passing a string to the first function which is the name of the second function to call. I.e choosing the the ...
1
vote
2answers
334 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 ------------------------- ...
1
vote
0answers
228 views

How to access another db from a stored procedure in db2

I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2. ...
1
vote
1answer
270 views

Execute grant for MySQL stored procedure revoked when stored procedure is altered

I have created the following stored procedure in a MySQL database (MySQL 5.5). DELIMITER $$ CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`() BEGIN "code here" END$$ ...
1
vote
1answer
302 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` ...
1
vote
1answer
131 views

How to log within a stored procedure that is encapsulated within a transaction

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I ...
1
vote
2answers
791 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
0answers
250 views

Stored procedure taking much longer than query [closed]

I have a simple code segment that runs in about 1 second when run alone, but more than 5 minutes when deployed as a stored procedure. I'm using sql server 2008. Unfortunately, I can't post the code ...
1
vote
0answers
1k views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH ...
1
vote
1answer
227 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 ...
1
vote
0answers
249 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
1
vote
0answers
287 views

user rights validation inside stored procedure?

You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application. Ignoring for the purposes of this question what should ...
1
vote
0answers
147 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 ...
1
vote
0answers
1k 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 ...
1
vote
1answer
8k 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 | ...
1
vote
0answers
221 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
1answer
395 views

Cannot grant execute to stored procedure [closed]

When I try to 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 ...
1
vote
0answers
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 ...