In this post, we learn about user-defined functions and their types ,creating and calling a scalar user define function, places where we can use these. And finally, how to alter and drop a user-defined function?.
We have learned how to use many of the built-in system functions that are available in the SQL Server in the previous article, Now in this post will turn our attention to creating user-defined functions.
Types of user-defined functions in sql server
User-define functions are also called UDS. In the sql server, There are three types of user-defined functions
- Scalar functions
- Inline table-valued functions
- Multi-statement table-valued functions.
In this post will concentrate on how to create scalar functions.
Now before in looking at how to create a scalar function, let’s look at how to use one of the existing system functions.
For example, we have SQUARE() function which is a scalar in-built system, so let’s try to use that. And to use that, we used to select a keyword and then SQUARE the name of the function itself.
select SQUARE(2)
when I hover the mouse on this function, look at the intelligence of SQL SERVER management studio, it shows that this function is expecting a float parameter and it returns a float data type, a float value.
So if we give it a number, for example, let’s say a pass in 2, so this function is going to square that function and return that the square back to us in the form of float.so when we execute this we get 4.
select SQUARE(2)
if you understand the functions, have the capability of taking in parameters, do some processing and return some values back.
Now, is it mandatory for a function to have a parameter? No, we have also looked at many functions, which doesn’t take parameters at all.
For example, if you take the GETDATE() method, which returns the current system daytime, it doesn’t take any parameter. Look at this. This function doesn’t expect any parameter
select GETDATE()
so a function can have parameters but a function should always return a value. so now let’s go in and see how to create a user-defined scalar function.
So what is a scalar function?
A scalar function is a function that takes zero or more parameters and returns a value. A function may or may not have parameters, but it should return a value. since a scalar function returns a single scalar value, it’s called scalar function.
To create a scalar function we use the following syntax –Create function function_name. Similarly to create a stored procedure we will use create procedure procedure_name.
Similarly to create a table we use create table table_name to create a function,create function function_name.
And then we know that a function can have parameters. So within the parentheses, you specify the name of the parameter and its data type.
CREATE FUNCTION Your_Function_Name(@InputParameter1 DataType, @InputParameter2 DataType,..@InputParametern Datatype)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END
you can have up 2024 parameters, and then a function will also return in value.So to specify what is the function going to return, we use the returns keyword and then the data type.
Now, remember, a scalar function can return almost any of the data types that we have, any of the scalar data types that we have except text and ntext image cursor and timestamp. A function cannot return these data types.
AS BEGIN just like the stored procedure, the body of the function goes inside this begin and end statements.
So let’s see how to create a user-defined function that computes the age of the person.
CREATE FUNCTION GetAge(@UserDOB Date)
RETURNS INT
AS
BEGIN
DECLARE @User_Age INT
SET @User_Age = DATEDIFF(YEAR, @UserDOB, GETDATE()) - CASE WHEN (MONTH(@UserDOB) > MONTH(GETDATE())) OR (MONTH(@UserDOB) = MONTH(GETDATE()) AND DAY(@UserDOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
RETURN @User_Age
END
If you look at function, I’ve created a variable called at @UserDOB of Type Date, and there is another variable of type integer @User_Age, which is used to hold the age of the user.
This function requires a date of birth as the input parameter.So let’s say @UserDOB and this has to be date parameter and it should return back an integer, which is nothing but age of the person AS BEGIN and finally END.
Whoever calls this function, returns the age back to that calling program.so if you look at the syntax of the function, create function function_name parameters,returns, data type AS BEGIN END, and within the BEGIN, then the function body goes in, and finally, you should have a return statement.
So when we execute this, what happens? This function gets created in our database.
if I expand the database if we go to the database programmability and functions and then scalar function. why it is called scaler function because it returns a single value.
And if I expand scalar functions here, scalar-valued functions and if I refresh this you should see Getage function that we have written and look at the name it says dbo.GetAge.
That’s nothing, but DBO stands for database owner and then .GetAge is the name of the function and this function is actually present in the ‘DemoDataBase‘ database.So the fully qualified name of this function is DemoDataBase.dbo.GetAge.
How to execute function in sql with parameters
To involve this function, just like how we have invoked getdate function.We do it the same way, but we need to use a two-part name. Let’s understand that.
Now, look at this. I’m just saying select GetAge. And when I execute this,it says GetAge is not recognized as a built-in function.
Msg 195, Level 15, State 10, Line 10
‘GetAge’ is not a recognized built-in function name.
And that’s because whenever you invoke a scalar user-defined function, you need to specify at least the two-part name database owner. The name of the function.
here, the database owner is DBO
select dbo.GetAge('01/08/1994')
Now you can also use the fully qualified name,
Select DemoDataBase.dbo.GetAge('10/08/1982')
How do you use user-defined function?
You know, we have used it in over the select statement.we have this table Tblusers. Now, I want you to write a query that will give me the name date of birth and the age of the users. And obviously, we have a function.And look at this in the database. We are not storing the age of the user, but we have date of birth.
So based on the date of birth, we can actually calculate the age of the user.we have written a scaler of function for that. So what we are going to do here?we want to name and the date of birth and to get that, we are using the age function.
This function is going to calculate an age for every person and return that. so you can use this user-defined scalar function in the select query, not only in the select clause. You can also use it in the where clause
Select Name, DOB, dbo.GetAge(DOB) as UserAge from TblUsers
Now I want all the users in all the TblUsers whose age is greater than 18
Select Name, DOB, dbo.GetAge(DOB) as UserAge from TblUsers
Where dbo.GetAge(DOB) > 18
Now, whatever we have achieved using this scalar dbo.GetAge function,we can also achieve it using stored procedures. We can write a stored procedure for that also.if you want, you can quickly convert this function into a stored procedure.
We have just seen that a function can be used in the select and where clause, but can I use a stored procedure in the select and where clause? you cannot do that. If you try to do that, you will get an error. A simple one difference between a function and a stored procedure.
Deterministic and non-deterministic functions
Now, what are deterministic and non-deterministic functions, and what are the differences between them? This is a very commonly asked interview question as well.
Now let’s understand these with an example. For example, we have a function called Square() Function which will square the number that we provide to it.
select SQUARE(2)
For example, if I pass in 2 and I execute this query, look at this. Every time you execute this, it gives you 4, Every time you execute that, it gives you 4 so you can determine the result of this function. So let’s look at the definition of the deterministic function.
A deterministic function always returns the same result any time they are called with a specific set of input values and given the same state of the database.
so here we have not changed the input value. So when we execute this function, I get the same result no matter how many times they execute that.
Non-deterministic functions
Obviously non-deterministic functions are opposite to deterministic functions for deterministic functions. They always return the same result for the given state and for the given set of input values, whereas non-deterministic functions may return different values for the same set of specific input values. And even if the database state has not changed.
The classic example is to Getdate() function.Every time you execute that the time is going to change for the same set of input values.
This function doesn’t take any input parameters and, and even if the database state has not changed, I get a different result every time I execute this function so that’s an example of a non-deterministic function.
The post Scalar User-defined functions In sql server-Step by Step appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Multiple column and multiple criteria SQL Server Delete?
- SqlParameter Datetime SqlValue difference with Value
- SSIS tasks are getting fail with Communication link failure error
- How to change default systemdate from ymd to dmy
- sql server authentication user
- Where to Use bigint?
- How to track changes in records in SQL Server?
- Can calculated column be used in another calculated column?
- Choose string between two chars that repeat multiple times in a string
- How to find all connected subgraphs of an undirected graph
- Nested Transactions - Delayed Durability
- .dwproj cannot be opened because its project type (.dwproj) is not supported by this version of the application
- Update 13 million rows - SQL Server 2008
- Need SQL MAX please
- Determine the person with the fewest entries
- sql select half distinct half not distinct
- Fill a table from CSV file with stored procedure
- SQL Server query designer on C# application
- Nesting multiple same select queries and reuse without second round trip to database
- SQL Server: How to select rows that were inserted on the same day?
- SQL Server: use parameter in CREATE DATABASE
- Connection between sql workbech on my Laptop and Mysql on Google cloud compute engine (not cloud sql)
- Query to return SINGLE DISTINCT row
- Is CTE the correct way to handle recursion with two tables?
- Multiple result sets excluding column
- Convert HH:MM:SS
- ReadyRoll Project: SemVer Option
- What happens if network is lost while rolling back transaction with TransactionScope in .Net
- ADO.NET SQLServer: How to prevent closed connection from holding S-DB lock?
- Best practices for SQL varchar column length
- How do I stop using ASPNETDB.MDF in LocalDB?
- How to configure IIS authentication for ASP.NET Core application to use SQL Server
- Reusing CHECK CONSTRAINTS
- How to compare the structure of two MS SQL databases?
- 30 million records a day, SQL Server can't keep up, other kind of database system needed?
- GROUP BY is returning wrong values?
- How to store conversion of datetime in a new column with new columnname which it is possible to use ORDER BY on?
- SCOPE_IDENTITY() always returning 0
- How do I make software that preserves database integrity and correctness?
- Can JDBC client execute SET ARITHABORT ON on Microsoft SQL Server 2012?
- How to Trim white spaces of string in stored procedure?
- Conditional SQL
- Pass select result as parameter of stored procedure
- How to use union all when using order by clause SQL Server 2014
- How I can fetch last two full years and current year records in SQL Server
- Sql fetch data with join
- SQL : Select Distinct rows by all columns but omit one column (say ID column)
- SQL Subquery to get actors in more than 30 films
- SQL Update query to replace last 4 characters in column
- EF Core fix-up when querying subset of columns
- Adding a word breaking character (underscore) to full text search
- How to arrive Last Year Week to Date and Month to Date in SQL?
- using join tables vs a one way lookup
- What's the best solution for file storage for a load-balanced ASP.NET app?
- What is pros and cons for these approaches SQL Server tedious queries
- Creating function to replace string query slows down the stored procedure
- In Sql server, what is the query to get the details of collation for a database and columns in the tables
- SQL Server 2008 Find row based on 1 unique column and duplicates in a second column
- Meaning of ' ' sql in query
- Find all records (rows) for most recent day of business
- Imitating SQL Server Profiler in a C# app?
- SQL: How to make a replace on the field ''
- Query to identify gaps in IDs on a per terminal basis
- SQL Server Pivot on this data
- SQL Query Running Total in Negative
- result not working in cfquery using railo
- incorrect syntax near '' unclosed quotation mark after the character string ')'
- SQL order by Month, Day closest to now
- SQL Server (2005) - "Deleted On" DATETIME and Indexing
- Add some symbols to row
- row count on duplicate field
- How can i transpose an .csv into a sql table with an SSIS Job?
- Append data to SQL Server from MS Access
- Return multiple datasets from sql server stored procedure
- How do I sum datetimes without a cursor?
- script issue Transact-SQL
- Select an element in nested XML in SQL Server
- SQL query to calculate hours spent from the the other day and ending up on the next day
- Saving binary data into SQL SErver database in vb.net
- How to create text files of database rows?
- Insert rows into a temp table having one and only one column
- VBA in Access 2010 - Run-time Error 430
- dynamic sql in stored procedure with column name as input parameter
- ROW_NUMBER() Performance optimization
- EXEC and Set Quoted_Identifier
- Invalid object name for cte
- Insert postgres JSON[] data type data into MSSQL data table
- What's the difference between begin transaction vs. begin work
- VB calls SQL Server stored procedure that writes to a file, fails over 104 KB
- Transaction Required with Optimistic Concurrency?
- merge tow querys in sql
- Populate auto-increment INT 5 digit value in case of null in SQL Server
- ORM Code First versa Database First in Production
- Distinct Count Via SQLFiddle
- Minimum database user permissions using Entity Framework database first approach
- SQL Server Select with two IN clause in conditions
- Want to know all possible Parent and Child Rows against specific Id?
- Check for DbNull in C#
- Get data by comma seperate with same UserId
- sum the count of days in many intervals
- Running total not working
- Calculate the time in minutes that a value has been greater than x
- How to configure AKS deployment to make an ASP.NET app able to connect to an Azure SQL database
- What is the proper way to insert millions of records from a csv file into a SQL Server database?
- Create extra sum column using one of the table columns value
- How to use sp_configure in another Stored Procedure?
- Is there a way to be notified in .NET when data changes in the Sql Server? Like an Event?
- Transposing a table in SQL server
- how to format bit datatype column in datagridview c#?
- Is there a difference between these two queries? CASE THEN
- Speeding up this query? Currently it takes 4 seconds on 100K rows
- How to configure SQL table to allow insertion of empty string to datetime?
- ExecuteSqlCommand parallel execution
- SQL, matching all the values in column b and return column a for those matches
- finding date in sql
- SQL - hours format in between
- Can I use LINQ to do a join on an IQueryable from SQL Server?
- SQL Server Extended Events - find particular string in the syntax
- Updating data to integrate it in my datawarehouse
- TSQL - Random winner for multiple locations based on weight
- SQL Server Management Studio 2012 does not start
- Ssis 2012, Ssisdb Catalog And Serverstorage Protection Level Errors
- How to use substring with case statement in order to filter for two specific category and group all others in a single category
- Small table has very high cost in query plan
- How to extract multiple strings from single rows in SQL Server
- Large number of database reads while calling procedure using Entity Framework 4
- Querying table statistics sample rates
- SQL server Left join with child tables and get the table name
- Use time selector from Power BI as an argument in my SQL query
- How to alter the below table?
- Incorrect syntax at '='
- How to use EXCEPT clause in multiple databases
- SQL Server 2000: Limit number of rows
- And condition in Case Statement IN SQL Server
- Multiple statements inside one BEGIN ... END block
- Alternative to parameter for case statement
- How long does it take to create an identity column?
- sql server Cast X,000
- Codeigniter connect database that contain spaces in database name
- EF Core idempotent migration script fails despite check on applied migration
- Auto complete in asp.net using jquery
- Error : There is already an open DataReader associated with this Command which must be closed first
- Execute result of Select Statement which in turn are select queries
- Hibernate Transformers.aliasToBean populate primary fields
- MVC 4 entity framework Not picking up database from SQL server
- How to use a recursive query as a subquery?
- SQL Output Parameter
- Authenticating MSSQL through django-pyodbc using Windows Authentication from IIS7
- Select Result grouped by all fields
- How do I write a TSQL function that takes a table name to drop, and drops a table