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

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
527 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
219 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
5k 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
307 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
30 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
276 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
335 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
808 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
4k 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
485 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
393 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
1k 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 ...
5
votes
2answers
3k 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 ...
4
votes
1answer
8k 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
72 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
97 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
82 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
174 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
115 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
1k 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
208 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
896 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
463 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
2answers
3k 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 ...
4
votes
4answers
17k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
2
votes
1answer
2k views

Behavior of Varchar with spaces at the end

When I use a Varchar with spaces it ignores the spaces at the end. ex: declare @X varchar(50) This... set @X= 'John' ...is the same as... set @X= 'John ' It considers these to be ...
4
votes
1answer
1k views

How Can I Correctly Pass a Table to a Stored Procedure in Oracle in SQL Plus?

Being new to Oracle, I'm almost certain I'm missing something simple here. This is what I'm trying to run: declare type string_table IS TABLE OF VARCHAR2(512); outuser nvarchar2(512); outrole ...
1
vote
1answer
1k views

What is the Best Way to detect if server is a Master inside a Stored Procedure?

I'm want to create a stored procedure/event to do some maintenance tasks. My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be ...
1
vote
3answers
2k views

BACKUP failed to complete - with spaces, for no reason

Every day my database backup fails for some databases and does fine for others. I'm calling the backup through a Stored Procedure that runs scheduled in a job. Tonight I'm going to run the procedure ...
3
votes
2answers
3k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
8
votes
2answers
2k 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 ...
2
votes
2answers
2k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
6
votes
4answers
3k 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 : ...
1
vote
2answers
151 views

SQL Server RandomNumbers Stored Procedure Error

I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record. CREATE PROCEDURE Update_Driver_Position @TempName ...
3
votes
1answer
203 views

Possible to add KEEPFIXED PLAN option to reduce the recompile in SqlServer2k5 on Production Env?

I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env? I'd know if we may gain performance after that or on the other hand it will be ...
4
votes
3answers
4k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
2answers
6k views

CASE ORDER BY with multiple columns, and different sort options

I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT. @Orderby = 1 Then Date column should be ASC @Orderby = 2 Then Date column ...
2
votes
1answer
336 views

Virtual Column - Using it in view (Oracle)

I have made a virtual column in Oracle, using the following code. CREATE OR REPLACE function email_address (ID_ varchar2) return varchar2 deterministic as lname varchar2 (256); snumber varchar2 ...
10
votes
6answers
3k 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, ...
1
vote
2answers
2k views

Print database_size from sp_spaceused

Using SQL Server 2008, I have found that the database size can be obtained using exec sp_spaceused This procedure returns two tables: database_name | database_size | unallocated space reserved | ...
6
votes
1answer
24k views

How does MySQL return a result set from a stored procedure?

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that ...
16
votes
4answers
5k 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 ...
3
votes
2answers
2k views

How to optimize this dynamic stored procedure

I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure: CREATE PROCEDURE GetEnquiries ( @EnquiryDate DATETIME = NULL ) AS ...
4
votes
1answer
445 views

Kill CLR stored procedure process

I have a CLR stored procedure which exports a large table into a text file. The procedure works fine but. When I manually stop its execution from SQL Management Studio I cannot delete the file it ...
1
vote
2answers
1k views

Is there a way to find out how many times a stored procedure was invoked since last DB start

I am currently in a fairly mismanaged environment, where stored procs were introduced to the database over a few years and some versions became obsolete. Is there an easy way to figure out (preferably ...
3
votes
1answer
685 views

Programmatically copy index from one table to another, via a stored procedure?

I use a stored procedure to copy the tables from one database to the other database. Right now it does insert into copy select * from original I would like to copy the index from the original ...
2
votes
2answers
16k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
7
votes
4answers
287 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...