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
2answers
3k views

MySQL Workbench not showing stored procedures that I have created

I have a simple stored procedure that I have just created. I have applied this script to my database This script shows up in the my list of stored procedure However, when I restart MySQL ...
1
vote
2answers
689 views

Stored procedure to check if username exists

I'm trying to write a stored procedure So far I have this, but it always returns 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4) SELECT CASE ...
1
vote
2answers
296 views

How are these tempdb tables automatically created after each SQL Server restart?

I have a session state management implementation using SQL Server. There is an ASPState database, and ASPStateTempApplications & ASPStateTempSessions tables in the tempdb database. When I restart ...
1
vote
1answer
243 views

Procedure permission issues with a SQL state query

We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side. ...
1
vote
1answer
169 views

How can I get a local variable that's a list in a stored procedure?

I have some code in C# I'm trying to move to a stored procedure - Sql Azure does not like multiple calls. So everything needs to get moved to a proc. Here's the C# code: List<string> ...
1
vote
2answers
919 views

AWS RDS MSSQL Extremely Slow compared to EC2 with MSSQL

Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format. ...
1
vote
1answer
352 views

MySQL Cursor Batching Process

I have a cursor in MySQL and what I want to do is rather than having an enormous cursor loop that can be up to 4000+ users in size, do it smaller batches so that the MySQL engine doesn't suffer. I ...
1
vote
2answers
730 views

Converting MSSQL Stored Procedure to MySQL - Syntax Error

I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out: CREATE PROCEDURE ...
1
vote
1answer
1k views

Recursive calls in stored procedure and temporary tables

I am trying to create a stored procedure in PostgreSQL which has recursive calls. The procedure creates a temporary table to store results. The temporary table isn't dropped after first call due to ...
1
vote
2answers
339 views

Is outputting to both a result set and a temporary table possible?

I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets. Here is a basic schema of what I ...
1
vote
1answer
124 views

Is this application creating stored procedures on the fly? [duplicate]

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue. When looking at *sys.dm_exec_sql_text*, I ...
1
vote
1answer
4k views

Rollback parent procedure on failure in MySQL

I have the following two procedures on my server: CREATE DEFINER=`someone`@`localhost` PROCEDURE `NewMagnet`( IN `nick` VARCHAR(32), IN `tth` CHAR(39), IN `name` TINYTEXT, IN ...
1
vote
1answer
4k views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
2answers
225 views

How to fill a tabe automatically with a procedure in MySQL

I have a very big table for users as below: CREATE TABLE `karbar` ( `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ...
1
vote
1answer
694 views

can I Run a Stored Procedure across all SQL servers and return 1 html report

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 ...
1
vote
1answer
2k views

How to run stored procedure in another database on the same server using a different login?

I have 2 databases (A and B) on the same server. Database A has user ua defined, and database B has user ub defined. I have a stored procedure in A, running as ua, that needs to run a stored ...
1
vote
1answer
323 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
457 views

Stored procedure to validate xml content against dtd

I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file? Thank you in advance!
1
vote
1answer
8k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
1
vote
1answer
2k views

Tracking changes in stored procedures within a database

Could you please help me out here. I have multiple databases and each of the databases have the same tables and stored procedures. I want to be able to compare the stored procedures and see if there ...
1
vote
1answer
484 views

Inserting filtered data from one table to another

I need to find the number of employees who are not in all departments. I have one more table T2 in which same department column is present. Now I want to insert only those values of department which ...
1
vote
1answer
114 views

Stored Procedure Not working as Expected

When I create the stored procedure without parameter it works as expected, but when I create it with parameter it doesn't return any row. DELIMITER // CREATE PROCEDURE search() BEGIN SELECT * FROM ...
1
vote
2answers
2k views

Same Parameter in MySQL Stored Procedure

How do I cache dynamic query from store procedure? Right now I have created my store procedure like this : CREATE PROCEDURE usp_MyProcedure ( IN UserID INT, .... ) BEGIN SET @sqlQuery = ...
1
vote
1answer
581 views

Trigger not firing when deleting a record from stored procedure MySql

Bare with me, as I'm fairly new to stored procedures / triggers. I have the following Stored Procedure: DELIMITER $$ CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
1
vote
1answer
2k views

PostgreSQL stored function that returns arbitrary resultset

I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
1answer
771 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
1answer
1k views

MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host

We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication. "'Column count of mysql.proc is ...
1
vote
2answers
1k views

PostgreSQL stored procedure which can return one of two custom rowtypes

I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this: CREATE OR REPLACE FUNCTION ...
1
vote
3answers
1k views

Migrate DB2 UDB (Iseries) to SQL Server [closed]

I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server. It's quite easy migrate schema and data using linked server or import export with SISS. But what about stored ...
1
vote
1answer
326 views

MySQL Dump all rountines except one

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
1
vote
1answer
217 views

Access Drizzle databases through MySQL

This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot ...
1
vote
1answer
2k views

Is there a way to skip errors in procedures and resuming procedure

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again. ...
1
vote
1answer
369 views

What can cause an UPDATE statement to execute endless? [closed]

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night. Today it started but never endet. So i've tried to run ...
1
vote
1answer
338 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
1
vote
2answers
2k views

Should I put the error handler inside the loop, or the loop inside the error handler?

When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
1
vote
1answer
4k views

Heterogeneous queries error using stored procedure

Stored procedure ALTER PROCEDURE Proc_DayShift AS insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO SET ANSI_WARNINGS OFF ...
1
vote
1answer
144 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
1
vote
1answer
32 views

weekly moving average from current_date to the last 52 weeks

I'm a new head to the mysql programming. I have to write a procedure for calculating a weekly moving average grouped by week from 'monday-friday'. Criteria is : current_date is the end date. start ...
1
vote
2answers
23 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
1
vote
1answer
72 views

Start failed for Job in SQL Server Agent

I am trying to schedule a job which executes stored procedure but it is failing. DECLARE @return_value int EXEC @return_value = [dbo].[sp_WarehouseAttendance_New] SELECT 'Return Value' = ...
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 ...
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
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 ( ...
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?
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
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 ...
1
vote
1answer
156 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 ...
1
vote
1answer
81 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
vote
0answers
46 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get ...