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
1answer
303 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
1
vote
1answer
30 views

Alter of stored procedure and execution performance

I would like to understand how an alter of a stored procedure without modifications can impact performance. I had a situation where a production environment had its database schema updated and a ...
0
votes
0answers
23 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
3
votes
1answer
50 views

How can I get the last inserted id from an specific table from a given stored procedure?

I know about the existence of LAST_INSERT_ID() function... Though I am not quite sure about its behaviour... Does it take the last inserted id from the current user into account? Or uses all the ...
0
votes
1answer
34 views

Why is my PL/SQL procedure so slow?

I have this sub-procedure in a fairly large program, and it is taking hundreds of times longer than comparable sub-procedures. Is there any way I could improve efficiency here? None of this ...
-9
votes
1answer
48 views

Stored Procedure which INSERT, UPDATE, DELETE any table(for any table) where table name and its values are passed as parameter [on hold]

Stored procedure which performed operations like inserting data to table, updating data, and deleting data of table where table name is passed by parameter. Parameters of stored procedure: table name ...
2
votes
1answer
24 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 ...
6
votes
3answers
637 views

Can I query stored procs in all databases with one query without dynamic sql

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following: SELECT 'select ' + ...
3
votes
3answers
144 views

INSERT/UPDATE in same query from previous SELECT with some singularities

I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,`first`,`last`,email) SELECT ...
0
votes
1answer
231 views

EXEC Multiple procedures in parallel?

SQL Server 2008. I have several stored procedures. I would like to execute them all in parallel, not serially. How can I do this?
1
vote
0answers
10 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
2answers
58 views

Stored procedure using cursors

I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as emp (eid, ename, age, salary) works ...
0
votes
1answer
174 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
1answer
3k views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
2
votes
1answer
31 views

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

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 ?
0
votes
1answer
263 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
1
vote
1answer
213 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` ...
3
votes
1answer
346 views

Stored Procedure to update an adjacency Model to Nested Sets Model

If you don't already know, these two models are the most common ways to store a tree in a relational DB. Adjacency Model: +-------------+----------------------+--------+ | category_id | name ...
0
votes
0answers
24 views

msdb.dbo.sp_start_job got stuck in stored procedure

I have a stored procedure USP_A with logic like following: ...... BEGIN TRY exec dbo.usp_Log 'Start to run job job1' Exec @intErrorCode = msdb.dbo.sp_start_job 'job1' IF @intErrorCode <> 0 ...
0
votes
1answer
161 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 ...
2
votes
3answers
5k views

What's the difference between functions and stored procedures? [closed]

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are. Is that true? If not, what are the ...
0
votes
1answer
27 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
0
votes
0answers
18 views

Statement ignored - PL/SQL Procedure

create or replace PROCEDURE UNIQUE_MATCH ( PHYSICIANID IN NUMBER , FNAME IN VARCHAR2 , LNAME IN VARCHAR2 , POSTALCODE IN VARCHAR2 , SOURCETABLE IN VARCHAR2 ) AS ALREADY_HAS NUMBER; BEGIN ...
1
vote
1answer
26 views

Is there another/better way to create and maintain large amounts of procedures?

So I've decided to take a step back, and scrutinize the way I implement procedures on my databases. I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a ...
0
votes
1answer
153 views

MySQL Subquery Returns Null In Stored Procedure, Works Fine In Query

I have a subquery in a MySQL Stored Procedure where the value returns null even though the data is clearly there. Pulling the subquery out and running it works fine. I've checked the parameter values ...
0
votes
2answers
24 views

Retrieve data from SP inside of From

Goal: Retreive a list of data from a stored procedure that is acting as a database name. When you have retrieved the list from stored procedure, I want to make another new data manipulation. Problem: ...
1
vote
1answer
44 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
3
votes
1answer
28 views

A Customized Table with Chronological order

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id. Problem: When I ...
3
votes
0answers
69 views

Why the stored procedure is much slower than a temporary table?

I want to do the updating of my database every day and I will like to use stored procedures but the time of execution of the temporary table is faster than the stored procedure. How can I optimize my ...
1
vote
0answers
16 views

BLToolKit save null instead of 0 [migrated]

(Can't use it as tag, but we're Using BLToolkit as ORM) I have this weird problem where BLToolkit saves NULL instead of 0 in our database. If you look the image, this is my values that will be ...
0
votes
0answers
18 views

MySql stored procedures are not atomic (JDBC)?

I have a stored procedure with one UPDATE and one INSERT in its body. I call the SP with a single executeQuery(...) wrapped within a try-with-resources. The DB connection is set (by default) to ...
0
votes
0answers
194 views

How to load XML data into an oracle db table?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? Can we write a stored procedure to accomplish the same in a more efficient way with the best performance? Can we ...
0
votes
0answers
44 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
1
vote
1answer
126 views

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

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 ...
0
votes
0answers
25 views

How to Create a Recommended Test Planning in SQL Server

Goal: Make lots of different test for a specific stored procedure named 'sp_test' in order to review if you are retrieving the same data based on data input. Purpose: If you have made lots of ...
1
vote
1answer
169 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 ...
4
votes
1answer
90 views

Scheduled stored procedure on all databases in my server

On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour. On the same server I have lot of databases with the same structure, and I'm constantly ...
0
votes
2answers
2k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
1
vote
0answers
59 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
votes
1answer
23 views

Run Sp on linked server 2008 [closed]

How could i run my sp on a linked server ? Using sql-server-2008 Exec my_sp [db-srv_name].[db-name].[dbo] Please help me i am new in sql
1
vote
1answer
38 views

Setting query to run as a stored procedure and email the results [closed]

I have the following query: select COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections' , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL ...
0
votes
1answer
29 views

trouble creating a stored procedure to print out values for each day of week

I am having difficulty in understanding how to get this working correctly. I am trying to have create a stored procedure that is run automatically on a sunday and will get the data for the previous ...
1
vote
1answer
225 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
17
votes
2answers
6k 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 ...
1
vote
3answers
717 views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
0
votes
2answers
119 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 ------------------------- ...
4
votes
2answers
225 views

Tracking down a stored procedure's parameters after execution

I have been asked to identify a permissions issue with a stored procedure. This stored procedure behaves in two possible ways depending on what values are used for its parameters. exec ...
2
votes
1answer
15 views

Retreive a Customized List with input data

I have a database that has about 223 tables and I need to locate the name of the constraint. My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the ...
0
votes
3answers
462 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
1answer
610 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 ...