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
0answers
9 views

Unexpected table created by running store procedure with integer OUT parameter [on hold]

I have written a simple SQL-script, which drops tables, then creates them and replaces stored functions in a PostgreSQL 9.5 database. The names of tables and stored functions are all prefixed by ...
0
votes
1answer
25 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
56 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
214 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
34 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
158 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
10 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
23 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
37 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
52 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
29 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
25 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
41 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
37 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
32 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
40 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
35 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
16 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
19 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
100 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
30 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
113 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
10 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
75 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
38 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
33 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
58 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
16 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
46 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
27 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
81 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
38 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 ...
0
votes
1answer
47 views

Slow query with JOIN

I have a stored procedure which runs pretty slow (6 sec) using the following JOIN: JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange) Without ...
1
vote
1answer
28 views

Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

MySQL does not support declaring constants which can be used between many stored procedures. For me, it seems that a deterministic no sql function can be used as an alternative for a constant. ...
1
vote
1answer
30 views

Stored Procedures execution permissions

So what I am trying to do is to limit the access to a stored procedure so that only another stored procedure may call it. Here is an example: create procedure Database_Deployment_Proc -- This ...
-1
votes
1answer
44 views

Password protected Database Engine with Server features and Store 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. MS SQL server is one of the best DB ...
1
vote
1answer
22 views

I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step

create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20)) AS BEGIN DECLARE @SQL VARCHAR(8000) declare @sql1 VARCHAR(1000) declare @counts numeric(4) set @counts=0; SET @sql1='SQLCMD -S ...
0
votes
0answers
41 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = ...
0
votes
1answer
18 views

First field from my return table is being eaten up

Another problem with some PostgreSQL stored procedure code... Same from this question: I have this function drop function uptimes(inicio date, fim date); create or replace function uptimes(inicio ...
1
vote
1answer
80 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 ...
0
votes
1answer
37 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
3
votes
1answer
85 views

Determine Which Parameters Passed Into Stored Procedure

Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?
1
vote
1answer
43 views

Procedure completes prematurely after converting comma-delimited value strings to XML

I have a procedure which executes beautifully within SSMS environment yet, when I hook it up to a web page, instead of seeing a list of packageIDs returned, the return is 'null'. I've checked ...
0
votes
0answers
28 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
34 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
1
vote
2answers
71 views

deteriorating stored procedure running times

I have a stored procedure that is executed by my web application. Today, when an end user started an operation that affected more rows than usual, I started getting calls that the wait times for the ...
5
votes
1answer
100 views

When does an execution plan not show in sp_WhoIsActive?

I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries. Why does the execution plan not show all the time in the results? ...