Use stored-functions for questions related to database specific user-defined functions which can perform queries, accept parameters, and return a computed value.
References
Questions tagged [stored-functions]
820 questions
121
votes
10 answers
SQL Server - where is "sys.functions"?
SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently.
What stumbles me is this: why is there a sys.procedures view to see info about your stored procedures, but there is no sys.functions view to see the same for your…
marc_s
- 732,580
- 175
- 1,330
- 1,459
97
votes
8 answers
View stored procedure/function definition in MySQL
What is the MySQL command to view the definition of a stored procedure or function, similar to sp_helptext in Microsoft SQL Server?
I know that SHOW PROCEDURE STATUS will display the list of the procedures available. I need to see a single…
Srinivas M.V.
- 6,508
- 5
- 33
- 49
66
votes
7 answers
Functions vs procedures in Oracle
What is the main difference between functions and procedures in Oracle?
Why must I use procedures if I can do everything with functions?
If I cannot call procedure in sql statement, ok, I'll write a function to do the same work.
Procedures don't…
0bj3ct
- 1,400
- 4
- 22
- 51
27
votes
7 answers
How to view a stored function - SQL Server
Version: SQLServer 8
I would like to view the contents of a stored function in sqlserver, i.e. what exactly the function is doing.
None of the options listed here work for me. There doesn't appear to be any database/table called sys.objects. I was…
etech
- 2,548
- 1
- 27
- 24
23
votes
8 answers
CREATE FUNCTION error "This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA"
Our database has a function to generate an order number. It reads a value from a Settings table, increments it, then returns the new value. For example:
CREATE FUNCTION NextOrderNumber() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC
BEGIN
DECLARE…
richb
- 4,716
- 5
- 24
- 22
20
votes
3 answers
How to use Table output from stored MYSQL Procedure
I've been looking for the last hour or so and haven't found a conclusive answer to this seemingly simple problem:
How do you call a stored MYSQL function/procedure and use its output in further SELECT queries?
Although this obviously doesn't work,…
Johannes
- 6,232
- 9
- 43
- 59
18
votes
5 answers
How to edit PostgreSQL stored procedure?
I'm slowly moving from MSSQL to PostgreSQL.
In MSSQL I could call editing of already saved procedure or function, and the administration shell (SQL Server Management Studio) showed me procedure's text, so I did not have to store its source code…
Paul
- 25,812
- 38
- 124
- 247
18
votes
1 answer
Why is it not recommended to use server-side stored functions in MongoDB?
According to the MongoDB documentation, it isn't recommended to use server-side stored functions. What is the reason behind this warning?
Carlos Melo
- 3,052
- 3
- 37
- 45
15
votes
2 answers
Search 'grep-alike' through PostgreSql functions
When refactoring PostgreSql functions (and more specific: while searching for 'unused' functions) it would be handy to have a function available to search for a specific string within the function definitions.
Does anyone know if this is the best…
ChristopheD
- 112,638
- 29
- 165
- 179
14
votes
5 answers
Compute 2,3 quartile average in SQL
I want to write a stored proc in SQL (MySQL) to compute the average of second and third quartiles.
In other words I have records for measurements for how long it takes for an URL to load. Records are (id,url,time) and they are many measurements for…
munch
- 2,061
- 2
- 16
- 23
13
votes
1 answer
Testing performance of Scalar vs Table-valued functions in sql server
OK so I have read a whole bunch of articles suggesting table-value functions and cross apply give better performance than a scalar udf. I wanted to write my function in both ways and then test to see which one is better - but I cannot figure out…
Jen
- 1,964
- 9
- 33
- 59
12
votes
1 answer
Stored procedure does not exist, even after creating it
I am trying to create a mysql stored procedure . I have successfully created a procedure using the following code :
delimiter $$
CREATE PROCEDURE `myprocedure` (IN
var1 DATE)
BEGIN
<---code-->
END
And
SHOW CREATE PROCEDURE myprocedure
shows…
ashu
- 1,339
- 7
- 27
- 43
12
votes
2 answers
How to throw an error in MySql procedure?
What is the mechanism to force the MySQL to throw an error within the stored procedure?
I have a procedure which call s another function:
PREPARE my_cmd FROM @jobcommand;
EXECUTE my_cmd;
DEALLOCATE PREPARE my_cmd;
the job command…
Arman
- 4,566
- 10
- 45
- 66
11
votes
1 answer
Using variables as OFFSET in SELECT statments inside mysql's stored functions
I'm quite new to subject of writting stored function for mySQL database, hence i'm not sure if what i'm trying to do here is possible at all.
I need a function that will return a column from random row from a table. I don't want to use ORDER BY…
CountZero
- 2,844
- 4
- 22
- 20
11
votes
1 answer
Error Code: 1422. Explicit or implicit commit is not allowed in stored function or trigger
Everywhere I look it seems MySQL stored procedures can do transactions. Yet when I declare my stored function
create function test( a int )
returns int
MODIFIES SQL DATA
BEGIN
START TRANSACTION ;
update t set col='some value' where id=a ;
…
bobobobo
- 64,917
- 62
- 258
- 363