In Transact-SQL, the primary formula of creating a function is: CREATE FUNCTION FunctionName() In SQL, a function was created as an object. As such, it must have a name. In our lessons, here are the rules we will use to name our functions:
Like a method in C#, a function can return or produce a result. When creating a function, you must specify the type of value it must return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example: CREATE FUNCTION Addition() RETURNS Decimal(6,3) After specifying the type of value the function would return, you can create a body for the function. While in C# the body of a function starts with {, in SQL, the body starts with the BEGIN keyword. The body of a function closes with the END keyword, equivalent to } in C#. Here is an example: CREATE FUNCTION Addition() RETURNS Decimal(6,3) BEGIN END Optionally, you can type the AS keyword before the BEGIN keyword: CREATE FUNCTION Addition() RETURNS Decimal(6,3) AS BEGIN END Between the BEGIN and END keywords, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is: CREATE FUNCTION Addition()
RETURNS Decimal(6,3)
AS
BEGIN
RETURN Expression
END
Here is an example CREATE FUNCTION GetFullName() RETURNS varchar(100) AS BEGIN RETURN 'Doe, John' END
After a function has been created, you can use the value it returns. Like a method in C#, using a function is also referred to as calling it. To call a function, you must qualify its name. To do this, type the name of the database in which it was created, followed by the period operator, followed by dbo, followed by the period operator, followed by the name of the function, and its parentheses. The formula to use is: DatabaseName.dbo.FunctionName() Because a function returns a value, you can use that value as you see fit. For example, you can use SELECT to display the function's value in a query window. Here is an example that calls a function: private void btnEvaluate_Click(object sender, EventArgs e)
{
string strConnection =
"Data Source=(local);" +
"Database='UtilityCompany1';" +
"Integrated Security=yes;";
string ExecuteFunction = "SELECT dbo.EvaluateInvoice();";
using (SqlConnection connection = new SqlConnection(strConnection))
{
SqlCommand command =
new SqlCommand(ExecuteFunction, connection);
connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
txtAmountDue.Text = rdr[0].ToString();
}
rdr.Close();
}
}
Because a function in Transact-SQL is treated as an object, it may need maintenance. Some of the actions you would take include renaming, modifying, or deleting a function. To rename a function, in the Object Explorer in Microsoft SQL Server, right-click it and click Rename. Type the desired new name and press Enter.
If you create a function and decide that you don't need it any more, you can delete it. To programmatically delete a function, type DROP FUNCTION followed by the name of the function and execute the statement. Here is an example: private void btnDatabase_Click(object sender, EventArgs e)
{
string strConnection =
"Data Source=(local);" +
"Database='UtilityCompany1';" +
"Integrated Security=yes;";
string CreateFunction = "DROP FUNCTION EvaluateInvoice;";
using (SqlConnection connection = new SqlConnection(strConnection))
{
SqlCommand command =
new SqlCommand(CreateFunction, connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A function named \"EvaluateInvoice\" has been deleted.");
}
}
As mentioned already, in the body of the function, you define what the function is supposed to take care of. As a minimum, a function can return a simple number, typed on the right side of the RETURN keyword. Here is an example: CREATE FUNCTION Addition()
RETURNS int
BEGIN
RETURN 1
END
You can also declare new variables in the body of the function to help in carrying the assignment. A variable declared in the body of a function is referred to as a local variable. Once such a variable has been declared, it can be used like any other variable. Here is an example: CREATE FUNCTION Addition()
RETURNS int
BEGIN
DECLARE @Number1 int
SET @Number1 = 588
RETURN @Number1 + 1450
END
Like a method in C#, a function can be passed some arguments. Put it another way, when you create a function, instead of, or in addition to, local variables, you may want the code that will call the function to provide the values needed to perform the assignment. An external value that is provided to a function is called a parameter. A function can also take more than one parameter. We know that a function's name is also followed by parentheses. If the function doesn't use any argument, its parentheses are left empty. If a function takes an argument, when you create the function, you must specify a name and the type of value of the parameters. The name of the parameter is created with the @ sign, like a variable as we saw in the previous lesson. Here is an example: CREATE FUNCTION Addition(@Number1 Decimal(6,2)) When a function takes a parameter, in the body of the function, you can use the parameter as if you knew its value, as long as you respect the type of that value. Here is an example: CREATE FUNCTION Addition(@Number1 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
RETURN @Number1 + 1450
END
When you call a function that takes one parameter, you must supply a value for that argument. To do this, type the value of the parameter in the parentheses of the function.
Instead of only one parameter, you can also create a function that takes more than one parameter. In this case, separate the arguments in the parentheses of the function with a comma. Here is an example: CREATE FUNCTION Addition(@Number1 Decimal(6,2), @Number2 Decimal(6,2)) Once again, in the body of the function, you can use the parameters as if you already knew their value. You can also declare local variables and involve them with parameters as you see fit. Here is an example: CREATE FUNCTION Addition(@Number1 Decimal(6,2),
@Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
DECLARE @Result Decimal(6,2)
SET @Result = @Number1 + @Number2
RETURN @Result
END;
GO
When calling a function that takes more than one parameter, in the parentheses of the function, provide a value for each parameter, in the exact order they appear in the parentheses of the function. Here is an example: SELECT Variables1.dbo.Addition(1450, 228); You can also pass the names of already declared and initialized variables. Here is an example that calls the above function: DECLARE @Nbr1 Decimal(6,2),
@Nbr2 Decimal(6,2)
SET @Nbr1 = 4268.55
SET @Nbr2 =26.83
SELECT @Nbr1 As First,
@Nbr2 As Second,
Variables1.dbo.Addition(@Nbr1, @Nbr2) AS Result
One way you can assist users is to use functions that perform otherwise complex tasks. To assist you, Transact-SQL provides a vast collection of read-made functions. If you use one of those functions, they must be transmitted to the SQL interpreter. In some if not most cases, you can use classes of the .NET Framework. Because of their complexities, some values can be easily recognized or fixed. For example, a date such as January 6, 1995 is constant and can never change. This type of value is referred to as deterministic because it is always the same. In the same way, a time value such as 5 PM is constant and cannot change. There are other values that cannot be known in advance because they change based on some circumstances. For example, the starting date of the school year changes from one year to another but it always occurs. This means that you know it will happen but you don't know the exact date. Such a value is referred to as non-deterministic. To support determinism and non-determinism, Transact-SQL provides two broad categories of functions. A function that always returns the same or known value is referred to as deterministic. A function whose returned value may depend on a condition is referred to as non-deterministic. Parsing consists of scanning, that is, checking every part (such as every symbol) of an expression or a word to match a pattern. An example of parsing is to check every symbol in a combination to find out if it is a number or something else. Based on this, parsing something like 275.25 would determine whether this is a decimal number or not. To assist you with parsing, Transact-SQL provides a function named PARSE. Its syntax is: PARSE ( string_value AS data_type [ USING culture ] ) This function takes one argument, passed as a string and accompanied by its data type preceded by the AS keyword. The function is used to "scan" an expression that is passed as the string_value argument. The expression must follow the rules of the data_type argument. For example, suppose you want to find out if some value is an integer. You can pass the data_type as int. Then, the expression passed as the argument must include digits and no other character or symbol. If the argument may include international characters or formats (Unicode), you should indicate the language, called a culture, that the argument follows. If the PARSE() function is not able to determine the type or if the value of the argument doesn't follow the rule of the data_type, this function produces (throws) an error. As an alternative to PARSE(), Transact-SQL provides the TRY_PARSE() function. Its syntax is: TRY_PARSE ( string_value AS data_type [ USING culture ] ) As you can see, this function uses the same argument as PARSE. The difference is that, while PARSE() produces an error if the parsing operation fails, TRY_PARSE produces NULL (if the parsing operation fails). This means that, in most cases, you should prefer TRY_PARSE() instead of PARSE().
In most cases, a value the user submits to your database is primarily considered a string. This is convenient if that's what you are expecting. If the value the user provides must be treated as something other than a string, for example, if the user provides a number, before using such a value, you should first convert it to the appropriate type, that is, from a string to the expected type. In a C# application, to convert a value, you can use either the Convert class or a Parse() method. In Transact-SQL, to convert a value, you can use either the CAST() or the CONVERT() function. The syntax of the CAST() function is: CAST(Expression AS DataType) The Expression is the value that needs to be cast. The DataType factor is the type of value you want to convert the Expression to. The DataType can be one of those we reviewed in Lesson 23. If the CAST() function is not able to cast the expression (if it fails), it produces (throws) an error. As an alternative to CAST(), Transact-SQL provides a function named TRY_CAST. Its syntax is: TRY_CAST ( expression AS data_type [ ( length ) ] ) This function takes the same arguments as CAST. The difference is that, instead of producing an error, if TRY_CAST() fails, it returns NULL. This also means that, in most cases, you should use TRY_CAST() instead of CAST. Here is an example: DECLARE @StrSalary nvarchar(10), @StrHours nvarchar(6), @WeeklySalary decimal(6,2) SET @StrSalary = N'2W2.18'; SET @StrHours = N'38.50'; SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) * CAST(@StrHours As Decimal(6,2)); SELECT @WeeklySalary; GO
Like CAST(), the CONVERT() function is used to convert a value. Unlike CAST(), CONVERT can be used to convert a value from its original type into a non-similar type. For example, you can use CONVERT to cast a number into a string and vice-versa. The syntax of the CONVERT() function is: CONVERT(DataType [ ( length ) ] , Expression [ , style ]) The first argument must be a known data type, such as those we reviewed already. If you are converting the value into a string (varchar, nvarchar, char, nchar) or a binary type, you should specify the number of allowed characters in the data type's own parentheses, as the length argument. As reviewed for the CAST() function, the Expression is the value that needs to be converted. If the conversion is performed on a date or time value, the style argument is a number that indicates how that conversion must proceed. If the CONVERT() function is not able to perform its operation, it produces an error. Because of some difficulties that can result from conversion, Transact-SQL provides a function named TRY_CONVERT. Its syntax is: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) The arguments are the same used in the CONVERT() function. The difference is that if the TRY_CONVERT() function fails, it returns NULL instead of producing (throwing) an error. Here is an example: -- Square Calculation
DECLARE @Side As decimal(10,3),
@Perimeter As decimal(10,3),
@Area As decimal(10,3);
SET @Side = 48.126;
SET @Perimeter = @Side * 4;
SET @Area = @Side * @Side;
PRINT N'Square Characteristics';
PRINT N'-----------------------';
PRINT N'Side = ' + TRY_CONVERT(varchar(10), @Side, 10);
PRINT N'Perimeter = ' + TRY_CONVERT(varchar(10), @Perimeter, 10);
PRINT N'Area = ' + TRY_CONVERT(varchar(10), @Area, 10);
GO
|
|
|||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||
