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

CASE ORDER BY with multiple columns, and different sort options

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT. @Orderby = 1 Then Date column should be ASC @Orderby = 2 Then Date column ...
2
votes
1answer
380 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
10
votes
6answers
4k 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, ...
1
vote
2answers
3k views

Print database_size from sp_spaceused

Using SQL Server 2008, I have found that the database size can be obtained using exec sp_spaceused This procedure returns two tables: database_name | database_size | unallocated space reserved | ...
6
votes
1answer
30k views

How does MySQL return a result set from a stored procedure?

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that ...
19
votes
4answers
7k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
3
votes
2answers
3k views

How to optimize this dynamic stored procedure

I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure: CREATE PROCEDURE GetEnquiries ( @EnquiryDate DATETIME = NULL ) AS ...
4
votes
1answer
500 views

Kill CLR stored procedure process

I have a CLR stored procedure which exports a large table into a text file. The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it ...
1
vote
2answers
1k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
3
votes
1answer
803 views

Programmatically copy index from one table to another, via a stored procedure?

I use a stored procedure to copy the tables from one database to the other database. Right now it does insert into copy select * from original I would like to copy the index from the original ...
2
votes
2answers
22k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
8
votes
4answers
329 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 ...
1
vote
4answers
1k views

How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query. Simply i can use ...
5
votes
3answers
651 views

Books on optimizing and refactoring SQL stored procedures

Can you please recommend resources to write efficient store procedures, refactor and optimize them? EDIT: - jcolebrand ♦ The intent of the answers to this post may be books or non-transient blogs. ...
2
votes
0answers
178 views

Stored procedures over SQL in the code, good idea? [duplicate]

Possible Duplicate: What are the arguments against or for putting application logic in the database layer? Assuming I got over the problem of maintainability and debugging. My assumption is ...
0
votes
2answers
4k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
1
vote
1answer
5k views

Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

I have upgraded MySQL version to 5.5.16 After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed. In those functions, cursors are there, but ...
10
votes
2answers
849 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 ...
3
votes
3answers
2k views

Are there ways to only replace SQL Server stored procedures when the definition has changed?

I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors. if object_id('my_proc') > 0 drop procedure my_proc go ...
3
votes
3answers
4k 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
282 views

ORM-style server-side programming languages (OO replacement for PL/SQL?)

Good evening, Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]? (for MySQL, PostgreSQL, Oracle or etc.)
0
votes
0answers
118 views

how do i bulk copy a huge amount of data and keep the same table updated through stored procedure in SQL SERVER [duplicate]

Possible Duplicate: How to move data from big table In my project i need to fetch datas from different tables in a data base and create a new table with the same colmn names for all those ...
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. ...
2
votes
1answer
1k views

SQL Server stored procedure permissions

I know that it's possible to set execute permissions for a stored procedure to a database role or user. My question is, how is access to any tables that the SP accesses determined? Does the ...
4
votes
1answer
804 views

Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?

In SQL Server I have set up transational replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored ...
5
votes
3answers
737 views

Does SQL server optimize or pre-parse stored procedures?

Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
0
votes
2answers
3k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...
2
votes
1answer
190 views

stored procedure times out while subsequent runs take 1/6 the time

So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other ...
3
votes
1answer
6k 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 ...
3
votes
3answers
343 views

Same tables with same data, different SQL Server stored procedure performance

I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs ...
5
votes
2answers
71 views

Using stored procedures

I have a task which receives an input and performs a series of tasks on the SQL server, which some depend on the content of the server and on the input. For that I use a lot of selects and then logic ...
8
votes
6answers
2k 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
1answer
14k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
13
votes
3answers
982 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 ...
2
votes
2answers
911 views

SQL 2005- stored procedure performance troubleshooting

I am investigating performance issues for a stored procedure on SQL 2005 server. This is how I intend to do it: Analyze the stored procedure for any bad TSQL practices (like count * etc) Run it ...
0
votes
1answer
409 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
5
votes
3answers
308 views

Can this method for unit testing a stored procedure be improved?

When I have written unit tests for my stored procedures, they usually look something like this: /* Unit test for stored procedure which runs a query */ /* Define #temp table with structure of query ...
7
votes
2answers
4k 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 ...
6
votes
2answers
2k 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 ...
11
votes
1answer
5k 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 ...
3
votes
3answers
14k 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 ...
4
votes
1answer
6k 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 ...
7
votes
1answer
6k 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?
4
votes
2answers
4k views

MySQL Deterministic Procedures

As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic? It seems to me that the majority of stored procedures will be ...
3
votes
2answers
381 views

Does adherence to the DRY principle justify dynamic SQL?

Suppose I have two queries that will be frequently run against my database: SELECT UserID, UserName, UserGender FROM Users WHERE UserID = @User SELECT UserID, UserName, UserGender FROM ...
3
votes
4answers
2k views

Should I convert empty varchar values to NULL?

I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a ...
1
vote
1answer
135 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. ...
3
votes
1answer
661 views

Help with DB2 Procedure

I have the following query: CREATE PROCEDURE MYPROC( IN @TABLES VARCHAR(500)) DYNAMIC RESULT SETS 1 LANGUAGE SQL SPECIFIC MYPROC NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT BEGIN ...
3
votes
2answers
2k 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 ...
3
votes
1answer
792 views

Linked tables versus stored procedure performance in MySQL

Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form). Option 1 is to ...