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

3
votes
3answers
985 views

What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?

I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some ...
4
votes
1answer
7k views

Show procedure code in MySQL

I've tried to show the SQL code of a stored procedure called add_pixel: mysql> SHOW PROCEDURE CODE add_pixel; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ...
2
votes
2answers
565 views

Error on Oracle calling external procedures

I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it. the C source(libcprog.c) is like this: int exec_hmmftg ...
4
votes
1answer
1k views

Find out what setting were used for a given stored procedure

As a follow-up to my previous question: is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ? It seems my ...
1
vote
1answer
152 views

transactional Replication of millions of updates

I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates ...
6
votes
2answers
633 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 ...
9
votes
3answers
1k 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 ...
2
votes
2answers
247 views

Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures

This question regards the proper use of NULL and utilizing CHECK constraints for business logic vs stored procedures. I have the following tables setup. I normalized the tables to avoid using ...
3
votes
1answer
2k views

Function/procedure to use dblink to fetch remote data and insert to multiple local tables

Am trying to fetch data from remote db and insert into three local tables. Currently I have a query which is successfully getting the data into one table. But now I was thinking of creating a ...
2
votes
2answers
929 views

Bulk grant execute permissions for scalar functions & stored procedures

We have a situation where we have restored a large number (50+) of MSSQL2008 databases from backup and most if not all of them require that a user is granted EXECUTE permissions on scalar functions ...
3
votes
1answer
108 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
10
votes
1answer
9k 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 ...
1
vote
1answer
706 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
2
votes
1answer
9k views

MySQL user permission on stored procedure

I've created a simple stored procedure: mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected, 1 ...
1
vote
0answers
443 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
0
votes
1answer
651 views

Need a stored procedure to check and update row when it does not already exists.

Need a stored procedure that will check and then update the rows (with username & email) except the id (auto incremented with a unique one). For example, if id = 5 has, the username abc and the ...
1
vote
1answer
614 views

How can I trigger this stored procedure on insert?

I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous ...
4
votes
2answers
10k views

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
6
votes
3answers
2k 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 ...
1
vote
1answer
749 views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
1
vote
1answer
3k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
6
votes
2answers
830 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 ...
4
votes
2answers
2k views

Using the correct database when calling a system stored procedure in SQL Server 2008

I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity): ...
4
votes
3answers
1k views

SQL Server stored procedure keeps restoring to previous one

There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure ...
6
votes
2answers
905 views

Stored Procedures Best Practices: Fenced or Unfenced?

I believe I understand the reasons behind fenced and unfenced stored procedures. Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
0
votes
1answer
1k views

Is there a way to skip errors in procedures and resuming procedure

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again. ...
2
votes
1answer
745 views

performance issues when concurrent requests

We are using SQL Server 2008 R2 on a Windows Server 2008 server. We have some main functions which uses some Stored Procedures which has a lot of sub-queries inside of it. I'll take one SP as an ...
7
votes
1answer
822 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: ...
0
votes
1answer
456 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
5
votes
2answers
884 views

Error handling in container procedures

I'm aware of and use the techniques in this question. In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum. For me ...
4
votes
2answers
10k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
2
votes
1answer
415 views

Stored Procedure doesn't show result in SQL Server

I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
3
votes
3answers
2k views

Best way to work around NOT IN NULL issue?

There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?) I have 6 ...
5
votes
1answer
1k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN ...
5
votes
2answers
18k 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 ...
1
vote
1answer
307 views

What can cause an UPDATE statement to execute endless? [closed]

I have a stored-procedure which is executed at 7 o'clock in the morning that updates the database because new records were imported at night. Today it started but never endet. So i've tried to run ...
1
vote
1answer
1k views

Executing a stored procedure on remote database

I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in ...
3
votes
2answers
396 views

How can I change three hundred procedures at once?

I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata. However the database has ...
5
votes
3answers
1k 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. ...
2
votes
1answer
631 views

How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization. I currently have around 160 procedures and about 30 functions. I am using the select benchmark() to check the ...
2
votes
1answer
243 views

Does the SQL Server optimizer inline function calls in stored procedures?

I'd like to factor out some of the functionality in our SPs into separate functions/stored procedures, but I'm worried about the potential performance penalty. In particular, how efficient will be a ...
5
votes
1answer
6k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
6
votes
2answers
418 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 ...
1
vote
0answers
31 views

What might cause all objects in an Oracle database to suddenly have quoted identifiers?

I have an Oracle schema in which, at some point in the past ("the event"), all of the procedural code objects in the database have been re-created with quoted identifiers. All of the objects are ...
2
votes
3answers
4k views

What's the difference between functions and stored procedures? [closed]

I heard that the difference between functions and stored procedures in SQL Server is that functions are not stored in the database while stored procedures are. Is that true? If not, what are the ...
1
vote
1answer
291 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
1
vote
1answer
365 views

use of signal for insert statement in mysql stored procedure

I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure: DROP PROCEDURE IF EXISTS `spItem`$$ CREATE PROCEDURE `spItem`( IN ...
1
vote
0answers
839 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
3
votes
1answer
5k views

Creating a table from variable names

Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement. CREATE ...
4
votes
1answer
519 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...