|
Summary
This article briefly explains the UDF in SQL Server with an example.
User-defined functions are either scalar-valued or table-valued.
Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Scalar-valued functions can be defined using multiple Transact-SQL statements.
Functions are table-valued if the RETURNS clause specified TABLE as shown in example. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multi-statement functions.
If the RETURNS clause specifies TABLE with no accompanying column list, the function is an inline function. Inline functions are table-valued functions defined with a single SELECT statement making up the body of the function. The columns, including the data types, of the table returned by the function are derived from the SELECT list of the SELECT statement defining the function.
If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.
Replacing stored procedures with UDFs doesn't simplify your code, but it provides you with other benefits. You can't include results returned from stored procedures in query joins, but you can join results returned from both inline and multistatement table-valued functions. Also, you can use table-valued UDF resultsets to create new tables on the fly by using the SELECT…INTO statement, which isn't possible with results you return from stored procedures. The multistatement table-valued functions allow you to build resultsets that require some intermediate processing before you can return the results. You must create a table variable in the function explicitly, populate it with data in the function body, and return the table. This type of function is like a hybrid between a view and a stored procedure—you can use the result in joins, as you can with views, and you can implement complex multistatement logic to obtain the resultset, as you can with stored procedures. In general, you can replace stored procedures with UDFs if the stored procedures return a single resultset and don't need to perform operations not allowed in UDFs, such as updating tables, executing dynamic SQL statements, or creating database objects.
The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:
- Assignment statements.
- Control-of-Flow statements.
- DECLARE statements defining data variables and cursors that are local to the function.
- SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
- Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
- INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
- EXECUTE statements calling an extended stored procedures
Below code you can use to parse a string like (MIKE,RYAN, JOE) and get individual elements. First parameter is string and second parameter is delimiter. In this case its ,.
|
Email TopXML