| Before I leave this chapter, let's take a look at T-SQL Functionsthey are similar to stored procedures in a number of ways. I've had the ability to manage SQL Server Functions since the earliest versions of Visual Studioas long as you were accessing SQL Server 2000 or later. A SQL Server Function (like its Visual Basic counterpart) is simply a block of code used to perform a set of logic and return a value based on zero or (almost) any number of input parameters. A Function, like a VIEW, can return a rowset, but unlike a VIEW, a Function can execute several SELECTs and other T-SQL or CLR logic as well. A VIEW can JOIN to the TABLE returned by a Function or simply return a Function-modified column. T-SQL (user-defined) Functions are built for the traditional reasons: Functions help us build modular stored procedures, queries, and VIEWs by encapsulating reusable logic. I also use them to simplify building code-intensive routines. This improves developer productivity. Functions can be used in a T-SQL SELECT, WHERE, or CASE statement or to create a JOIN. They're also easier to invoke than stored procedures from within another SQL statement. Compiled query plans used by Functions are cached, thus improving overall application performance.
Unlike a stored procedure, a T-SQL Function can't be used to execute DML statementsthey can't be used to modify data.
As I discuss later in Chapter 13, Functions can be written in T-SQL or in any of the CLR languages (like Visual Basic .NET or C#). I'm going to stick with T-SQL Functions in this chapter and show how to create, locate, edit, and debug Functions using the Server Explorer. SQL Server supports a wealth of pre-written Functions. There are Functions to return rowsets, Functions to perform complex aggregation or ranking operations, as well as configuration, server-side cursor, data-time, mathematical, and many other Functions you'll find useful, if not essential. I suggest you consult BOL for a list of all supported Functionsbefore you start building a Function that might already be included in SQL Server. The value returned by the Function defines the type of user-defined Function (UDF): A scalar UDF returns a single value. An Inline Function returns a single TABLE type variable created by a SELECT statement against an existing table or VIEW. Inline Functions are generally fairly simple and are similar in functionality to a T-SQL View. Think of the Inline Function as a VIEW with parameters. A "multi-statement" (or Table-type) UDF table Function returns a variable typed as a T-SQL TABLE object. Its structure is defined in the Function (using an INSERT INTO statement). Generally, this type of Function is most like a stored procedure in that the logic is more complex.
Unlike a stored procedure, a Function can return only a single TABLE-type variable, while a stored procedure can return any number of rowsets. Remember, while Functions are designed to be called from within stored procedures or other Functions, they can be called directly from your application. They cannot call stored procedures or execute "dynamic" (created on-the-fly) SQL. A Function also cannot call certain "non-deterministic" built-in Functions (those that don't return the same value each time, like RAND) or extended stored procedures[4]. Error handling is also very limited, as a Function cannot reference RAISERROR or @@ERROR. No, you won't be able to set breakpoints in a Function, but you can "step through" an existing Function to test it. [4] See "Deterministic and Nondeterministic Functions" in SQL Server BOL.
Tip No, you won't be able to set breakpoints in a Function, but you can "step through" an existing Function to test it.
Let's take a closer look at the structure of a Function. While similar to a stored procedure, there are some important differencesespecially in regard to defining how the data is to be returned. Like stored procedures, user-defined Functions (UDF) include: - A name that includes the owner. This means a UDF must be invoked by name and includes the owner name as: DBO.MyFunction.
- One or more input parameter definitions, including options for each parameter. Note that T-SQL Functions can't return output parameters[5]. Parameters serve as constant placeholders whose value is set at runtime. It cannot take the place of a table n0ame, column name, or other database object. Each Function parameter can be assigned a default value. When a parameter of your Function has a default value assigned, you must call the Function with the keyword DEFAULT to use the default valueunlike the way stored procedures are executed. You can't just leave off the parameter.
[5] CLR functions can designate specific parameters to be returned to the calling routine.
- For Scalar and Multi-statement Functions, a BEGIN statement that frames the executable T-SQL code and an executable T-SQL body that perform the Function's logic. T-SQL Functions can (now) be programmed to call out to CLR logic. I'll discuss that and many other aspects of CLR functionality in Chapter 13. Note the limitations I mentioned earlier regarding use of non-deterministic Function calls, use of DML, and error-handling/reporting Functions. The T-SQL statement body is ended with an END statement that marks the end of the executable T-SQL code.
- A RETURN parameter definition and options. Functions can return a scalar (a single value), a rowset from a single SELECT, or a rowset in the form of a TABLE object. The RETURN type can be any type (except Timestamp), including CLR user-defined types.
Creating T-SQL Functions with the Server Explorer I'm ready to step through the process of creating three new T-SQL Functions using the Server Explorer. In the following example, I start by illustrating the code to create a simple Inline Function that compares two strings to see if they sound alike. This T-SQL uses the built-in T-SQL SOUNDEX Function to do most of the work. Tip Sure, this could have been done by simply coding an expression that called the built-in SOUNDEX Functions directly.
1. | To get started, as with stored procedures, drill into an existing Data Connection in the Server Explorer and navigate to the Functions tab.
| | | 2. | Right-click on the Functions tab and choose "Add New". This opens a selection menu (as shown in Figure 5.24) that permits you to choose the Function template. There is a template for each type of FunctionI'll create one of each in the following exercises.
Figure 5.24. Creating a new Function with the Server Explorer. 
| | | 3. | Choose "Scalar-valued Function". This opens a T-SQL editor window with the Scalar Function template read to be completed with your code (as shown in Figure 5.25).
Figure 5.25. The Scalar Function template. 
Note how the RETURN statement datatype is left out. This needs to be set to correspond to the datatype to be returned by the Function. Yes, you can use any type (except for timestamp). The logic for the Scalar Function is inserted between the BEGIN and RETURN (the RETURN must be the last statement in the code). This is called the "Function body"it's specified only in Scalar and Multi-statement type Functions.
| | | 4. | Add parameters and code to your new Scalar Function. In this case, I need to capture three parameters and return a string (varchar). The parameters input two strings to compare and a "difference" criterion. The DIFFERENCE Function compares the two strings and returns a value (04) to indicate how closely the two strings sound alike. This approach is useful when searching the database for a name that "sounds like" some other word. The completed Scalar Function is shown in Figure 5.26.
Figure 5.26. The completed Scalar Function. 
| 5. | To save the Function, click the Save icon or click the File | Save <Function name> menu. If you've made a syntax error, Visual Studio returns a simple (and relatively useless) error message. If you get stuck, try pasting the code into a SQL Server Management Studio query windowthe syntax exception returned by this editor includes the (very useful) line number of the incorrect line of code.
You can test this Function using the Server Explorer or by using another T-SQL script, as I've discussed earlier in this chapter. I'll step through some testing scenarios after I discuss the next type of Function.
|
Creating an Inline Function Inline Functions are less complicatedespecially when the Server Explorer template helps get you started. In this case, the Inline Function is programmed to return a T-SQL TABLE object built from a single SELECT. Sure, this SELECT can JOIN two or more tables; include an ORDER BY, GROUP BY; or reference a VIEW or other Functionsjust as long as it returns a single resultset containing a rowset. Let's step through the process of creating the Inline Function. 1. | Choose "Inline Function". This opens a T-SQL editor window with the Inline Function template ready to be filled in (as shown in Figure 5.27).
Figure 5.27. The Inline Function template. 
| | | 2. | Note that the template is fairly simple. It defaults to returning a TABLE-type variable generated from a single SELECT. Remember to think of the Inline Function as a VIEW with Parameters. For this example, I'm going to create a Function that returns a TABLE containing customer IDs (CustID) from customers living in a selected state. Once the SELECT is generated, it appears imbedded in the query (as shown in Figure 5.28).
Figure 5.28. A simple user-defined T-SQL Inline Function. 
| | | 3. | To save the Function, click the Save icon or click the File | Save <Function name> menu. If you've made a syntax error, Visual Studio returns a simple (and relatively useless) error message. If you get stuck, try pasting the code into a SQL Server Management Studio query windowthe syntax exception returned by this editor includes the (very useful) line number of the incorrect line of code.
| 4. | At this point, I'm ready to test the Function. Since the Inline Function returns a TABLE object, you can address the columns using normal syntax. A simple test executes a SELECT against the Function-generated TABLE. To set this up, open a new stored procedure window (right-click on the Stored Procedures tab and choose "New Stored Procedure"). Next, enter the test T-SQL (as shown in Figure 5.29). Yes, I'm using SELECT * here to test the Functionto ensure that all of the required columns are returned.
Figure 5.29. Testing the new Inline Function in a stored procedure code edit window. 
| | | 5. | Once the code is ready to execute, select one or both lines of the code in the stored procedure code edit window and right-clickchoose "Run selection" to test the Function. The results are shown in the Visual Studio Output (Database Output) window (at the bottom of the IDE), as shown in Figure 5.30.
|
Figure 5.30. Executing code with Run Selection from the stored procedure code edit window. 
Creating a Table Function The Table Function is a bit more complex, but again, the Visual Studio Function template can give us a good running start at creating it. This type of Function is called a "Table" Function in Visual Studio but is referred to as a "Multi-Statement" Function in the SQL Server documentation and the earlier discussion in this chapter. It's different from the Inline Function in that it includes code to generate the TABLE it returns in the body of the logic. No, the "signature" of the function can't change from execution to execution, so the Function always returns the same TABLE object columns. In this case, I'm going to create a particularly useful Table Functionit converts a delimited string into a TABLE object. While that might not seem like much, this Function can be very useful when building multi-choice queries. That is, if you're generating an application that permits the user to choose from a list of pick-list options, this function can convert the delimited string returned by the control to a TABLE object that can be used in a JOIN. I'll illustrate this in an example a bit later. | | 1. | Start by right-clicking the Functions tabchoose "Table-valued Function" from the "Add New" function. This opens a new Function code edit window, as shown in Figure 5.31.
Figure 5.31. The Table-type Function template. 
Note that the RETURNS statement defines the signature of the TABLE object created by the function. It's exposed to the Function code block by the @table_variable, which is scoped within the Function. You're not permitted to execute any SQL function that returns values other than those returned by the TABLE variable in the RETURN statement. Let's step through the Table Function syntax so I'll better understand the code when I explore the example.
| 2. | Next, define the input parameters for the Function. Just as before, define each parameter's datatype and (optionally) a default value. Defaults are set so the Function can be executed without passing a value for the parameter (but you will have to use the keyword "DEFAULT" in place of the parameter).
| | | 3. | Next, set up the RETURNS statement by defining the TABLE object to be returned. This statement resembles a parameter blockand it is, in a sense. It defines each column of the new TABLE object I'll be returningsort of like "output" parameters, but in this case, I'll return many rows of data instead of a set of OUTPUT parameters, as I do with stored procedures. My simple TABLE object has only two columnsan integer row number and a string value. These first sections of the TABLE-type function are shown in Figure 5.32.
Figure 5.32. The parameter block and RETURNS statement for the Table-type Function. 
| 4. | At this point, I'm ready to fill in the code block. I frame the code block with BEGIN and END statementsin between, I code the Function logic, finishing with the RETURN statement. The Function must end with a RETURN statement.
In this example, I use the (rather limited) T-SQL string functions to parse the delimited string passed to the Function. The delimiter is passed to the Function as a parameter, so the Function includes logic to validate the input parameters as well as the length of the string to parse. Since the Function can't raise an error or throw an exception, I don't have a lot of options to report invalid parameters at runtimeI return an empty TABLE if the arguments aren't within spec. The code for these validation tests are shown in Figure 5.33.
Figure 5.33. Validating the Function input parameters. 
| | | 5. | The rest of the code block parses the delimited string passed to the function. It walks the string and uses the INSERT statement to add rows to the in-memory TABLE object I've defined. The rest of the code block is shown in Figure 5.34.
Figure 5.34. Parsing the delimited string and populating the TABLE object. 
| 6. | To save the Function, click the Save icon or click the File | Save <Function name> menu. Once saved, the Function appears in the list of Functions exposed by the Server Explorer.
|
I'm ready to test the function, so (as promised) I'll create a query that uses the StripToTable Function to use a delimited list to drive a T-SQL IN statement. Again, I used the Query Designer and the empty stored procedure code editor window to create and test the query, as shown in Figure 5.35. Figure 5.35. Testing the Table-type Function. 
Note that the IN statement ordinarily accepts a delimited string, but this string must be fixed at compile time. By using the StripToTable Function, you can provide a delimited string as an input parameter to a stored procedure that duplicates the functionality shown in Figure 5.35. Let's take that extra step and create a stored procedure that accepts a delimited string and returns a customer list. Figure 5.36 illustrates this code. Figure 5.36. Using the Table-type Function in a stored procedure. 
I get the same results as when I tested Figure 5.35 by executing the following code (shown in Figure 5.37.) Figure 5.37. Testing the stored procedure that executes the Table-type Function. 
|
