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
- Getting error executing package SSIS through a job of RUNDECK
- Why does SQL Management Studio add a cross join?
- How Get Reports To See Shared Data Sources
- How to form a where clause from XML data
- SQL Server cross database alias
- Dynamic SQL MERGE: Not a valid identifier
- SQL Server 2012 : getting a list of available backups
- Primary Keys in Oracle and SQL Server
- Server Error in Application
- Distinct In SQL Query resulting duplicate Values
- How to control casting of null int field to varchar in sql server?
- How to get the year wise employee as per the promotion
- Save datetime in sql table
- Incorrect syntax near the keyword 'TRIGGER'
- Can a view return multiple result sets?
- Capitalization of Spanish month and day names in syslanguages
- sorting columns with in a row SQlite
- SQL Server - Querying XML DataType
- SQL Server > Query > Column Names
- Mixing Nhibernate and ADO transactions?
- Count columns with a value x at least once
- SQL: Show Records Once SUM Threshold Is Reached
- column constraint that uses a user-defined function (udf)
- Creating statistical data from a table
- get Max value without group by 1 row sql query
- How to implement client-side tracing for SQL Server?
- Connect to db server from an app server: (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
- Is there an equivalent in MS SQL Server to the <=> operator in MySQL?
- What is the T-SQL syntax for finding table/s in my SQL Server database which has a VARBINARY column type?
- Performance of Aggregate Functions on Large Infrequently Changing Datasets
- Receiving automatic notification from SQL Server when a table is non-empty
- Get a number from a sql string range
- How to group projected revenue (same, daily, fixed amount) by month between two dates
- Return rows which have same data in three columns
- MS SQL Server - depersonalise data
- How to get specified number of prior rows from a specified row in SQL Server 2014?
- SQL Server - Counting number of times an attribute in a dataset changes (non-concurrently)
- Need to delete a row if it is occurring twice in a year and quarter with different status
- Should I use SqlString or string as parameter type to SQLCLR UDF's
- Convert an Int to a date field
- Is there a way to set a default cursor type in PDO (specifically PDO_SQLSRV)?
- When exactly do we use stored procedures with output parameters?
- Best choice to store a list of ints in mssql
- SQL: Return Aggregated Results based on some conditions
- compare databases (tables only) using C# and Ado.net
- T-Sql Get Row Count Same Values
- JDBC MSSQL with Logsatsh to connect to Montoring DB
- Find IP (IPv4 and IPv6) address between range
- Issue in SQL Server install INI file to allow non-admin users to connect?
- Messed up SQL data - Select within update statement
- Does IsDate function in SQL Server treat 201301 as YYYYMM or YYMMDD?
- ssis generate json file remove return
- Update multiple rows in one column with XML in MS SQL
- Incorrect syntax near a var
- 1 of x updates failed vs row not found or changed
- Insert multiple rows for each row in another table
- Where is the Query Analyzer in SQL Server Management Studio 2008 R2?
- Get ID of inserted & Selected row after multiple insert
- Blank Output for AJAX Call
- SQL readmits in 30 day partitions
- restricting access on sql server to "my" views
- SSIS Send Email File Attachment Failure
- How to count number of vowel - SQL Server
- Best way to support reordering html table rows using jquery as well as make SQL backend fast?
- How to secure SQL Server Management Studtio
- How to import an XML file into SQL Server 2012
- do we have to write while (1=1) in activation stored procedures for service broker queues always or it's an optional?
- Column 'Comptes.CO_NUMERO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
- Connection failed to SQL Server Express using user with SQL Server authentication
- error occurred while establishing a connection to SQL Server
- SQL query group by multiple time periods
- Overriding SQL Server Reporting Service Hourly Subscription
- How to make layout from SQL query
- using row_number to return specific rows of query
- How to prepare data for a BULK INSERT command
- T-sql - determine if value is integer
- check constraint in generated scripts
- SQL Count Number Of Classmates For a Student Across All Courses
- Need help to pass multiple values to single parameters in case?
- update nvarchar not null field length
- SQL Server Load Distribution
- SQL Server 2008, pivot with no aggregate fuction
- How can i increase number of rows in sql table
- Why should I use char instead of varchar?
- How to use LIKE and NOT LIKE together in a SQL Server query
- SQL Numbering of non-sequential Groups
- c#: try/catch DB exceptions does not work as expected
- SQL Comma separated values with COALESCE
- STUFF SQL Query in NHibernate, basically trying to replicate MySQLGroupConcat
- Why is this query not working for number 1?
- SCOPE_IDENTITY, multiple inserts, carries value from previous insert if current insert doesn't insert
- Window function behaves differently in Subquery/CTE?
- Creating ASP.NET Identity user using SQL
- How to group by only 2 rows of a column?
- SQL query order by top category follow by subcategory
- SQL WHERE statements in SELECT statements
- SQL SUM negative value not being returned
- SSRS 2012: "The report execution has expired or cannot be found. (rsExecutionNotFound)"
- Regular expression for mm/yy in Microsoft SQL Server
- SQL Server - Is it possible to return an existing column value before a row update?
- Get count of each id in SQL Server
- Sql Server Find Next Most Recent Changed Record
- Update w/Select Subquery - Correct?
- sql case in where clause
- Extract uppercase letters and numbers
- Fetch records in the same order WITHOUT using ORDER BY clause
- SQL Server 2008: concatenate fractions of n-rows together
- Rows to field level in query?
- Table relation with stored procedure and trigger
- SQL Server error log entry : Error: 17806, Severity: 20, State: 14
- Selecting data from SQL Server Using PHP
- Connect to SQL Server instance using pymssql
- Flattening T-SQL bitmask from enumeration table
- Error while executing SSIS package to data migration from SQL 2012 server to MySql
- SQL Server CASE then statement error
- sql server unique key for each partition
- GROUP BY clause not showing desired result
- Convert string with expression to decimal
- How to find which columns don't have any data (all values are NULL)?
- SQL XML PATH--HOW to remove the space in the middle
- Update data in SQL Server database
- CASE Expression with T-SQL
- Convert SQL to C# Entity Framework Linq
- SQL Server Delay in Committing Inserted Data
- UNION two SELECT queries but result set is smaller than one of them
- Displaying an image from SQL Server in asp.net
- Using IIF in SQL update statement
- Get most recent job's data for all unique jobs
- Safe convert from string to date in Transact
- Fuzzy logic matching
- SQL Server 2012 how do I view data?
- German Umlaut hash - SHA256 on SQL server
- Get AutoIncrement ID and insert into Foreign Key Table
- Can't change values in SSIS Transformation Script Editor
- Convert Unix Time column to UTC in SQL
- summarize dates from a list of dates
- Execute xp_cmdshell command as specific user
- Querying selection of values in XML in SQL Server, on table of mixed values
- SQL Server 2005 The Select permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'
- Parse XML Result in Entity Framework 6.1.2
- How can I get TotalPages in SSRS Report body?
- SQL Server Query Performance with Timestamp and variable
- SQL Server sum all the rows with the same row name
- Update multiple fields in one column using one query
- Using Multiple And's in a Case statement instead of subqueries
- Msg 7391: linked server unable to begin a distributed transaction (both svrs running locally)
- What's SQL Server's analogue of MySQL's unicode_ci collation?
- Display Column Values for last record only in sql server
- List names of all tables in a SQL Server 2012 schema
- SQL Error 586 When running batch scripts through Flyway