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.
3
votes
1answer
895 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 ...
2
votes
4answers
6k 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
-- ...
1
vote
1answer
739 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
334 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
379 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
2answers
1k 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
1k 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
750 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
1k 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
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 :
...
1
vote
2answers
102 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
127 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 ...
3
votes
3answers
2k 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
3k 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
236 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 ...
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, ...
1
vote
2answers
1k 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 | ...
4
votes
1answer
9k 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 ...
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 ...
3
votes
2answers
1k 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
319 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 ...
0
votes
2answers
554 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
521 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
6k 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)
...
4
votes
1answer
159 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 ...
1
vote
4answers
754 views
How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?
I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query.
Simply i can use ...
3
votes
3answers
337 views
Books on optimizing and refactoring SQL stored procedures
Can you please recommend resources to write efficient store procedures, refactor and optimize them?
EDIT: - jcolebrand ♦
The intent of the answers to this post may be books or non-transient blogs. ...
2
votes
0answers
174 views
Stored procedures over SQL in the code, good idea? [duplicate]
Possible Duplicate:
What are the arguments against or for putting application logic in the database layer?
Assuming I got over the problem of maintainability and debugging.
My assumption is ...
0
votes
1answer
2k views
work with json in oracle
Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
1
vote
1answer
2k views
Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5
I have upgraded MySQL version to 5.5.16
After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed.
In those functions, cursors are there, but ...
8
votes
2answers
493 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 ...
3
votes
3answers
887 views
Are there ways to only replace SQL Server stored procedures when the definition has changed?
I know the following two patterns to write scripts for creating stored procedures, which can be executed repeatedly without throwing errors.
if object_id('my_proc') > 0 drop procedure my_proc
go
...
2
votes
3answers
2k views
SQL Server: How to call stored procedure without schema name?
SQL Server 2005std/2008web edition
db=user=schema=owner='John'
I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.
Dim sqlCmdVehicle ...
1
vote
2answers
205 views
ORM-style server-side programming languages (OO replacement for PL/SQL?)
Good evening,
Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?
(for MySQL, PostgreSQL, Oracle or etc.)
0
votes
0answers
111 views
how do i bulk copy a huge amount of data and keep the same table updated through stored procedure in SQL SERVER [duplicate]
Possible Duplicate:
How to move data from big table
In my project i need to fetch datas from different tables in a data base and create a new table with the same colmn names for all those ...
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.
...
2
votes
1answer
545 views
SQL Server stored procedure permissions
I know that it's possible to set execute permissions for a stored procedure to a database role or user. My question is, how is access to any tables that the SP accesses determined? Does the ...
4
votes
1answer
355 views
Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?
In SQL Server I have set up transational replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to stored ...
5
votes
3answers
393 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 ...
0
votes
2answers
1k views
Hide column if any value is null or zero via proc sql or view [closed]
Given either of the two following datasets
Kağıt Cinsi Sarı Yeşil Mavi
Kalın 4 0 5
İnce null 0 3
or
Kağıt Cinsi ...
2
votes
1answer
145 views
stored procedure times out while subsequent runs take 1/6 the time
So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other ...
1
vote
1answer
2k views
Can mysqldump dump triggers and procedures?
Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?
Some time ago I read that mysqldump will also save my triggers, but it doesn't look like ...
3
votes
3answers
267 views
Same tables with same data, different SQL Server stored procedure performance
I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs ...
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 ...
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 ...
4
votes
1answer
7k views
PLS-00306 Error: How to find the wrong argument?
PLS-00306: wrong number or types of arguments in call to 'string'
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
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
...
2
votes
2answers
475 views
SQL 2005- stored procedure performance troubleshooting
I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:
Analyze the stored procedure for any bad TSQL practices (like count * etc)
Run it ...
0
votes
1answer
295 views
How to start and then stop one cyclic process
I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
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 ...
