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.
7
votes
3answers
2k views
Dynamic SQL in MySQL stored routines
According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
1
vote
1answer
294 views
decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose ...
3
votes
1answer
3k views
DROP PROCEDURE IF EXISTS not included in mysqldump
I'm dumping my stored procedures only using the following command:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql
but the ...
5
votes
2answers
905 views
'Conversion failed' error with ORDER BY CASE expression
I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'.
I get the following error:
Conversion failed when converting the nvarchar value 'SK11270' to ...
4
votes
1answer
163 views
How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?
This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
3
votes
1answer
351 views
Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]
Possible Duplicate:
Determining how a schema change occurred?
I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
7
votes
3answers
2k views
What is the best method to add error handling in SQL 2005 stored procs?
What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?
Additionally, whats the best way to handle multiple error scenarios in a stored ...
6
votes
2answers
2k views
Performance of a Trigger vs Stored Procedure in MySQL
A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
9
votes
6answers
2k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
6
votes
1answer
2k views
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
5
votes
3answers
694 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
2
votes
4answers
7k views
Call a stored procedure from a trigger
I have created a stored procedure in mysql using the following syntax.
DROP PROCEDURE IF EXISTS `sp-set_comment_count`;
DELIMITER $$
CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
-- ...
2
votes
3answers
2k views
SQL Server: How to call stored procedure without schema name?
SQL Server 2005std/2008web edition
db=user=schema=owner='John'
I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.
Dim sqlCmdVehicle ...
1
vote
2answers
1k views
BACKUP failed to complete - with spaces, for no reason
Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
0
votes
1answer
160 views
how to dump a single stored procedure from a database
I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
0
votes
1answer
127 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.
...
9
votes
1answer
1k views
How do you ORDER BY a parameter?
Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!).
Basically I have ...
11
votes
2answers
2k views
MySQL: Tree-Hierarchical query
SUB-TREE WITHIN A TREE in MySQL
In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
8
votes
2answers
526 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
11
votes
3answers
643 views
Is table aliasing a bad practice?
I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:
SELECT t1.id, t2.stuff
FROM
someTable t1
...
4
votes
3answers
6k views
Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*
Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions:
Attempting to view or run ...
3
votes
3answers
2k views
Stored procedure executes too long
We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
2
votes
2answers
6k views
Data Truncated for Column
DECLARE float_one, float_two, my_result NUMERIC(7,2)
my_result = CONVERT(float_one/float_two, DECIMAL(7,2));
In this mysql query, I do this type of operation in a stored procedure, but the Linux ...
1
vote
1answer
2k views
Can mysqldump dump triggers and procedures?
Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?
Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
11
votes
0answers
1k views
Passing array parameters to a stored procedure [duplicate]
Possible Duplicate:
Technique for sending lots of data into stored proc
I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.
...
9
votes
1answer
3k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
7
votes
6answers
725 views
How do you submit a List of items to a Stored Procedure to limit results? [duplicate]
Possible Duplicate:
Passing array parameters to a stored procedure
Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read:
WHERE ...
6
votes
4answers
2k views
Do SQL Server stored procedures cache data results?
I have heard this from friends, but I never investigated whether this is true.
Is it true that the data results of an executed query are stored in cache?
I mean, if I have a stored procedure like :
...
6
votes
2answers
497 views
Most efficient way to return multiple aggregates in a single stored proc?
What is the best or most efficient way to get multiple aggregate result values?
Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
5
votes
1answer
237 views
Conditional compilation of SQL Server stored procedure
Short version: Is there some way to conditionally compile chunks of TSQL code in a SQL Server Data Project using SQL Server Data Tools for Visual Studio 2010?
I'm using SQL Server Data Tools in ...
3
votes
1answer
3k views
Is commit necessary after DML operation in Function/Procedure?
I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?
Example:
create or replace function test_fun
return number is
begin
delete from a;
return ...
3
votes
2answers
2k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
2answers
1k views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
2
votes
1answer
2k views
MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query
I've created a procedure to loop through large InnoDB tables and delete a chunk of rows, then pause and repeat until max field value allowed is reached.
Binary logging is enabled, but I'm leaving it ...
2
votes
1answer
281 views
performance issues when concurrent requests
We are using SQL Server 2008 R2 on a Windows Server 2008 server.
We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it.
I'll take one SP as an ...
2
votes
1answer
138 views
Stored Procedure doesn't show result in SQL Server
I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
2
votes
1answer
82 views
don't want to be able creating stored procedures with incorrect
Right now I can execute a CREATE PROCEDURE or an ALTER PROCEDURE script with incorrect table and column references in the MS SQL Server Management Studio and using ADO.NET commands. The scripts run ...
1
vote
1answer
87 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
1
vote
1answer
388 views
Declare variable of table type in PL/pgSQL
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:
q1 = select * from foo;
q2 = select * from bar;
...
1
vote
3answers
476 views
SELECT Data Set Using Stored Procedure with BCP Error
All, I have a BCP export query. It is failing with the useless error mesage on BCP usage:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f ...
1
vote
1answer
195 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 ...
0
votes
1answer
147 views
What is the best way to insert a XML input to an oracle table in stored procedure?
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:
<rowset>
<row>
...
0
votes
1answer
244 views
Preloading a Java library in DB2, similar to ORACLE's loadjava
Edit to add details to clarify, based on Chris' comment:
A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
1answer
98 views
TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs
I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs.
Each office has already imported data ...
