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
289 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
1answer
39 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
2answers
78 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
52 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
30 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
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 ...
1
vote
1answer
304 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
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
49 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
0answers
12 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
295 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
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 ...
1
vote
0answers
78 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
45 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
56 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
336 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
727 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
58 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
56 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
437 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
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 ------------------------- ...
1
vote
0answers
251 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
7k views

Executing try catch block in mysql stored procedure

In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure . I want my select query to be in try block and if it executes correctly I want other query to ...
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` ...
1
vote
1answer
142 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
0answers
301 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
0answers
272 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
339 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
157 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
2k 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 ...
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
243 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
414 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
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 ...
1
vote
1answer
9k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...
1
vote
0answers
1k views

How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
1
vote
1answer
948 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
1
vote
0answers
650 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
1
vote
0answers
2k views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
1
vote
4answers
93 views

MYSQL: used variable on IN condition

EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results into a ...
1
vote
3answers
653 views

How to use a MySQL error handler in a stored procedure with prepare/execute

I have a procedure defined like this: drop procedure if exists drop_tables_like; delimiter $$ create procedure drop_tables_like(myint int, pattern varchar(255), db varchar(255) ) begin DECLARE ...
1
vote
1answer
286 views

Why is my stored procedure very much slower than the same MYSQL query? [duplicate]

My stored procedure is very slow compared to a normal query or a temporary table. How can I optimize my stored procedure? CREATE DEFINER=`datasll`@`...` PROCEDURE `product`() BEGIN EXPLAIN ...
1
vote
3answers
4k views

BACKUP failed to complete - with spaces, for no reason

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
0
votes
2answers
1k views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
2answers
147 views

What do cursors do internally?

I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR... The client saw this SP ran for more than a day so, my manager assigned this to me. Upon searching, I used table ...
0
votes
1answer
232 views

Simply inserting to temp table and failing

Can anyone tell me why this won't work for me? CREATE TABLE #UndistributedCmds ( pendingcmdcount int, estimatedprocesstime INT ) INSERT INTO #UndistributedCmds EXEC ...
0
votes
2answers
5k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...
0
votes
2answers
3k views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...