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

Scripting execute permissions on stored procedure

My goal is to write a script which: Creates a stored procedure Grants execution permissions to a particular user on that new stored procedure On this SO question the recommendation is to add GRANT ...
1
vote
1answer
50 views

Determine structure of out cursor

I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains? For ...
1
vote
1answer
45 views

SQL Stored Procedure with one parameter and several variables that will change over time

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in Error output: ...
0
votes
1answer
35 views

How to select recently updated row? [closed]

I have problem in selecting a row in child sp which just got updated in parent SP. MySP updates MyTestTable's MonkeyColumn, and right after it want to return newly updated row to the user by calling ...
2
votes
1answer
53 views

stored procedure can select and update tables in other databases - minimal permissions granted

I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum. This was ...
1
vote
0answers
32 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
6
votes
1answer
112 views

Why would a procedure sometimes hang executing SELECT…INTO #Temp2 FROM #Temp1 JOIN LocalTable…?

We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, ...
0
votes
1answer
95 views

syntax error at or near “IF” [closed]

I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job. For now I have the following code: CREATE OR REPLACE ...
1
vote
2answers
145 views

Script to find the list of stored procedures in all databases

I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database. select * from ...
0
votes
1answer
90 views

Delete Works in one Stored Procedure but not another

I've built an app that uses stored procedures to insert into, update, and delete rows from tables in my database. All domain users have been given execute permissions on these procedures, all ...
1
vote
1answer
66 views

Xquery, insert, Trigger and no place to squeeze stored procedure

I have a Stored procedure, which is, basically, xquery pulling data from xml and inserting it into multiple tables. Once it's inserting into last of the mentioned tables, the trigger fires up and then ...
1
vote
2answers
92 views

SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only

I'm attempting to create a stored procedure that's able to create and drop tables. A given user will have EXECUTE permissions on that stored procedure. Is there any way that the user would be able to ...
1
vote
1answer
49 views

How to rebind a package inside a Stored Procedure (DB2)

I have a few Stored Procedures for my application (DB2 V 9.7.3). I need to rebind the packages corresponding to the Stored Procedures. I wish to get having them recompiled inside a Stored Procedure by ...
1
vote
0answers
38 views

DB2 : Dynamic Result Sets, Duplication in Sending output to File

What I am trying to do here is to Generate insert scripts for selected tables in my database in DB2 10.5 LUW. I have modified a procedure available here as below. CREATE PROCEDURE Create_Inserts ( ...
3
votes
1answer
91 views

Intermittent “Conversion failed…” error

I have several stored procedures. These procedures when called have been fully operational for over 18 months without error. Over the last 3-5 months, occasionally I will receive the error: ...
1
vote
2answers
42 views

Need Script to find SP Invoked in SQL

I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?
0
votes
1answer
28 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
4
votes
1answer
60 views

Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them. The user performing the search is restricted as to what items they can see by ...
3
votes
2answers
229 views

Stored procedure returns -4 after catching exception

In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming ...
0
votes
1answer
42 views

MYSQL: weird bug with nested stored procedure

I have two stored procedures. The first one fetch language codes in my language table. CREATE PROCEDURE `updateCategoryNameAll` ( IN categorieNumber VARCHAR (10), IN categoryName VARCHAR (128) ) ...
4
votes
2answers
291 views

How can I tell what database/procedure is using a linked server?

I have 3 servers, one of which has a linked server configured that points to the other - I'll call this Server A. Server A has over 100 user databases for various purposes. Server B is running SQL ...
0
votes
0answers
16 views

Is it possible to call java API from mysql stored procedure?

I am using stored procedure to migrate data from one schema to another schema. I need to use some complex logic which is already implemented as java API. Is it possible to call java API from mysql ...
1
vote
1answer
31 views

Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

I have the following stored function for calculating ELO ranking: CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT) RETURNS DOUBLE DETERMINISTIC BEGIN DECLARE wynik DOUBLE; SET wynik = ...
-1
votes
0answers
32 views

Get the sum of Total Churn_MTD(Current+2015+<2015) [duplicate]

how to declare 8 variables and get the total Sum,as you can see from the picture i created the 3 store procedures but dont know how to do the ...
2
votes
2answers
193 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
2
votes
1answer
55 views

