Tagged Questions

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
5k views

Running a stored procedure across db Link

I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ...
8
votes
2answers
2k views

How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this create table #t1 (...); insert into #t1 Select ... from table_a where ...; insert into ...
6
votes
1answer
1k views

Can I call SMO from pure T-SQL?

I know how to script a stored procedure using PowerShell and SMO: [System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null ...
2
votes
1answer
4k views

“Procedure: XXXX has an unresolved reference to object XXXX” errors

After using for some time MS SQL Server Management Studio for creating and editing databases I tried to use Visual Studio 2010 "SQL Server 2008 Database Project" for this. I used the SQL 2008 Wizard ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
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
682 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 ...
5
votes
3answers
9k 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
853 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
9k 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
172 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
193 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
5k 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
443 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 ...