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

Examples of SQL transaction procedures for sales tracking or a financial database

I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions: salesman creates new product ...
4
votes
2answers
593 views

Fetch first 9 records and make one more record which will be as “Others” in MySQL

I am fetching records from one table with count of one field with other field as name. I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as ...
4
votes
3answers
8k 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 ...
2
votes
6answers
803 views

Optimize this SQL query for performance

I'm trying to optimize the following statement: 'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR) +'/00000' +CAST(@number+1 AS VARCHAR) The statement produces a value ...
3
votes
2answers
2k views

How to return a CTE as REFCURSOR from an Oracle stored procedure?

I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement. My procedure looked like this: Create or replace Myproc ( MyRefCursor IN OUT SYS_REFCURSOR) as begin ...
5
votes
4answers
7k views

How do I find out if a procedure or function exists in a mysql database?

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
10
votes
2answers
166 views

Is there anybody using the SQL Server feature to create groups of stored procedures differentiated by number?

The question refers to the number parameter in this msdn documentation If you don't you can create multiple stored procedures in SQL-Server differentiated by number and drop them with a single drop. ...
8
votes
3answers
3k 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 ...
7
votes
3answers
191 views

Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
8
votes
1answer
4k views

How to connect to another db using stored procedures?

I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys. PS. Im using Oracle ...
1
vote
4answers
435 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...