Server Interaction  «Prev  Next»

Lesson 3 SQL Server 2012 Functions
ObjectiveDescribe how to use many of the functions available in SQL Server 2018.

SQL Function Defined

SQL Server Functions

A function is a piece of self-contained code that performs some process or calculation and returns a value back to the calling program. SQL Server 2018 comes with hundreds of built-in functions for you to use.

Retrieve a value from Table

You can use a function to retrieve a value with the SELECT statement. The statement below uses the GETDATE system function to retrieve the current date and time: 

SELECT GETDATE()

Notice that the function name is followed by parentheses. Without these, SQL Server would think that GETDATE is a column name in a table and would generate an error. The parentheses are empty because the GETDATE function accepts no arguments.

Multiple Arguments

Some functions accept more than one argument. For example, the CAST function, which converts data from one data type to another, accepts two arguments:
  1. The value to be converted
  2. The data type to which it should be converted

CAST is a unique function because the return data type is not fixed, but is determined by the Transact-SQL programmer as an argument.
This is illustrated in the following SlideShow.

In the code above, a variable @val is declared as an integer.
1) In the code above, a variable @val is declared as an integer.
Then a value is assigned to the variable with the SET command
2) Then a value is assigned to the variable with the SET command
Finally, the value is returned back to the calling application, by concatenating a string literal with the value from the variable. Concatenation is the process of appending one string to another.
3) Finally, the value is returned back to the calling application, by concatenating a string literal with the value from the variable. Concatenation is the process of appending one string to another.
Because you cannot concatenate an integer datatype to a char or varchar datatype, the CAST function is used to convert the integer to a string datatype
4) Because you cannot concatenate an integer datatype to a char or varchar datatype, the CAST function is used to convert the integer to a string datatype

  1. In the code above, a variable @val is declared as an integer.
  2. Then a value is assigned to the variable with the SET command
  3. Finally, the value is returned back to the calling application, by concatenating a string literal with the value from the variable. Concatenation is the process of appending one string to another.
  4. Because you cannot concatenate an integer datatype to a char or varchar datatype, the CAST function is used to convert the integer to a string datatype

Functions versus Global Variables

Global variables were used in prior versions of SQL Server, and were available to all procedures that had access to a connection. SQL Server set the value of these variables, based on certain conditions. In SQL Server 2008, global variables have been replaced with functions, but these function names all begin with @@. For example, @@LANGID returns the currently selected language ID for SQL Server. You call it like it is any other function, like this: 

SELECT 'Language ID is ' + CAST(@@LANGID as varchar)

Nested functions

The Transact-SQL statement above is actually a function within a function. This is a very common situation in SQL Server. A function within another function is called nesting. There are a couple of things to keep in mind when you nest functions:
  1. SQL Server evaluates functions from the inside out. This means that the innermost function within parentheses is evaluated first.
  2. You must be aware of the data type returned by the innermost functions to the next level, as well as the data type expected by the next level. If there is a mismatch of data types, an error will result.
In the next lesson, common ways in which functions are used will be discussed.