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
22 views
Procedure after commit
I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
0
votes
1answer
39 views
Stored procedure with and without preparing statement
I have 2 stored procedures:
CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
...
0
votes
0answers
21 views
developing procedure in sql for global use
what type of complex procedure can i develop in SQL which will make others work easy as well ,
like in Java , i had developed a program for FTP so that anyone can import this program and can use it ...
-1
votes
1answer
44 views
Use select query in stored procedure
I have some doubt about stored procedure.
Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast Or if have ...
3
votes
2answers
67 views
How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION ...
0
votes
0answers
44 views
DBA_SCHEDULER_Job is not giving any result
I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering.
select job_name,status from dba_scheduler_jobs;
...
1
vote
2answers
51 views
Same Parameter in MySQL Stored Procedure
How do I cache dynamic query from store procedure?
Right now I have created my store procedure like this :
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @sqlQuery = ...
3
votes
0answers
33 views
SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows
So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:
ALTER PROCEDURE ...
2
votes
2answers
62 views
The multi-part identifier “xxx” could not be bound
The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);
HAVING (SELECT COUNT(*) FROM
(
SELECT *
FROM ...
2
votes
2answers
74 views
How to create a mysql stored procedure through linux terminal
In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the command delimiter for only the mysql client(client side ...
0
votes
1answer
39 views
Postgres - Schema information within Trigger?
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).
Reviewing patterns for the following problem: I ...
1
vote
1answer
31 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
0
votes
1answer
39 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
0
votes
2answers
97 views
From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads
I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0.
Now I am using features of SQL Server like:
Common Table Expressions (I have a table ...
0
votes
1answer
34 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment i have decided to create separate database just for stored routines storage. Mainly i am using them for reporting. ...
2
votes
2answers
79 views
Executing a table type parameter stored procedure or function within a select clause
Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
-2
votes
1answer
27 views
Following procedure generates an error and I cannot find the reason [closed]
create or replace procedure buyunits is
mnav number;
mpno varchar2(100);
mtransactioncharge number;
mservicecharge number;
maccno number;
cursor ecursor is
select accno from sipholder where ...
1
vote
1answer
47 views
MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host
We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication.
"'Column count of mysql.proc is ...
0
votes
1answer
42 views
Different Results between stored proc and udf
I am getting different results between my stored proc and my SVF.
The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, ...
5
votes
1answer
82 views
Best practice for copying tables from server to server
Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)
...
0
votes
1answer
63 views
PostgreSQL stored procedure to return rows or empty set
I have stored procedure, which RETURNS SETOF ct_custom_type and inside I do
RETURN QUERY EXECUTE 'some dynamic query'
And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
0
votes
2answers
55 views
PostgreSQL stored procedure which can return one of two custom rowtypes
I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:
CREATE OR REPLACE FUNCTION ...
3
votes
0answers
27 views
PLSQL : DBMS Jobs - parallelization?
I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?
for rec in select column from table1
loop
execute stored_procedure( rec.column );
end ...
3
votes
1answer
53 views
Is it possible to use Oracle XMLTABLE and %type column type selector?
I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.
I want to know how can i specify a field type according to a table column type when passing ...
0
votes
0answers
23 views
Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.
create or ...
0
votes
2answers
55 views
How to Drop Tables using a variable in SQL Server?
DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.
WHY?:
In my Access Database I am running a ...
0
votes
1answer
90 views
What is the best way to insert a XML input to an oracle table in stored procedure?
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:
<rowset>
<row>
...
0
votes
2answers
38 views
Why does this procedure raise a privilege error?
I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.
QUESTIONS
Could somebody help ...
0
votes
1answer
114 views
Stored Procedure Create Table from Variable with Variable Constraint
I have managed use a store procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure.
The problem:
The ...
3
votes
5answers
222 views
Stored Procedures under Source Control, best practice
I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
0
votes
1answer
92 views
Error: set_valued function called in context that cannot accept a set. What is it about?
I use Postgresql 9.1, with ubuntu 12.04.
Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
1
vote
1answer
62 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
0
votes
1answer
25 views
Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?
In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth.
For example:
create or replace function ...
1
vote
1answer
63 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
0
votes
1answer
77 views
Cannot `create function` in plpython3u, permission denied
As postgres user, I have create extension plpython3u; in my database
then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true
but ...
0
votes
1answer
59 views
Cannot use python3 as stored procedure language in posgresql database
I want to have python3 in my postgresql database for writing stored procedures.
Being in the psql client, when I enter the command create extension plpython3u I get the error:
couldn't open ...
0
votes
0answers
36 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
0
votes
0answers
55 views
Stored Procedures and Functions in MySQL
I just need to know about Stored Procedures and Functions in MySQL.
While I designing a database for an eCommerce website, I was in a situation to write stored procedure for my database, this is new ...
1
vote
1answer
52 views
Custom sp_who/sp_whoUsers
I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
1
vote
1answer
443 views
How to return resultset from MySQL Stored Procedure using prepared statement?
DELIMITER $$
CREATE PROCEDURE List_IL()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Temp_List_val VARCHAR(255);
DECLARE Project_List_val VARCHAR(255);
DECLARE FoundCount INT;
...
5
votes
2answers
176 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
2
votes
2answers
98 views
EMS SQL manager permission problem
I have a strange problem with EMS SQL Manager.
I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:
GRANT CREATE ROUTINE, ALTER ROUTINE ON ...
4
votes
1answer
140 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
1answer
23 views
Two dichotomy views of a table, and means to express this as a stored procudure?
There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events.
How to express ...
0
votes
1answer
65 views
Difference between return next and return record
What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
2
votes
1answer
165 views
returning set of records in stored procedure
I would like to return a set of records from a pl/pgsql. Is there a way to do that without using the "for" construct and cursors? For instance when I compared these two stored procedures:
create or ...
1
vote
1answer
286 views
Declare variable of table type in PL/pgSQL
I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like:
q1 = select * from foo;
q2 = select * from bar;
...
5
votes
3answers
204 views
Create procedure in else block on SQL Server
I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things.
I was able to generate ...
