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.
0
votes
0answers
98 views
slow response from MySql server
We just moved our database from an old server to a new one with more processing power and 4G RAM. However, for some unknown reason, the execution of our store procedures became even slower. An ...
4
votes
2answers
4k 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 ...
5
votes
3answers
664 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
262 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
631 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
458 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
271 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):
...
0
votes
1answer
340 views
How to update another table using triggers in postgresql [closed]
I'm using PostgreSQL.
I've 2 tables in my schema.
One called taggings where I've (id, film_id, tag_id, user_id), all integers.
And another where I intend to sum tags, called film_tag_counts (id, ...
4
votes
3answers
324 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
1answer
404 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
292 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
269 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 ...
5
votes
1answer
245 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
231 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
429 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 ...
3
votes
1answer
3k 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
132 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
387 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 ...
4
votes
1answer
321 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
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 ...
1
vote
1answer
206 views
What can cause an UPDATE statement to execute endless?
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
573 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
269 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
2answers
792 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
217 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 ...
0
votes
0answers
301 views
Grouping a comma separated value on common data [closed]
I have a table with col1 id int, col2 as varchar(comma seperated value) and column 3 for assigning group to them.
Table looks like
col1 col2 group
..............................
...
2
votes
1answer
145 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 ...
4
votes
1answer
2k 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
135 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
28 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
2k 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
185 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
207 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
557 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
1k 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 ...
3
votes
1answer
311 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 ...
4
votes
3answers
184 views
Can I query stored procs in all databases with one query without dynamic sql
If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:
SELECT
'select ' + ...
1
vote
2answers
669 views
Should I put the error handler inside the loop, or the loop inside the error handler?
When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run ...
4
votes
2answers
788 views
How to Determine the input and output values of stored procedures?
I have hundreds of SPs and I would like to find out:
input parameters with type
output fields with type (not output parameters)
Of course I could manually go through each one and write it down but ...
2
votes
1answer
3k views
Table-Valued Parameter as Output parameter for stored procedure
Is it possibile to Table-Valued parameter be used as output param for stored procedure ?
Here is, what I want to do in code
/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
id int ...
0
votes
2answers
61 views
SQL Server Stored Procs not parameterized, leading to high compilations?
We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL. However, I noticed that in SQL Profiler our calls look like:
exec cid.dbo.uspCIDLog ...
2
votes
1answer
79 views
don't want to be able creating stored procedures with incorrect
Right now I can execute a CREATE PROCEDURE or an ALTER PROCEDURE script with incorrect table and column references in the MS SQL Server Management Studio and using ADO.NET commands. The scripts run ...
1
vote
1answer
59 views
SQL Server many active create procedure processes
While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures.
Is this ...
2
votes
1answer
136 views
Why evaluates “IS NULL” in SSMS interactive mode to “TRUE” but the same statement being executed in a SP to “FALSE”? [closed]
Given
DECLARE @IN__CUST_NO INT
DECLARE @OUT__USER_ID INT
SET @IN__CUST_NO = 1000006660
CASE 1: IS NULL evaluates TRUE
Since no CUSTOMER_NUMBER with the value of 1000006660 exists in our dummy ...
0
votes
1answer
96 views
TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs
I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs.
Each office has already imported data ...
1
vote
1answer
683 views
Heterogeneous queries error using stored procedure
Stored procedure
ALTER PROCEDURE Proc_DayShift
AS
insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
...
1
vote
1answer
125 views
Oracle, Stored and External procedures
Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures.
Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...
2
votes
2answers
443 views
SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?
I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not ...
2
votes
1answer
271 views
high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005
I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)
SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ...
3
votes
1answer
877 views
Is there a way to tell your hostname within a stored procedure?
Is there a way to determine the hostname of the server you're running in w/ in a stored procedure?
You can run system hostname from a client but you can't make system calls from SP. I don't see it ...
