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.
22
votes
1answer
459 views
In SQL Server, what is the purpose of grouping stored procedures?
One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
21
votes
4answers
1k 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 ...
11
votes
3answers
632 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
...
11
votes
1answer
203 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 ...
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 ...
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.
...
10
votes
3answers
2k 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 ...
10
votes
2answers
151 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.
...
9
votes
6answers
1k 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, ...
9
votes
1answer
1k 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 ...
9
votes
1answer
3k views
Stored Procedure to return dynamically created table data
Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
8
votes
2answers
494 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 ...
8
votes
4answers
494 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 ...
8
votes
3answers
257 views
Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?
I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.
I am not a real DBA, I'm a software developer who is ...
8
votes
1answer
3k 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 ...
8
votes
2answers
753 views
How to delete a procedure which name is ambigous?
I am using informix...
I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a
DROP PROCEDURE myProc;
then i get a error message
...
7
votes
5answers
3k views
What are the differences between “Stored Procedures” and “Stored Functions”?
So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL.
The comment links to a wikipedia article but some of this don't ...
7
votes
3answers
2k views
What is the best method to add error handling in SQL 2005 stored procs?
What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?
Additionally, whats the best way to handle multiple error scenarios in a stored ...
7
votes
6answers
703 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 ...
7
votes
3answers
341 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 ...
7
votes
3answers
187 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 ...
7
votes
3answers
2k 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
2answers
1k 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
4answers
2k views
Do SQL Server stored procedures cache data results?
I have heard this from friends, but I never investigated whether this is true.
Is it true that the data results of an executed query are stored in cache?
I mean, if I have a stored procedure like :
...
6
votes
2answers
466 views
Parameter Sniffing: Why Does This Become An Issue?
Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds).
After suspecting ...
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 ...
6
votes
1answer
265 views
CATCH block is triggered when it should not
On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of ...
6
votes
2answers
137 views
How to query the names of encrypted stored procedures?
What query will identify the names of stored procedures which are encrypted? Here's what I got so far -
select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need ...
6
votes
2answers
487 views
Most efficient way to return multiple aggregates in a single stored proc?
What is the best or most efficient way to get multiple aggregate result values?
Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
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 ...
6
votes
2answers
343 views
SQL Server database : moving from 2008 to 2005 - encrypted procedure issue
I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.
Neither do I have older ...
6
votes
1answer
2k 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?
6
votes
2answers
2k 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 ...
5
votes
3answers
668 views
Passing in the table into a stored procedure
Is it possible to pass in the name of a table into a stored procedure?
For example, suppose you have several views of the same table. They all have the exact same structure.
You want a store ...
5
votes
3answers
3k 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;)
5
votes
2answers
371 views
PostgreSQL procedural languages - differences between PL/pgSQL and SQL
Can anybody please summarize the differences between:
http://www.postgresql.org/docs/9.1/static/xfunc-sql.html
and
http://www.postgresql.org/docs/9.1/static/plpgsql.html
?
Main points:
...
5
votes
2answers
1k 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 ...
5
votes
2answers
8k views
Granting Execute Permissions on Stored Procedures, Functions and Views
I am using a user which has read permission only. It basically has permission to
Connect
Execute
However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
5
votes
3answers
396 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 ...
5
votes
2answers
873 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 ...
5
votes
2answers
180 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
5
votes
3answers
205 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 ...
5
votes
2answers
181 views
Why you want to avoid Dynamic SQL in stored procedure?
I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's ...
5
votes
3answers
258 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 ...
5
votes
2answers
95 views
Will a SP which writes records fail if accessed by a db_datareader role?
I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that.
Recently there ...
5
votes
2answers
6k views
How to use prepared statements within MySQL stored procedures?
I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
5
votes
2answers
795 views
How can I detect broken stored procedures after a schema change?
I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.
...
5
votes
2answers
61 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 ...
5
votes
1answer
205 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 ...
5
votes
1answer
251 views
Why does the order of parameters matter for sp_trace_create?
The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:
...
