Questions tagged [sql-function]
A function defined in a relational database system. Most RDBS's include numerous built-in functions and also allow for the creation of additional custom functions.
918
questions
996
votes
19
answers
788k
views
Function vs. Stored Procedure in SQL Server
When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
- 80.8k
864
votes
12
answers
1.3m
views
How to filter Pandas dataframe using 'in' and 'not in' like in SQL
How can I achieve the equivalents of SQL's IN and NOT IN?
I have a list with the required values. Here's the scenario:
df = pd.DataFrame({'country': ['US', 'UK', 'Germany', 'China']})
...
- 76k
223
votes
9
answers
640k
views
How to check date of last change in stored procedure or function in SQL server
I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio).
I found that in SQL Server 2000 it wasn't ...
- 9,664
206
votes
7
answers
133k
views
MySQL procedure vs function, which would I use when?
I'm looking at MySQL procedures and functions. What is the real difference?
They seem to be similar, but a function has more limitations.
I'm likely wrong, but it seems a procedure can do everything ...
- 7,505
159
votes
6
answers
192k
views
How to check if a function exists in a SQL database?
I need to find out if a function exists in a database, so that I can drop it and create it again. It should basically be something like the following code that I use for stored procedures:
IF EXISTS (
...
- 2,300
127
votes
3
answers
158k
views
MySQL: Selecting multiple fields into multiple variables in a stored procedure
Can I SELECT multiple columns into multiple variables within the same select query in MySQL?
For example:
DECLARE iId INT(20);
DECLARE dCreate DATETIME;
SELECT Id INTO iId, dateCreated INTO ...
- 3,510
116
votes
4
answers
60k
views
How to create a temporary function in PostgreSQL?
I have to execute a loop in database. This is only a one time requirement.
After executing the function, I am dropping the function now.
Is there any good approach for creating temporary / disposable ...
- 1,327
95
votes
4
answers
46k
views
Table-Valued Function(TVF) vs. View
What's the difference between table-valued functions and views? Is there something you can do with 1 that's hard or impossible to do with the other? Or does the difference lie in efficiency?
- 13.8k
54
votes
10
answers
257k
views
Select Rows with id having even number
I am passing a simple query where I am searching for specific rows where OrderID is an even number
SELECT *
FROM Orders
WHERE mod(OrderID,2) = 0;
Error :
Syntax error (missing operator) in ...
- 541
52
votes
6
answers
186k
views
SQL Query - Concatenating Results into One String [duplicate]
I have a sql function that includes this code:
DECLARE @CodeNameString varchar(100)
SELECT CodeName FROM AccountCodes ORDER BY Sort
I need to concatenate all results from the select query into ...
- 26k
40
votes
2
answers
24k
views
SQL function return-type: TABLE vs SETOF records
What's the difference between a function that returns TABLE vs SETOF records, all else equal.
CREATE FUNCTION events_by_type_1(text) RETURNS TABLE(id bigint, name text) AS $$
SELECT id, name FROM ...
- 124k
38
votes
12
answers
190k
views
How to Replace Multiple Characters in SQL?
This is based on a similar question How to Replace Multiple Characters in Access SQL?
I wrote this since sql server 2005 seems to have a limit on replace() function to 19 replacements inside a where ...
- 2,060
38
votes
1
answer
55k
views
pass parameter in table valued function using select statement
I have created a table valued return function which returns me a table . here is call of my function as follow
SELECT * FROM dbo.[StateFixedTaxesCalculation](3020,16,1,1006)
and its working OK for ...
- 10.7k
35
votes
4
answers
61k
views
INNER JOIN with Table-Valued Function not working
I have a table valued function that returns a table. When I try to JOIN the table-valued function with another table I don't get any results, but when I copy the result of the function into an actual ...
- 1,015
32
votes
5
answers
132k
views
Calling Scalar-valued Functions in SQL
I have migrated a database from oracle, and now have a few Scalar-valued Functions.
However, when I call them, I get an error saying:
Cannot find either column "dbo" or the user-defined function ...
- 6,487
29
votes
4
answers
39k
views
Entity Framework 6 Code First function mapping
I want integrate Entity Framework 6 to our system, but have problem.
I want to use Code First. I don’t want to use Database First *.edmx file for other reasons.
I use attribute mapping [Table], [...
- 291
27
votes
1
answer
30k
views
What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?
In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which?
In this very ...
user3284469
21
votes
3
answers
17k
views
Error 6046: Unable to generate function import return type of the store function
I have a scalar-valued function in my SQL database.
I receive this error when importing this function into Entity Framework model:
Error 6046: Unable to generate function import return type of the ...
- 3,815
20
votes
5
answers
74k
views
How to create Daylight Savings time Start and End function in SQL Server
I need to create a function in SQL server that returns daylight savings time start datetime and daylight savings time end datetime.
I've come across a few examples on the web, however they all are ...
- 309
19
votes
3
answers
69k
views
Why MySQL is giving error "Not allowed to return a result set from a function"?
I am trying to create a MySQL function using phpMyAdmin and getting this error.
#1415 - Not allowed to return a result set from a function
The function code is as below:
DELIMITER $$
CREATE ...
- 3,825
18
votes
6
answers
45k
views
Incompatible object type when create and alter a table value function in SQL
I'm getting the below error for the given function.
Msg 2010, Level 16, State 1, Procedure GetTableFromDelimitedValues, Line 2
Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is ...
- 607
17
votes
8
answers
50k
views
Why do SQL Server Scalar-valued functions get slower?
Why do Scalar-valued functions seem to cause queries to run cumulatively slower the more times in succession that they are used?
I have this table that was built with data purchased from a 3rd party.
...
- 6,870
16
votes
4
answers
21k
views
PostgreSQL function or stored procedure that outputs multiple columns?
Here is what I ideally want. Imagine that I have a table with the row A.
I want to do:
SELECT A, func(A) FROM table
and for the output to have say 4 columns.
Is there any way to do this? I have ...
- 3,259
15
votes
3
answers
10k
views
SQL: Last_Value() returns wrong result (but First_Value() works fine)
I have a table in SQL Server 2012 as the snapshot shows:
Then I'm using Last_Value() and First Value to get AverageAmount of each EmpID for different YearMonth. The script is as follows:
SELECT A....
- 1,117
13
votes
3
answers
76k
views
SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
is there mySQL function to convert a date from format dd.mm.yy to YYYY-MM-DD?
for example, 03.09.13 -> 2013-09-03.
- 814
13
votes
3
answers
48k
views
How to call a recursive function in sql server
I have a table as follows
cat_id Cat_Name Main_Cat_Id
1 veg null
2 main course 1
3 starter 1
4 Indian 2
5 mexican 2
6 tahi 3
7 ...
- 3,660
12
votes
4
answers
18k
views
Registering a SQL function with JPA and Hibernate
I would like to know what's the best way to register a custom SQL function with JPA/Hibernate.
Do I have to go through extending the MysqlInnodb dialect or is there a better way?
Can anyone please ...
- 24.1k
11
votes
4
answers
94k
views
How do I create a SQL Server function to return an int?
I'm trying to create a SQL Function that tests whether a parameter starts with a certain term or contains the term, but doesn't start with it.
Basically, if the parameter starts with the term, the ...
- 41.7k
10
votes
7
answers
12k
views
Alter SQL Function Referenced by Computed Column
If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column ...
- 10.3k
10
votes
3
answers
7k
views
Database Function VS Case Statement
Yesterday we got a scenario where had to get type of a db field and on base of that we had to write the description of the field. Like
Select ( Case DB_Type When 'I' Then 'Intermediate'
...
- 3,052
9
votes
2
answers
11k
views
How do I create custom sequence in PostgreSQL based on date of row creation?
I am in the process of replacing a legacy order management application for my employer. One of the specs for the new system is that the order numbering system remain in place. Right now, our order ...
- 1,297
9
votes
2
answers
23k
views
SQL Server execute (sp_executesql ) command in SQL function
I created a function in SQL Server to execute a dynamic SQL query and return a value. I'm getting error when calling function in SQL query:
Only functions and some extended stored procedures can be ...
- 3,742
9
votes
2
answers
3k
views
Function executes faster without STRICT modifier?
I stumbled upon a slump in performance when a simple SQL function is declared STRICT while answering this question.
For demonstration, I created two variants of a function ordering two elements of an ...
- 632k
8
votes
1
answer
82k
views
divide the value of a column by another column
I want to divide a value of a column by the value of another column and display the result as separate column
ex:
project name total-percent no.of screen
------------ --...
8
votes
8
answers
53k
views
Why can we not execute a stored procedure inside a function in SQL Server
Why can we not execute a stored procedure inside a function when the opposite is possible?
- 139
8
votes
4
answers
6k
views
Should SQL functions be uppercase or lowercase?
I am new in SQL and I was wondering what is the right way to write the functions. I know the norm for statements like SELECT is uppercase, but what is the norm for functions? I've seen people write ...
- 247
8
votes
2
answers
12k
views
EF 6.1 Scalar-Valued Functions Database First
My application is c# MVC5, using EF 6.1. Imported tables and functions using Database First. I can see the function in model (emdx) browser listed under DALModel.Store / Stored Procedures / ...
- 2,295
8
votes
2
answers
7k
views
Show function definition in MySQL
I am working with existing project, and I am found a sql query with sql-function like
SELECT * FROM money WHERE amount = float_convert(0.1);
This Query is Working Properly, But I want to see and ...
- 85
8
votes
3
answers
8k
views
Using function result in WHERE clause in PostgreSQL
I am trying to use result of function execution in where clause but with no success:
SELECT clinics.*, distance_between_objects(1, id, 7, 3) AS dist FROM clinics WHERE dist<=1;
gives me:Column "...
- 584
8
votes
2
answers
8k
views
SQL Lead and Lag functions from C# code
Is it possible to use the LEAD or LAG SQL functions from C#?
My preference of method is:
Linq to SQL
Entity Framework
Dynamic Linq
SQLFunctions class
Manual TSQL via a SQLCommand
I know it can be ...
- 2,632
7
votes
3
answers
21k
views
ExecuteScalar always returns null when calling a scalar-valued function
Why does this return null?
//seedDate is set to DateTime.Now; con is initialized and open. Not a problem with that
using (SqlCommand command = new SqlCommand("fn_last_business_date", con))
{
...
- 63.6k
7
votes
3
answers
10k
views
Are execution plan for functions cached in SQL server?
Can any body help me in understanding if the execution plan for functions cached in SQL server?
Any online resource for this?
- 7,908
7
votes
2
answers
25k
views
How can i cut the left part of the string with unknown legth? (with sql function)
In the ETL process, I receive a varchar field, and the length (of the value) is changed from row to row.
I need to keep 5 symbols from the right side of the string.
It means that I need to cut the ...
- 73
7
votes
2
answers
85k
views
Return multiple values from PL/SQL Function
I need to try and find a free seat on a plane, and then reserve that seat.
I need to return both a value to indicate success or not as well as a seat number. By looking around I've found that ...
- 5,619
7
votes
3
answers
15k
views
Concatenating distinct column values in SQL Server
I am trying to concatenate many columns and separating it with a comma as below:
Column
------
abc
bcd
bgd
abc
Expected output: abc,bcd,bgd
I am using this code:
CREATE FUNCTION concatinate(@PK ...
- 299
7
votes
2
answers
9k
views
Very Slow SQL Server Query using Functions
This sort of follows on from a previous question:
SQL Server Query time out depending on Where Clause
In which a query would run extremely slowly depending on a where clause. I rewrote that query ...
- 2,331
7
votes
3
answers
9k
views
Azure Pipelines - CI/CD: How to execute all .sql files in a folder against DB
I have all the SQL files checked into a repo on Azure devops.
I have naming convention that allows me to know which .sql files are used by other .sql files (ex. file creates a view that is used by a ...
- 131
6
votes
2
answers
6k
views
Returning table from table-valued function and set that value in temp table
I have a table valued function. Also I have scalar valued function within I have declared a temp table. I want to execute the table valued function inside of scalar valued function and set that value ...
- 1,251
6
votes
1
answer
3k
views
How to generate SQL function calls with the CakePHP query builder?
I have a fullname column for authors and would like to extract the surname into another column. I do that with the following raw SQL:
SELECT name,
SUBSTRING_INDEX(`name`, ' ', -1) AS `surname`
FROM ...
- 83
5
votes
3
answers
26k
views
TSQL Error: A RETURN statement with a return value cannot be used in this context
I am just trying to create a function that returns a select statement, but it gives the error:
A RETURN statement with a return value cannot be used in this context.
This is my code:
CREATE ...
- 1,087