Is there a query to see which stored procedures are using a specific symmetric key?

Is there a query to see which stored procedures are using a specific symmetric key? Or a built in feature of SQL to see this information. The stored procedures are using: OPEN SYMMETRIC KEY !@#$%^ ...
0
votes
1answer
32 views

Why running queries one by one yields correct results, but running lots of them in one chunk doesn't?

Let's say I create a simple table and a stored function to populate it: CREATE TABLE t ( id INT PRIMARY KEY, val VARCHAR(255) )// CREATE FUNCTION populateIfNotAlreadyIn (idArg INT, valArg ...
-1
votes
1answer
55 views

How to execute system command in mysql stored procedure? [closed]

Here is the code, delimiter // create procedure trig () begin declare cmd varchar(50); set cmd = "sh /home/yogaraj/Documents/Maastry/firefox.sh"; system cmd; end // delimiter ; But I ...
1
vote
1answer
52 views

Best way of order processing

I would like to write a procedure for order processing Single Order can have multiple items, how can I process the order ? Consider below JSON is an example: { "Order": { ...
0
votes
1answer
39 views

Use a SP to Archive/Revision related records in several related tables?

I have a database for an application that has a complicated relational table structure. Is there a good way to create revisions of the data, or archive the data before a user makes changes? Ideally ...
4
votes
1answer
364 views

Is there an alternative to “Create or replace procedure” in MySQL?

Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the ...
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
13 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
52 views

Stored Procedure Error

I am trying to create this procedure in MySQL and show the error mentioned below. CREATE DEFINER=`root`@`%` PROCEDURE `synctables`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL ...
0
votes
0answers
19 views

Is there an alternative to delete a procedure inside a procedure?

For my Bachelor Thesis I try to automate some recurring tasks in MySQL. One task consists of the deletion of a specific stored procedure. Normally I would create a stored procedure which takes one ...
0
votes
0answers
38 views

Syncing DB's between local and live server?

I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below. There are two ...
6
votes
2answers
471 views

Raise error if BCP command fails to dump data into a file

Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error ...
1
vote
3answers
34 views

MySql cursor is failing to retrieve all results

I am importing data from one table(say old_table). I have made a table(say new_table) in another database and i am using mysql stored procedure to populate it. I need to iterate through each record in ...
3
votes
1answer
124 views

procedure is deleting data instead of inserting

I have a procedure as the following: create procedure MoveDailyData @date1 bigint as declare @datenow bigint set @datenow = datediff(S,'01/01/1970',getdate()) if ...
0
votes
0answers
16 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
0
votes
0answers
11 views

Is it possible to create a Stored Procedure inside a Stored Procedure in MySQL? [duplicate]

For provenance aspects, I want to keep track of the changes of a stored procedure of a MySQL database. When I want to update a stored procedure, I want to save the old procedure definition in a ...
1
vote
2answers
79 views

Problem executing multiple procedures [closed]

I'm really new to SQL Server development and I learn everyday. Today I encountered a problem while executing multiple stored procedures in a query. I saw only one of both start. After a long research ...
0
votes
0answers
137 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to ...
2
votes
0answers
50 views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...
3
votes
2answers
576 views

How to get return value from pl sql procedure and assign it to variable?

Code: execute x.procedurename(row.accc_no, row.bill_no, 0, null, row.total_balance, 'A',assgn_scen_site_cv, :assgn_scen_site_cv); Its my procedure. How to get return value from this proc and ...
0
votes
0answers
25 views

calling multiple procedures from my application Vs calling procedures inside another procedure

I have a scenario of saving user details into my database(MySQL 5.1); i have three tables for saving user details; user_data,user_contact,user_address, so if i insert one user data i have to check for ...
0
votes
2answers
53 views

I have following two MySQL queries and i want to execute it inside one procedure

Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure. Query 1: ...
0
votes
0answers
39 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
1
vote
1answer
271 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 ...
4
votes
2answers
63 views

Timeout expired SqlException for complex Procedure

I use Sql Server 2008 R2 and ASP.NET 4.5. Sometimes, I get this error when I executed a complex procedure: System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed ...