1
vote
0answers
68 views

How to use the result of regexp_split_to_array() in stored procedure [closed]

I'm trying to write a function used by an trigger BEFORE INSERT on a table; the function should convert a raw GPS string to useable columns in a different table. The point where I get stuck is the ...
5
votes
2answers
159 views

Optimize queries from MySQL SP/Triggers/Functions

I have to investigate a MySQL Production server,While investigating I found a DELETE query in "SHOW PROCESSLIST" which was taking more than 400 second in preparing state, I tried to find the query in ...
1
vote
0answers
86 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
3answers
190 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
1
vote
1answer
1k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
2
votes
1answer
595 views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
7
votes
3answers
696 views

Store a formula in a table and use the formula in a function

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
0
votes
2answers
124 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
1
vote
3answers
558 views

Support both stored procedures and functions with same TSQL code base?

We have a need for both Microsoft SQL Server 2008 functions (ITVFs) and stored procedures to provide the same interface and functionality to end-user reporting frontends. Given the following ...
1
vote
1answer
397 views

How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

Ok, I know the title is confusing but here it goes. BTW I am using SQL Server 2008 I have a table that I am trying to use as a storage container. It has 3 columns: reportID integer, dataLabel ...
1
vote
1answer
804 views

MySQL - Create a function from another function

I want to create a function same like my old functions. When I create a table, I can use `AS to other table as follows: CREATE TABLE dummy2 AS SELECT * FROM dummy1; Can I do the same thing with ...
2
votes
2answers
437 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 ...
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 ...
3
votes
1answer
7k 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 ...
5
votes
2answers
14k 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 ...
2
votes
3answers
3k 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
0answers
774 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; .... ...