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.
2
votes
1answer
172 views
returning set of records in stored procedure
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:
create or ...
1
vote
1answer
306 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;
...
5
votes
3answers
207 views
Create procedure in else block on SQL Server
I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things.
I was able to generate ...
0
votes
0answers
48 views
Returning multiple resultset from the same table
i am working with mysql procedure. final result set i have got in to one table.
now i need to return for each set i need to return the value.
example:
like this is contents in my the table.
i need ...
3
votes
1answer
99 views
PostgreSQL 8.4: How to tell if a procedural language is installed or not?
I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the ...
8
votes
4answers
501 views
Can I launch a stored procedure and immediately return without waiting for it to finish?
We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.
I have a second stored procedure that should be run after ...
1
vote
1answer
59 views
stored procedure examples
I am wondering if there are examples of open source apps that are written in stored procedures? I read a lot about them in textbooks and would like to see how they are used in practice beyond ...
4
votes
1answer
126 views
Stored procedure performance metrics
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by ...
4
votes
2answers
193 views
Stored Procedure as Create Table proxy?
With SQL Server it was my understanding, I thought, that you could elevate a users permissions with specific constraints.
For example, you need a user to create a specific table object in a specific ...
1
vote
2answers
51 views
MySQL: Adding Routines to a Slave
While setting up the MySQL replication, I took the mysqldump (without--opt --routinesoptions) unaware of the fact that in mysqldump, --routines it is NOT enabled by default.
Questions
Is there any ...
0
votes
2answers
173 views
Migrate DB2 UDB (Iseries) to SQL Server
I'm looking for a tools that allows migration of DB2/UDB databases to SQL Server.
It's quite easy migrate schema and data using linked server or import export with SISS.
But what about stored ...
0
votes
1answer
146 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
1
vote
2answers
192 views
Using MySQL EXPLAIN to analyze the performance of a function
You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
0
votes
1answer
106 views
How do I check if a stored procedure is defined in MySQL
When I do :
select routine_definition
from information_schema.routines
where routine_name = 'sp_name';
it returns a row, but the routine_definition column is NULL. Does this mean that this Stored ...
1
vote
0answers
206 views
How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?
This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
1
vote
2answers
250 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');
...
6
votes
3answers
195 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
84 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 ...
1
vote
1answer
68 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 ...
6
votes
2answers
484 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 ...
3
votes
1answer
58 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
71 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
141 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
208 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
62 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
188 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
474 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 ...
0
votes
1answer
443 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.
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
162 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
2answers
166 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
82 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 ...
0
votes
2answers
339 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
77 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 ...
5
votes
1answer
207 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 ...
0
votes
1answer
100 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 ...
11
votes
2answers
1k 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
214 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
81 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
2k 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
155 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
206 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
376 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
172 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
93 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
200 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
179 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
440 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 ...
0
votes
0answers
208 views
MySQL: Get info from SHOW ERRORS
How can I get the info (code and message fields) from SHOW ERRORS? Or even, how can I insert this information into a table to keep tracking the errors?
Something like
SELECT CONCAT(Code,' - ', ...
0
votes
1answer
290 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 ...
