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

4
votes
1answer
589 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
42 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
54 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
23 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
49 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
609 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
36 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 ((@datenow)-@...
0
votes
0answers
17 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 information_schema....
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
80 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
175 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
53 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
960 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
27 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
56 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
49 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
403 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
70 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
48 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 ...
2
votes
1answer
50 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. Example:...
1
vote
1answer
58 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 ...
-2
votes
1answer
76 views

Password protected Database Engine with Server features and stored 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. SQL Server is one of the best DB engine ...
1
vote
1answer
28 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 ICB3110\...
0
votes
0answers
65 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 = '`tituloOferta`';...
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
178 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
54 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
123 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
64 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
29 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
69 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
140 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
199 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? ...
0
votes
1answer
36 views

How do I go about creating a script to compare , update and match structure of two databases

I am comparing two databases using Opendiff which is similar to sqlCompare. I have two databases which should match in structure so as I compare I would like to construct a script such that if the ...
0
votes
2answers
49 views

Column name or number of supplied values doesn not match table definition

I have created three tables using USE UserDecomAudit GO CREATE TABLE ADUserData ( ADUserID int NOT NULL PRIMARY KEY, Name varchar(50), SamAccountName varchar(10) NOT NULL, DistinguishedName varchar(...
0
votes
1answer
34 views

Exhaustive SQL stored proc validation make insert slow

So basically our team is working on a module to retrieve rows value from excel file and insert it during batch job. The requirement are as follow: - Exhaustive validation should be done for each ...
5
votes
1answer
431 views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
0
votes
1answer
36 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
0
votes
0answers
122 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
16 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
0
votes
1answer
59 views

When to use stored procedure / UDF? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
1
vote
1answer
82 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
votes
1answer
225 views

Job successfully execute Stored Procedure but no results [closed]

I have stored procedure which is using dynamic query and inserts details of severs into table. If I execute stored procedure manually it works fine. but if try to execute the same with JOB, Job ...
2
votes
1answer
258 views

Encryption/Decryption with StoredProcedure/View

I'm working on a project that uses EntityFramework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first ...
0
votes
1answer
589 views

how to call a stored procedure in Oracle Scheduler job

i want to call a stored procedure in Oracle Scheduler job, my procedure and job like below ----stored procedure create or replace PROCEDURE emp_test IS begin delete from emp; insert into emp(...
0
votes
0answers
90 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
0answers
120 views

Postgres asynchronous procedure execution?

I have an ETL script entirely contained within a Postgres 9.3 procedure. The script takes several hours to process, which is fine for our purposes. What I haven't been able to find out is if there ...