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

“can't return a result set in the given context” error when calling stored procedure - differences between mysql 5.0 and 5.5?

I have a stored procedure to regenerate leafs (nleft, nright) in simple (id, id_parent) tree. (sample table) When I'am calling CALL EdgeListToNestedSet( 'category', 'id_category', 'id_parent'); ...
7
votes
3answers
2k views

Store a formula in a table and use the formula in a function

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
2
votes
1answer
1k views

Oracle Packages, Procedures and Functions Not Respecting Roles

When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
2
votes
1answer
288 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
11
votes
2answers
7k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
4
votes
1answer
73 views

SP for Restoring Database and performing other tasks on it

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for ...
0
votes
2answers
151 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
1
vote
1answer
298 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 ...
0
votes
2answers
3k views

How can I change collation for all tables and columns in MySQL database?

I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, ...
3
votes
2answers
88 views

With the advent of integrated source control, do stored procedure changes still need comments?

Back in the old days, my team had a policy that any stored procedure modification required comments in two places: Top of the stored procedure with an overall summary of the changes made Comments ...
2
votes
3answers
1k views

Verify all Stored Procedures

How can I verify all stored procedures in a SQL Server database? I need to verify if all stored procedures still work, after some tables/views etc. have been deleted or changed.
1
vote
3answers
720 views

Support both stored procedures and functions with same TSQL code base?

We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends. Given the following ...
1
vote
1answer
672 views

How to split the comma separated list in the stored procedure

I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
3
votes
1answer
12k 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 ...
4
votes
1answer
4k views

Error in restore database

I use this query in SQL Server 2008 R2 to restore DB1 database: RESTORE DATABASE [DB1] FROM DISK = N'D:\new.bak' WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10; The following error ...
4
votes
1answer
322 views

Delete all rows within transaction: what would happen to other transactions?

I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
1
vote
0answers
142 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
2
votes
3answers
6k views

How to avoid using the IN clause in sql query used in a stored procedure

Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the ...
2
votes
1answer
127 views

Where to put utility (not administration) stored procedures in SQL Server? [duplicate]

Possible Duplicate: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? I have some simple user-defined functions ...
6
votes
2answers
1k 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 ...
1
vote
1answer
205 views

MySQL user defined rollback procedure

I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more ...
13
votes
2answers
16k 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 ...
1
vote
1answer
769 views

How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008 I have a table that I am trying to use as a storage container. It has 3 columns: reportID integer, dataLabel ...
1
vote
1answer
202 views

Binding errors with dependent stored procedures

We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
5
votes
2answers
9k views

Separate stored procedures for inserts and updates?

I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures: InsertNewPerson UpdatePertsonById But I was thinking to write ...
2
votes
1answer
751 views

Only a single WITH MOVE clause should be specified for any logical file name

I'm trying to do a copy database by scripting a backup and restore. I have the backup procedure working correctly but I keep getting the error Conflicting file relocations have been specified ...
11
votes
1answer
2k views

Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler?

Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a ...
0
votes
2answers
8k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
2
votes
1answer
1k views

How to check parameter value existence in a procedure?

The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking: ...
0
votes
2answers
159 views

Understanding the use of Sql command parameters

I am using MySql database and I have a stored procedure to insert data, like this- CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`( IN id varchar(10), IN pwd varchar(10), IN fname ...
3
votes
1answer
859 views

“Thread was being aborted” error when inserting 25k rows

I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a ...
1
vote
1answer
1k views

Another BCP Export [Fail]ure

All, in SELECT Data Set Using Stored Procedure with BCP Error @Remus Rusanu provided a great solution to the problem. However, this evolved in to another problem: this BCP query now runs DECLARE ...
1
vote
3answers
2k 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 ...
2
votes
1answer
1k views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
1
vote
1answer
157 views

Stored procedures and network congestion

When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
2
votes
2answers
4k views

Oracle Procedure to Enable/Disable all constraints

I have this nice piece of code: begin for i in ( select constraint_name, table_name from user_constraints where constraint_type ='R' and status = 'ENABLED' ) LOOP execute immediate ...
1
vote
1answer
141 views

oracle how to call procedure

This is very basic question, but I couldn't find an answer here... So I have this very useful procedure without any parameters: CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS IS v_statement ...
2
votes
1answer
62 views

Is it possible to determine what part of a stored procedure is currently executing?

Say I have a stored procedure like this (assume that the table schemas are fine): CREATE PROCEDURE p_MyProc AS INSERT MyTable SELECT Col1 FROM Table1 INSERT MyTable SELECT Col2 FROM Table2 INSERT ...
1
vote
1answer
3k views

MySQL: how to optimize the stored procedure?

MySQL 5.5.28 Running pt-query-digest on a slow query log, I get something like this: # 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # ...
2
votes
1answer
686 views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
4
votes
1answer
3k views

How can I create a stored procedure using xp_fixeddrives?

I need a stored procedure to check the free space in 2 linked servers .(the servers are linked manually) .I have comed up with a query like this and using sql server 2008 ALTER PROCEDURE freespace AS ...
1
vote
1answer
1k views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
8
votes
3answers
2k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
0
votes
1answer
2k views

Calling stored procedure with select and update statement

I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like create procedure testproc as select ...
2
votes
2answers
2k views

Event Scheduler: No data - zero rows fetched, selected, or processed?

MySQL 5.5.28 The error log keeps getting this error: 121004 12:08:59 [ERROR] Event Scheduler: [user@ip][reportingdb.tvc_bookingonline_update_status] No data - zero r ows fetched, selected, or ...
26
votes
4answers
3k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
2
votes
0answers
230 views

Stored procedure doesn't return results or any messages when executed as other privileged user [closed]

I have many stored procedures with... EXECUTE permission given to role XXXUser. Login XXXLogin is created and assigned to the role of XXXUser There is one particular stored procedure that I have ...
4
votes
1answer
775 views

Detecting if bit set in numeric value

I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?
1
vote
1answer
174 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 ...
6
votes
2answers
6k views

mysql.proc keeps crashing, cannot do a mysqldump?

Due to some problems with InnoDB, I'm going to dump all databases to a new server: mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server The dump process stopped with ...