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

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
5
votes
2answers
132 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 ...
4
votes
1answer
89 views

Is it possible to encapsulate a table in MySQL?

I am new to Databases. I want the following: UPDATEs in a database to happen only through a stored procedure The user does not have GRANT UPDATE The user has GRANT EXECUTE ON PROCEDURE I tried it ...
0
votes
0answers
142 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...
2
votes
1answer
1k views

How to modify a stored procedure using SQLCMD for MS SQL?

Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio. use testbase go create procedure testtable_pricesmaller @pricelimit money as select * from ...
5
votes
3answers
10k views

"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio

I have created a table testtable inside the database testbase that have the following structure: product_no (int, not null) product_name (varchar(30), not null) price (money, null) expire_date (date, ...
6
votes
1answer
442 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 ...
11
votes
2answers
2k 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: ...
0
votes
1answer
2k views

How to automatically regularly a week backup SQL Server database if changes exist in the database for the previous week

I´m new to SQL Server administration. I´d like to create automatically backup database every sunday night, if there was any change in previous week (if not, backup is unnecessary). Is there some way ...
6
votes
3answers
2k views

RETURN statement before DEALLOCATE CURSOR

Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure Consider this example: CREATE PROCEDURE [dbo].[test] @ReturnEarly BIT = 0 AS BEGIN SET NOCOUNT ...
6
votes
2answers
837 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 ...
3
votes
3answers
879 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 ...
3
votes
1answer
6k 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
493 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
625 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
150 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
574 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 ...
8
votes
3answers
730 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
235 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
789 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
7k 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
645 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
7k 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
354 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
424 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
556 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
9k 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
595 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
2k 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
761 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
1k 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
910 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 ...
5
votes
2answers
766 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
877 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
589 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
730 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
409 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
766 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
1answer
8k 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
322 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
1k 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
15k 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
290 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
376 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. ...