In this chapter, I’ll present recipes for user-defined functions and types. User-defined functions (UDFs) allow you to encapsulate both logic and subroutines into a single function that can then be used within your Transact-SQL queries and programmatic objects. User-defined types (UDTs) allow you to create an alias type based on an underlying system data type and enforce a specific data type, length, and nullability.

In this chapter, I’ll also cover the SQL Server user-defined table type, which can be used as a user-defined table parameter for passing table result sets within your T-SQL code.

UDF Basics

Transact-SQL user-defined functions fall into three categories: scalar, inline table-valued, and multi-statement table-valued.

A scalar user-defined function is used to return a single value based on zero or more parameters. For example, you could create a scalar UDF that accepts a CountryID as a parameter and returns the CountryNM.

An inline table-valued UDF returns a table data type based on a single SELECT statement which is used to define the returned rows and columns. Unlike a stored procedure, an inline UDF can be referenced in the FROM clause of a query, as well as be joined to other tables. Unlike a view, an inline UDF can accept parameters.

A multi-statement table-valued UDF also returns a tabular result set and is referenced in the FROM clause. Unlike inline table-valued UDFs, multi-statement UDFs aren’t constrained to using a single SELECT statement within the function definition and, instead, allow multiple Transact-SQL statements in the body of the UDF definition in order to define a single, final result set to be returned.

UDFs can also be used in places where a stored procedure can’t, like in the FROM and SELECT clauses of a query. UDFs also encourage code reusability. For example, if you create a scalar UDF that returns the CountryNM based on a CountryID, and the same function is needed across several different stored procedures, rather than repeat the 20 lines of code needed to perform the lookup, you can call the UDF function instead.

In the next few recipes, I’ll demonstrate how to create, drop, modify, and view metadata for each of these UDF types.

18-1. Creating Scalar Functions

Problem

You need to create a function to check or alter the values in the parameters passed into the function (such as you might do when checking for SQL Injection).

Solution

Create a scalar user-defined function. A scalar user-defined function accepts zero or more parameters and returns a single value. Scalar UDFs are often used for converting or translating a current value to a new value or performing other sophisticated lookups based on specific parameters. Scalar functions can be used within search, column, and join expressions.

The simplified syntax for a scalar UDF is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body RETURN scalar_expression END

Table 18-1 briefly describes each argument’s intended use.

This example creates a scalar UDF that accepts a varchar(max) data type parameter. It returns a bit value (1 or 0) based on whether the passed parameter contains suspicious values (as defined by the function). So, if the input parameter contains a call to a command such as DELETE or SHUTDOWN, the flag is set to 1:

Use AdventureWorks2014;
GO

Create Function dbo.udf_CheckForSQLInjection (@TSQLString varchar(max))
Returns bit

AS

BEGIN

DECLARE  @IsSuspect  bit;

--  UDF  assumes  string  will  be  left  padded  with  a  single  space
SET  @TSQLString  =  '  '  +  @TSQLString;

IF        (PATINDEX('%  xp_%'  ,  @TSQLString  )  <>  0 OR
      PATINDEX('%  sp_%'  ,  @TSQLString  )  <>  0      OR
      PATINDEX('%  DROP %'  ,  @TSQLString  )  <>  0   OR
      PATINDEX('%  GO %'  ,  @TSQLString  )  <>  0  OR
      PATINDEX('%  INSERT %'  ,  @TSQLString  )  <>  0 OR
      PATINDEX('%  UPDATE %'  ,  @TSQLString  )  <>  0 OR
      PATINDEX('%  DBCC %'  ,  @TSQLString  )  <>  0   OR
      PATINDEX('%  SHUTDOWN %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  ALTER %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  CREATE %'  ,  @TSQLString  )  <>  0 OR
      PATINDEX('%;%'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  EXECUTE %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  BREAK %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  BEGIN %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  CHECKPOINT %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  BREAK %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  COMMIT %'  ,  @TSQLString  )<>  0   OR
      PATINDEX('%  TRANSACTION %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  CURSOR %'  ,  @TSQLString  )<>  0   OR
      PATINDEX('%  GRANT %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  DENY %'  ,  @TSQLString  )<>  0     OR
      PATINDEX('%  ESCAPE %'  ,  @TSQLString  )<>  0   OR
      PATINDEX('%  WHILE %'  ,  @TSQLString  )<>  0    OR
      PATINDEX('%  OPENDATASOURCE %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  OPENQUERY %'  ,  @TSQLString  )<>  0  OR
      PATINDEX('%  OPENROWSET %'  ,  @TSQLString  )<>  0      OR
      PATINDEX('%  EXEC %'  ,  @TSQLString  )<>  0)

BEGIN
      SELECT  @IsSuspect  =     1;
END
ELSE
BEGIN
      SELECT  @IsSuspect  =     0;
END
      RETURN  (@IsSuspect);
END

GO

Next, you should test the function by evaluating three different string input values. The first contains a SELECT statement:

Use AdventureWorks2014;
GO
SELECT dbo.udf_CheckForSQLInjection ('SELECT * FROM HumanResources.Department'),

This query returns the following:

0

The next string contains the SHUTDOWN command:

Use AdventureWorks2014;
GO
SELECT dbo.udf_CheckForSQLInjection (';SHUTDOWN'),

This query returns the following:

1

The last string tested contains the DROP command:

Use AdventureWorks2014;
GO
SELECT dbo.udf_CheckForSQLInjection ('DROP HumanResources.Department'),

This query returns the following:

1

In the next example, I create a user-defined function that can be used to set a string to the proper case:

Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_ProperCase(@UnCased varchar(max))
RETURNS varchar(max)
AS
BEGIN
SET @UnCased = LOWER(@UnCased)
DECLARE @C int
SET @C = ASCII('a')
WHILE @C <= ASCII('z') BEGIN
SET @UnCased = REPLACE( @UnCased, ' ' + CHAR(@C), ' ' + CHAR(@C-32)) SET @C = @C + 1
END
SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased, LEN(@UnCased)-1)

RETURN @UnCased END
GO

Once the user-defined function is created, the string to modify (to proper case) can be used as the function parameter:

SELECT dbo.udf_ProperCase(DocumentSummary)
FROM Production.Document
WHERE FileName = 'Installing Replacement Pedals.doc'

This query returns the following:

Detailed Instructions For Replacing Pedals With Adventure Works Cycles Replacement Pedals. Instructions Are Applicable To All Adventure Works Cycles Bicycle Models And Replacement Pedals. Use Only Adventure Works Cycles Parts When Replacing Worn Or Broken Components.

How It Works

This recipe demonstrated a scalar UDF, which in this case accepted one parameter and returned a single value. Some of the areas where you can use a scalar function in your Transact-SQL code include the following:

• A column expression in a SELECT or GROUP BY clause
• A search condition for a JOIN in a FROM clause
• A search condition of a WHERE or HAVING clause

The recipe began by defining the UDF name and parameter:

CREATE FUNCTION dbo.udf_CheckForSQLInjection (@TSQLString varchar(max))

The @TSQLString parameter held the varchar(max) string to be evaluated. In the next line of code, the scalar_return_data_type was defined as bit. This means that the single value returned by the function will be the bit data type:

RETURNS BIT AS

The BEGIN marked the start of the function_body, where the logic to return the bit value was formulated:

BEGIN

A local variable was created to hold the bit value. Ultimately, this is the parameter that will be passed as the function’s output:

DECLARE @IsSuspect bit

Next, the string passed to the UDF had a space concatenated to the front of it:

-- UDF assumes string will be left padded with a single space SET @TSQLString = ' ' + @TSQLString

The @TSQLString was padded with an extra space in order to make the search of suspicious words or patterns easier to do. For example, if the suspicious word is at the beginning of the @TSQLString and you were searching for the word drop, you would have to use PATINDEX to search for both '%DROP %' and '% DROP %'. Of course, searching '%DROP %' could give you false positives, such as the word gumdrop, so you should prevent this confusion by padding the beginning of the string with a space.

In the IF statement, @TSQLString was evaluated using PATINDEX. For each evaluation, if a match were found, the condition would evaluate to TRUE.

IF   (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR PATINDEX('% sp_%' , @TSQLString ) <> 0 OR PATINDEX('% DROP %' , @TSQLString ) <> 0 OR PATINDEX('% GO %' , @TSQLString ) <> 0 OR PATINDEX('% BREAK %' , @TSQLString )<> 0 OR

If any of the conditions evaluate to TRUE, the @IsSuspect bit flag would be set to 1:

BEGIN
      SELECT  @IsSuspect  =     1;
END
ELSE
BEGIN
      SELECT  @IsSuspect  =     0;
END

The RETURN keyword was used to pass the scalar value of the @IsSuspect variable back to the caller:

RETURN (@IsSuspect)

The END keyword was then used to close the UDF, and GO was used to end the batch:

END
GO

The new scalar UDF created in this recipe was then used to check three different string values. The first string, SELECT * FROM HumanResources.Department, came up clean, but the second and third strings, SHUTDOWN and DROP HumanResources.Department, both returned a bit value of 1 because they matched the suspicious word searches in the function’s IF clause.

SQL Server doesn’t provide a built-in proper case function, so in my second example, I demonstrate creating a user-defined function that performs this action. The first line of the CREATE FUNCTION definition defines the name and parameter expected—in this case, a varchar(max) data type parameter:

CREATE FUNCTION dbo.udf_ProperCase(@UnCased varchar(max))

The RETURNS keyword defines what data type would be returned by the function after the logic has been applied:

RETURNS varchar(max)
AS
BEGIN

Next, the variable passed to the function was first modified to lowercase using the LOWER function:

SET @UnCased = LOWER(@UnCased)

A new integer local variable, @C, was set to the ASCII value of the letter a:

DECLARE @C int
SET @C = ASCII('a')

A WHILE loop was initiated to go through every letter in the alphabet and, for each, search for a space preceding that letter and then replace each occurrence of a letter preceded by a space with the uppercase version of the character:

WHILE @C <= ASCII('z') BEGIN
SET @UnCased = REPLACE( @UnCased, ' ' + CHAR(@C), ' ' + CHAR(@C-32)) SET @C = @C + 1
END

The conversion to uppercase is performed by subtracting 32 from the ASCII integer value of the lowercase character. For example, the ASCII value for a lowercase a is 97, while the uppercase A is 65:

SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased, LEN(@UnCased)-1)

The final proper case string value of @UnCased is then returned from the function:

RETURN @UnCased END GO

Next, I used the new scalar UDF in the SELECT clause of a query to convert the DocumentSummary text to the proper case:

SELECT dbo.udf_ProperCase(DocumentSummary)

18-2. Creating Inline Functions

Problem

You need to create a reusable query that can return data in a table form and potentially be joined to tables in queries found throughout views and stored procedures in your database.

Solution

Create an inline user-defined function. An inline UDF returns a table data type. In the UDF definition, you do not explicitly define the returned table but instead use a single SELECT statement for defining the returned rows and columns. An inline UDF uses one or more parameters and returns data using a single SELECT statement. Inline UDFs are very similar to views, in that they are referenced in the FROM clause. However, unlike views, UDFs can accept parameters that can then be used in the function’s SELECT statement. The basic syntax is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] scalar_parameter_data_type [ = default ]
} [ ,...n ] ]
) RETURNS TABLE [ AS ] RETURN [ ( ] select_stmt [ ) ]

Table 18-2 details the arguments of this command.

The following example demonstrates creating an inline table UDF that accepts an integer parameter and returns the associated addresses of a business entity:

Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_ReturnAddress
(@BusinessEntityID int)
RETURNS TABLE
AS RETURN (
SELECT t.Name AddressTypeNM, a.AddressLine1, a.City,
a.StateProvinceID, a.PostalCode
FROM Person.Address a
INNER JOIN Person.BusinessEntityAddress e
ON a.AddressID = e.AddressID
INNER JOIN Person.AddressType t
ON e.AddressTypeID = t.AddressTypeID
WHERE e.BusinessEntityID = @BusinessEntityID )
;
GO

Next, the new function is tested in a query, referenced in the FROM clause for business entity 332:

Use AdventureWorks2014;
GO
SELECT AddressTypeNM, AddressLine1, City, PostalCode
FROM dbo.udf_ReturnAddress(332);
GO

This query returns the following:

How It Works

In this recipe, I created an inline table UDF to retrieve the addresses of a business entity based on the @BusinessEntityID value passed. The UDF started off just like a scalar UDF, but the RETURNS command used a TABLE data type (which is what distinguishes it from a scalar UDF):

CREATE FUNCTION dbo.udf_ReturnAddress
(@BusinessEntityID int)
RETURNS TABLE
AS

After the AS keyword, the RETURN statement was issued with a single SELECT statement in parentheses:

RETURN (
SELECT t.Name AddressTypeNM, a.AddressLine1, a.City,
a.StateProvinceID, a.PostalCode
FROM Person.Address a
INNER JOIN Person.BusinessEntityAddress e
ON a.AddressID = e.AddressID
INNER JOIN Person.AddressType t
ON e.AddressTypeID = t.AddressTypeID
WHERE e.BusinessEntityID = @BusinessEntityID )
;
GO

After it was created, the new inline UDF was then used in the FROM clause of a SELECT query. The @BusinessEntityID value of 332 was passed into the function in parentheses:

SELECT AddressTypeNM, AddressLine1, City, PostalCode
FROM dbo.udf_ReturnAddress(332);
GO

This function then returned a result set, just like when you are querying a view or a table. Also, just like a view or stored procedure, the query you create to define this function must be tuned as you would a regular SELECT statement. Using an inline UDF offers no inherent performance benefits over using a view or stored procedure.

18-3. Creating Multi-Statement User-Defined Functions

Problem

You need to create a function that can accept multiple parameters and that will be able to execute multiple SELECT statements.

Solution

Create a multi-statement table user-defined function. Multi-statement table UDFs are referenced in the FROM clause just like inline UDFs, but unlike inline UDFs, they are not constrained to using a single SELECT statement within the function definition. Instead, multi-statement UDFs can use multiple Transact-SQL statements in the body of the UDF definition in order to define that a single, final result set be returned. The basic syntax of a multi-statement table UDF is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] )
RETURNS @return_variable TABLE <table_type_definition> [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN
function_body RETURN END

Table 18-3 describes the arguments of this command.

Notice the RETURNS keyword, which defines a table variable definition. Also notice the RETURN keyword at the end of the function, which doesn’t have any parameter or query after it, because it is assumed that the defined table variable will be returned.

In this example, a multi-statement UDF will be created that accepts two parameters: one to hold a string and the other to define how that string will be delimited. The string is then broken apart into a result set based on the defined delimiter:

-- Creates a UDF that returns a string array as a table result set
Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_ParseArray
( @StringArray varchar(max), @Delimiter char(1) ) RETURNS @StringArrayTable TABLE (Val varchar(50))
AS
BEGIN
DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) != @Delimiter
      SET @StringArray = @StringArray + @Delimiter
WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN
SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray)
INSERT INTO @StringArrayTable (Val)
      VALUES (LEFT(@StringArray, @Delimiter_position - 1));

SELECT @StringArray = STUFF(@StringArray, 1, @Delimiter_position, '') ;
END

RETURN
END
GO

Now it will be used to break apart a comma-delimited array of values:

SELECT Val
FROM dbo.udf_ParseArray('A,B,C,D,E,F,G', ','),
GO

This returns the following results:

Val
A
B
C
D
E
F
G

How It Works

The multi-statement table UDF in this recipe was created using two parameters, the first to hold a string and the second to define the character that delimits the string:

CREATE FUNCTION dbo.udf_ParseArray
( @StringArray varchar(max), @Delimiter char(1) )

Next, a table variable was defined after the RETURNS token. The @StringArrayTable was used to hold the values of the string array after being shredded into the individual values:

RETURNS @StringArrayTable TABLE (Val varchar(50))

The function body started after AS and BEGIN:

AS
BEGIN

A local variable was created to hold the delimiter position in the string:

DECLARE @Delimiter_position int

If the last character of the string array wasn’t the delimiter value, then the delimiter value was concatenated to the end of the string array:

IF RIGHT(@StringArray,1) != @Delimiter
SET @StringArray = @StringArray + @Delimiter

A WHILE loop was created, looping until there were no remaining delimiters in the string array:

WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN

Within the loop, the position of the delimiter was identified using CHARINDEX:

SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray)

The LEFT function was used with the delimiter position to extract the individual-delimited string part into the table variable:

INSERT INTO @StringArrayTable (Val)
      VALUES (LEFT(@StringArray, @Delimiter_position - 1));

The inserted chunk was then removed from the string array using the STUFF function:

SELECT @StringArray = STUFF(@StringArray, 1, @Delimiter_position, '') ;

STUFF is used to delete a chunk of characters and insert another character string in its place. The first parameter of the STUFF function is the character expression, which in this example is the string array. The second parameter is the starting position of the deleted and inserted text, and in this case I am removing text from the string starting at the first position and stopping at the first delimiter. The third parameter is the length of the characters to be deleted, which for this example is the delimiter-position variable value. The last argument is the string to be inserted, which in this case was a blank string represented by two single quotes. The net effect is that the first comma-separated entry was replaced by an empty string—the same result as if the first entry had been deleted.

This process of inserting values continued until there were no longer delimiters in the string array. After this, the WHILE loop ended, and RETURN was called to return the table variable result set:

END RETURN END GO

The new UDF was then referenced in the FROM clause. The first parameter of the UDF was a comma-delimited list of letters. The second parameter was the delimiting parameter (a comma):

-- Now use it to break apart a comma-delimited array
SELECT Val
FROM dbo.udf_ParseArray('A,B,C,D,E,F,G', ','),
GO

The list was then broken into a result set, with each individual letter as its own row. As you can see, multi-statement table UDFs allow for much more sophisticated programmability than an inline table-valued UDF, which can use only a single SELECT statement.

18-4. Modifying User-Defined Functions

Problem

You have determined that a user-defined function is not producing the desired results. You need to modify this function.

Solution

A function can be modified by using the ALTER FUNCTION command, as I demonstrate in this next recipe:

Use AdventureWorks2014;
GO
ALTER FUNCTION dbo.udf_ParseArray ( @StringArray varchar(max),
@Delimiter char(1),
@MinRowSelect int,
@MaxRowSelect int)
RETURNS @StringArrayTable TABLE (RowNum int IDENTITY(1,1), Val varchar(50))
AS
BEGIN

DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) != @Delimiter
      SET @StringArray = @StringArray + @Delimiter;
WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN
SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray);

INSERT INTO @StringArrayTable (Val)
      VALUES (LEFT(@StringArray, @Delimiter_position - 1));

SELECT @StringArray = STUFF(@StringArray, 1, @Delimiter_position, ''),
END
DELETE @StringArrayTable
      WHERE RowNum < @MinRowSelect OR RowNum > @MaxRowSelect;
RETURN
END
GO

/* Now use it to break apart a comma delimited array */
Use AdventureWorks2014;
GO
SELECT RowNum,Val
FROM dbo.udf_ParseArray('A,B,C,D,E,F,G', ',',3,5);
GO

This query returns the following:

How It Works

ALTER FUNCTION allows you to modify an existing UDF by using syntax that is almost identical to that of CREATE FUNCTION, with some limitations:

• You can’t change the name of the function using ALTER FUNCTION. What you’re doing is replacing the code of an existing function—therefore, the function needs to exist first.
• You can’t convert a scalar UDF to a table UDF (either inline or multi-statement), and you cannot convert a table UDF to a scalar UDF.

In this recipe, the udf_ParseArray from the previous recipe was modified to add two new parameters, @MinRowSelect and @MaxRowSelect:

ALTER FUNCTION dbo.udf_ParseArray ( @StringArray varchar(max),
@Delimiter char(1) ,
@MinRowSelect int,
@MaxRowSelect int)

The @StringArrayTable table variable also had a new column added to it called RowNum, which was given the IDENTITY property (meaning that it will increment an integer value for each row in the result set):

RETURNS @StringArrayTable TABLE (RowNum int IDENTITY(1,1), Val varchar(50))

The other modification came after the WHILE loop was finished. Any RowNum values less than the minimum or maximum values were deleted from the @StringArrayTable table array:

DELETE @StringArrayTable 
      WHERE RowNum < @MinRowSelect OR RowNum > @MaxRowSelect;

After altering the function, the function was called using the two new parameters to define the row range to view (in this case, rows 3 through 5):

Use AdventureWorks2014;
GO
SELECT RowNum,Val
FROM dbo.udf_ParseArray('A,B,C,D,E,F,G', ',',3,5);
GO

This returned the third, fourth, and fifth characters from the string array passed to the UDF.

18-5. Viewing UDF Metadata

Problem

You want to view a list which includes the definitions of all user-defined functions in your database.

Solution

Query the catalog view sys.sql_modules. You can use the sys.sql_modules catalog view to view information regarding all user-defined functions within a database. In this recipe, I will demonstrate how to view the name and the definition of each function.

Use AdventureWorks2014;
GO
SELECT name, o.type_desc
      , (Select definition as [processing-instruction(definition)]
            FROM sys.sql_modules
            Where object_id = s.object_id
            FOR XML PATH(''), TYPE
      )
FROM sys.sql_modules s
INNER JOIN sys.objects o
      ON s.object_id = o.object_id
WHERE o.type IN ('IF', -- Inline Table UDF
      'TF', -- Multistatement Table UDF
      'FN') -- Scalar UDF
;

How It Works

The sys.sql_modules and sys.objects system views are used to return the UDF name, type description, and SQL definition in a query result set:

FROM sys.sql_modules s
INNER JOIN sys.objects o
        ON s.object_id = o.object_id

The SQL definition is maintained in sys.sql_modules. In this example, I have shown how to return the result in a clickable format, which will render the function formatted as it is stored in the database (and for readability). This is done through the FOR XML PATH command using the processing-instruction directive:

, (Select definition as [processing-instruction(definition)]
      FROM sys.sql_modules
      Where object_id = s.object_id
      FOR XML PATH(''), TYPE
  )

Because sys.sql_modules contains rows for other object types, sys.objects must also be qualified to return only UDF rows:

WHERE o.type IN ('IF', -- Inline Table UDF
      'TF', -- Multistatement Table UDF
      'FN') -- Scalar UDF
;

Benefitting from UDFs

User-defined functions are useful for both the performance enhancements they provide because of their cached execution plans and their ability to encapsulate reusable code. In this next section, I’ll discuss some of the benefits of UDFs. For example, scalar functions in particular can be used to help make code more readable and allow you to apply lookup rules consistently across an application rather than repeating the same code multiple times throughout different stored procedures or views.

Table-valued functions are also useful for allowing you to apply parameters to results; for example, using a parameter to define row-level security for a data set (demonstrated later in the chapter).

The next few recipes will demonstrate some of the more common and beneficial ways in which user-defined functions are used in the field.

18-6. Maintaining Reusable Code

Problem

You have discovered that a code segment has been duplicated numerous times throughout your database. You want to reduce the amount of code bloat in the database.

Solution

Create an appropriate UDF. For instance, scalar UDFs allow you to reduce code bloat by encapsulating logic within a single function, rather than repeating the logic multiple times wherever it happens to be needed.

The following scalar, user-defined function is used to determine the kind of personal computer that an employee will receive. There are several lines of code that evaluate different input parameters, including the employee’s title, hire date, and salaried status. Rather than include this logic in multiple areas across your database application, you can encapsulate the logic in a single function.

Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_GET_AssignedEquipment (@Title nvarchar(50), @HireDate datetime, @SalariedFlag bit)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @EquipmentType nvarchar(50)
IF @Title LIKE 'Chief%' OR
      @Title LIKE 'Vice%' OR
      @Title = 'Database Administrator'
BEGIN
      SET @EquipmentType = 'PC Build A' ;
END
IF @EquipmentType IS NULL AND @SalariedFlag = 1
BEGIN
      SET @EquipmentType = 'PC Build B' ;
END
IF @EquipmentType IS NULL AND @HireDate < '1/1/2002'
BEGIN
      SET @EquipmentType = 'PC Build C' ;
END
IF @EquipmentType IS NULL
BEGIN
      SET @EquipmentType = 'PC Build D' ;
END
RETURN @EquipmentType ;
END
GO

Once you’ve created it, you can use this scalar function in many areas of your Transact-SQL code without having to recode the logic within. In the following example, the new scalar function is used in the SELECT, GROUP BY, and ORDER BY clauses of a query:

Use AdventureWorks2014; 
GO
SELECT PC_Build = dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag)
      , Employee_Count = COUNT(*)
FROM HumanResources.Employee
GROUP BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag)
ORDER BY dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag);

This query returns the following:

This second query uses the scalar function in both the SELECT and WHERE clauses, too:

Use AdventureWorks2014;
GO
SELECT JobTitle,BusinessEntityID
      ,PC_Build = dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag)
FROM HumanResources.Employee
WHERE dbo.udf_GET_AssignedEquipment(JobTitle, HireDate, SalariedFlag)
      IN ('PC Build A', 'PC Build B'),

This returns the following (abridged) results:

How It Works

Scalar user-defined functions can help you encapsulate business logic so that it isn’t repeated across your code, providing a centralized location for you to make a single modification to a single function when necessary. This also provides consistency so that you and other database developers are using and writing the same logic in the same way. One other benefit is code readability, particularly with large queries that perform multiple lookups or evaluations.

18-7. Cross-Referencing Natural Key Values

A surrogate key is an artificial primary key, as opposed to a natural key, which represents a unique descriptor of data (for example, a Social Security number is an example of a natural key, but an IDENTITY property column is a surrogate key). IDENTITY values are often used as surrogate primary keys, but are also referenced as foreign keys.

In my own OLTP and star schema database designs, I assign each table a surrogate key by default, unless there is a significant reason not to do so. Doing this helps you abstract your own unique key from any external legacy natural keys. If you are using, for example, an EmployeeNumber that comes from the HR system as your primary key instead, you could run into trouble later if that HR system decides to change its data type (forcing you to change the primary key, any foreign key references, and composite primary keys). Surrogate keys help protect you from changes like this because they are under your control, and thus make good primary keys. You can keep your natural keys’ unique constraints without worrying about external changes impacting your primary or foreign keys.

When importing data from legacy systems into production tables, you’ll often still need to reference the natural key in order to determine which rows get inserted, updated, or deleted. This isn’t very tricky if you’re just dealing with a single column (for example, EmployeeID, CreditCardNumber, SSN, UPC). However, if the natural key is made up of multiple columns, the cross-referencing to the production tables may not be quite so easy.

Problem

You are using natural keys and surrogate keys within your database. You need to verify that a natural key exists prior to performing certain actions.

Solution

You can create a scalar user-defined function that can be used to perform natural key lookups.

The following demonstrates a scalar user-defined function that can be used to simplify natural key lookups by checking for their existence prior to performing an action. To set up the example, I’ll create a few objects and execute a few commands.

First, I’ll create a new table that uses its own surrogate keys, along with three columns that make up the composite natural key (these three columns form the unique value that was received from the legacy system):

Use AdventureWorks2014;
GO
CREATE TABLE dbo.DimProductSalesperson
(DimProductSalespersonID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ProductCD char(10) NOT NULL,
CompanyNBR int NOT NULL,
SalespersonNBR int NOT NULL );
GO

Next, I’ll create a staging table that holds rows from the external legacy data file. For example, this table could be populated from an external text file that is dumped out of the legacy system. This table doesn’t have a primary key, because it is just used to hold data prior to being moved to the dbo.DimProductSalesperson table:

Use AdventureWorks2014;
GO
CREATE TABLE dbo.Staging_PRODSLSP ( ProductCD char(10) NOT NULL,
CompanyNBR int NOT NULL,
SalespersonNBR int NOT NULL ); 
GO

Next, I’ll insert two rows into the staging table:

Use AdventureWorks2014;
GO
INSERT dbo.Staging_PRODSLSP (ProductCD, CompanyNBR, SalespersonNBR)
      VALUES ('2391A23904', 1, 24);
INSERT dbo.Staging_PRODSLSP (ProductCD, CompanyNBR, SalespersonNBR)
      VALUES ('X129483203', 1, 34);
GO

Now these two rows can be inserted into the DimProductSalesperson table using the following query, which doesn’t use a scalar UDF:

Use AdventureWorks2014;
GO
INSERT Into dbo.DimProductSalesperson (ProductCD, CompanyNBR, SalespersonNBR)
      SELECT s.ProductCD, s.CompanyNBR, s.SalespersonNBR
            FROM dbo.Staging_PRODSLSP s
            LEFT OUTER JOIN dbo.DimProductSalesperson d
                  ON s.ProductCD = d.ProductCD
                  AND s.CompanyNBR = d.CompanyNBR
                  AND s.SalespersonNBR = d.SalespersonNBR
      WHERE d.DimProductSalespersonID IS NULL;
GO

Because each column forms the natural key, I must LEFT JOIN each column from the inserted table against the staging table and then check to see whether the row does not already exist in the destination table using IS NULL.

An alternative to this, allowing you to reduce the code in each INSERT/UPDATE/DELETE, is to create a scalar UDF like the following:

Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_GET_Check_NK_DimProductSalesperson (@ProductCD char(10), @CompanyNBR int, @SalespersonNBR int )
RETURNS bit
AS
BEGIN
DECLARE @Exists bit
IF EXISTS (SELECT DimProductSalespersonID
            FROM dbo.DimProductSalesperson
            WHERE @ProductCD = @ProductCD
            AND @CompanyNBR = @CompanyNBR
            AND @SalespersonNBR = @SalespersonNBR)
BEGIN
      SET @Exists = 1;
END
ELSE
BEGIN
      SET @Exists = 0;
END
RETURN @Exists
END
GO

The UDF certainly looks like more code up front, but you’ll realize its benefits later during the data-import process. For example, now you can rewrite the INSERT operation demonstrated earlier, as follows:

Use AdventureWorks2014;
GO
INSERT INTO dbo.DimProductSalesperson(ProductCD, CompanyNBR, SalespersonNBR)
      SELECT ProductCD, CompanyNBR, SalespersonNBR
      FROM dbo.Staging_PRODSLSP
      WHERE dbo.udf_GET_Check_NK_DimProductSalesperson
       (ProductCD, CompanyNBR, SalespersonNBR) = 0;
GO

How It Works

In this recipe, I demonstrated how to create a scalar UDF that returned a bit value based on three parameters. If the three values already existed for a row in the production table, a 1 was returned; otherwise, a 0 was returned. Using this function simplifies the INSERT/UPDATE/DELETE code that you must write in situations where a natural key spans multiple columns.

Walking through the UDF code, the first lines defined the UDF name and parameters. Each of these parameters was for the composite natural key in the staging and production tables:

CREATE FUNCTION dbo.udf_GET_Check_NK_DimProductSalesperson (@ProductCD char(10), @CompanyNBR int, @SalespersonNBR int )

Next, a bit data type was defined to be returned by the function:

RETURNS bit
AS
BEGIN

A local variable was created to hold the bit value:

DECLARE @IfExists bit

An IF statement was used to check for the existence of a row matching all three parameters for the natural composite key. If there is a match, the local variable is set to 1. If not, it is set to 0.

IF EXISTS (SELECT DimProductSalespersonID
                  FROM dbo.DimProductSalesperson
                  WHERE @ProductCD = @ProductCD
                  AND @CompanyNBR = @CompanyNBR
                  AND @SalespersonNBR = @SalespersonNBR)
BEGIN
      SET @Exists = 1;
END
ELSE
BEGIN
      SET @Exists = 0;
END

The local variable was then passed back to the caller:

RETURN @IfExists END
GO

The function was then used in the WHERE clause, extracting from the staging table those rows that returned a 0 from the scalar UDF and therefore do not exist in the DimProductSalesperson table:

WHERE dbo.udf_GET_Check_NK_DimProductSalesperson (ProductCD, CompanyNBR, SalespersonNBR) = 0

18-8. Replacing a View with a Function

Problem

You have a view in your database that you need to parameterize.

Solution

Create a multi-statement UDF to replace the view. Multi-statement UDFs allow you to return data in the same way you would from a view, only with the ability to manipulate data like a stored procedure.

In this example, a multi-statement UDF is created to apply row-based security based on the caller of the function. Only rows for the specified salesperson will be returned. In addition to this, the second parameter is a bit flag that controls whether rows from the SalesPersonQuotaHistory table will be returned in the results.

Use AdventureWorks2014;
GO
CREATE FUNCTION dbo.udf_SEL_SalesQuota ( @BusinessEntityID int, @ShowHistory bit )
RETURNS @SalesQuota TABLE (BusinessEntityID int, QuotaDate datetime, SalesQuota money)

AS
BEGIN
INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
      SELECT BusinessEntityID, ModifiedDate, SalesQuota
      FROM Sales.SalesPerson
      WHERE BusinessEntityID = @BusinessEntityID;
IF @ShowHistory = 1
BEGIN
INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
      SELECT BusinessEntityID, QuotaDate, SalesQuota
      FROM Sales.SalesPersonQuotaHistory
      WHERE BusinessEntityID = @BusinessEntityID;
END
RETURN
END
GO

After the UDF is created, the following query is executed to show sales-quota data for a specific salesperson from the Salesperson table:

Use AdventureWorks2014;
GO

SELECT BusinessEntityID, QuotaDate, SalesQuota
      FROM dbo.udf_SEL_SalesQuota (275,0);

This query returns the following:

Next, the second parameter is switched from a 0 to a 1 in order to display additional rows for Salespersons 275 from the SalesPersonQuotaHistory table:

Use AdventureWorks2014;
GO

SELECT BusinessEntityID, QuotaDate, SalesQuota
      FROM dbo.udf_SEL_SalesQuota (275,1);

This returns the following (abridged) results:

How It Works

This recipe demonstrated a multi-statement table-valued UDF to return sales-quota data based on the BusinessEntityID value that was passed. It also included a second bit flag that controlled whether history was also returned.

Walking through the function, you’ll notice that the first few lines defined the input parameters (something that a view doesn’t allow):

CREATE FUNCTION dbo.udf_SEL_SalesQuota ( @BusinessEntityID int, @ShowHistory bit )

After this, the table columns that are to be returned by the function were defined:

RETURNS @SalesQuota TABLE (BusinessEntityID int, QuotaDate datetime, SalesQuota money)

The function body included two separate batch statements, the first being an INSERT into the table variable of rows for the specific salesperson:

AS
BEGIN
INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
      SELECT BusinessEntityID, ModifiedDate, SalesQuota
      FROM Sales.SalesPerson
      WHERE BusinessEntityID = @BusinessEntityID;

Next, an IF statement (another construct not allowed in views) evaluated the bit parameter. If equal to 1, quota history will also be inserted into the table variable:

IF @ShowHistory = 1
BEGIN
INSERT Into @SalesQuota(BusinessEntityID, QuotaDate, SalesQuota)
      SELECT BusinessEntityID, QuotaDate, SalesQuota
      FROM Sales.SalesPersonQuotaHistory
      WHERE BusinessEntityID = @BusinessEntityID;
END

Lastly, the RETURN keyword signaled the end of the function (and, unlike a scalar function, no local variable is designated after it):

RETURN END
GO

Although the UDF contained Transact-SQL not allowed in a view, it was still able to be referenced in the FROM clause:

Use AdventureWorks2014;
GO

SELECT BusinessEntityID, QuotaDate, SalesQuota
      FROM dbo.udf_SEL_SalesQuota (275,0);

The results could be returned in a view using a UNION statement, but with that you wouldn’t be able to have the control logic to either show or not show history in a single view.

In this recipe, I demonstrated a method to create your own parameter-based result sets. This can be used to implement row-based security, which is not built natively into the SQL Server security model. You can use functions to return only the rows that are allowed to be viewed by designating input parameters to filter the data.

18-9. Dropping a Function

Problem

You no longer need a user-defined function in your database. You have confirmed that it is not used anywhere else, and you need to remove it from the database.

Solution

You can use DROP FUNCTION to remove a function. The syntax, like other DROP commands, is very straightforward.

DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ]

Table 18-4 details the arguments of this command.

This recipe demonstrates how to drop the dbo.udf_ParseArray function created in an earlier recipe.

Use AdventureWorks2014;
GO
DROP FUNCTION dbo.udf_ParseArray;

How It Works

Although there are three different types of user-defined functions (scalar, inline, and multi-statement), you need only drop them using the single DROP FUNCTION command. You can also drop more than one UDF in a single statement; for example:

Use AdventureWorks2014;
GO
DROP FUNCTION dbo.udf_ParseArray, dbo.udf_ReturnAddress,
dbo.udf_CheckForSQLInjection;

UDT Basics

User-defined types are useful for defining a consistent data type that is named after a known business or application-centric attribute, such as PIN, PhoneNBR, or EmailAddress. Once a user-defined type is created in the database, it can be used within columns, parameters, and variable definitions, providing a consistent underlying data type. The next two recipes will show you how to create and drop user-defined types. Note that unlike some other database objects, there isn’t a way to modify an existing type using an ALTER command.

18-10. Creating and Using User-Defined Types

Problem

You have a frequently used account-number field throughout the database. You want to try to enforce a consistent definition for this field while providing convenience to the database developers.

Solution

Create a user-defined type (also called an alias data type), which is a specific configuration of a data type that is given a user-specified name, data type, length, and nullability. You can use all base data types except the xml data type.

The basic syntax for creating a user-defined type is as follows:

CREATE TYPE [ schema_name. ] type_name {
FROM base_type
[ (precision [ ,scale ] ) ]
[ NULL | NOT NULL ] }

Table 18-5 details the arguments of these commands.

In this recipe, I’ll create a new type based on a 14-character string:

Use AdventureWorks2014;
GO
/*
-- In this example, we assume the company's Account number will
-- be used in multiple tables, and that it will always have a fixed
-- 14 character length and will never allow NULL values
*/

CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL;
GO

Next, I’ll use the new type in the column definition of two tables:

Use AdventureWorks2014;
GO
-- The new data type is now used in two different tables
CREATE TABLE dbo.InventoryAccount
(InventoryAccountID int NOT NULL,
InventoryID int NOT NULL,
InventoryAccountNBR AccountNBR);
GO
CREATE TABLE dbo.CustomerAccount
(CustomerAccountID int NOT NULL,
CustomerID int NOT NULL,
CustomerAccountNBR AccountNBR); 
GO

This type can also be used in the definition of a local variable or input parameter. For example, the following stored procedure uses the new data type to define the input parameter for a stored procedure:

Use AdventureWorks2014;
GO
CREATE PROCEDURE dbo.usp_SEL_CustomerAccount
@CustomerAccountNBR AccountNBR

AS
SELECT CustomerAccountID, CustomerID, CustomerAccountNBR
FROM dbo.CustomerAccount
WHERE CustomerAccountNBR = CustomerAccountNBR;
GO

Next, a local variable is created using the new data type and is passed to the stored procedure:

Use AdventureWorks2014;
GO
DECLARE @CustomerAccountNBR AccountNBR
SET @CustomerAccountNBR = '1294839482';
EXECUTE dbo.usp_SEL_CustomerAccount @CustomerAccountNBR;
GO

To view the underlying base type of the user-defined type, you can use the sp_help system stored procedure:

Use AdventureWorks2014;
GO
EXECUTE sp_help 'dbo.AccountNBR';
GO

This returns the following results (only a few columns are displayed for presentation purposes):

How It Works

In this recipe, a new user-defined type called dbo.AccountNBR was created with a char(14) data type and NOT NULL. Once the user-defined type was created, it was then used in the column definition of two different tables:

CREATE TABLE dbo.InventoryAccount
(InventoryAccountID int NOT NULL,
InventoryID int NOT NULL,
InventoryAccountNBR AccountNBR);
GO
CREATE TABLE dbo.CustomerAccount
(CustomerAccountID int NOT NULL,
CustomerID int NOT NULL,
CustomerAccountNBR AccountNBR);
GO

Because NOT NULL was already inherent in the data type, it wasn’t necessary to explicitly define it in the column definition.

After creating the tables, a stored procedure was created that used the new data type in the input parameter definition. The procedure was then called using a local variable that also used the new type.

Although Transact-SQL types may be an excellent convenience for some developers, creating your application’s data dictionary and abiding by the data types may suit the same purpose. For example, if an AccountNBR is always 14 characters, as a DBA/developer, you can communicate and check to make sure that new objects are using a consistent name and data type.

18-11. Identifying Dependencies on User-Defined Types

Problem

You want to list all of the columns and parameters that have a dependency on a user-defined data type within your database.

Solution

Query the sys.types catalog view. Before showing you how to remove a user-defined data type, you’ll need to know how to identify all database objects that depend on that type. As you’ll see later, removing a UDT doesn’t automatically cascade changes to the dependent table.

This example shows you how to identify which database objects are using the specified user-defined type. The first query in the recipe displays all columns that use the AccountNBR user-defined type:

Use AdventureWorks2014;
GO
SELECT Table_Name = OBJECT_NAME(c.object_id) , Column_name = c.name
FROM sys.columns c
      INNER JOIN sys.types t
            ON c.user_type_id = t.user_type_id
WHERE t.name = 'AccountNBR';

This query returns the following:

This next query shows any procedures or functions that have parameters defined using the AccountNBR user-defined type:

Use AdventureWorks2014;
GO
/*
-- Now see which parameters reference the AccountNBR data type
*/
SELECT ProcFunc_Name = OBJECT_NAME(p.object_id) , Parameter_Name = p.name
FROM sys.parameters p
      INNER JOIN sys.types t
            ON p.user_type_id = t.user_type_id
WHERE t.name = 'AccountNBR';

This query returns the following:

How It Works

To report which table columns use the user-defined type, the system catalog views sys.columns and sys.types are used:

FROM sys.columns c
      INNER JOIN sys.types t
      ON c.user_type_id = t.user_type_id

The sys.columns view contains a row for each column defined for a table-valued function, table, and view in the database. The sys.types view contains a row for each user and system data type.

To identify which function or procedure parameters reference the user-defined type, the system catalog views sys.parameters and sys.types are used:

FROM sys.parameters p
      INNER JOIN sys.types t
      ON p.user_type_id = t.user_type_id

The sys.parameters view contains a row for each database object that can accept a parameter, including stored procedures, for example.

Identifying which objects reference a user-defined type is necessary if you plan on dropping the user-defined type, as the next recipe demonstrates.

18-12. Passing Table-Valued Parameters

Problem

You have an application that calls a stored procedure repetitively to insert singleton records. You would like to alter this process to reduce the number of calls to this stored procedure.

Solution

Table-valued parameters can be used to pass rowsets to stored procedures and user-defined functions. This functionality allows you to encapsulate multi-rowset capabilities within stored procedures and functions without having to make multiple row-by-row calls to data-modification procedures or create multiple input parameters that inelegantly translate to multiple rows.

For example, the following stored procedure has several input parameters that are used to insert rows into the Department table:

Use AdventureWorks2014;
GO
CREATE PROCEDURE dbo.usp_INS_Department_Oldstyle
@Name_l nvarchar(50),
@GroupName_l nvarchar(50),
@Name_2 nvarchar(50),
@GroupName_2 nvarchar(50),
@Name_3 nvarchar(50),
@GroupName_3 nvarchar(50),
@Name_4 nvarchar(50),
@GroupName_4 nvarchar(50),
@Name_5 nvarchar(50),
@GroupName_5 nvarchar(50)

AS
INSERT INTO HumanResources.Department(Name, GroupName)
      VALUES (@Name_l, @GroupName_l)
INSERT INTO HumanResources.Department(Name, GroupName)
      VALUES (@Name_2, @GroupName_2);
INSERT INTO HumanResources.Department(Name, GroupName)
      VALUES (@Name_3, @GroupName_3);
INSERT INTO HumanResources.Department (Name, GroupName)
      VALUES (@Name_4, @GroupName_4);
INSERT INTO HumanResources.Department (Name, GroupName)
      VALUES (@Name_5, @GroupName_5);
GO

This previous example procedure has several limitations. First, it assumes that each call will contain five rows. If you have ten rows, you must call the procedure twice. If you have three rows, you need to modify the procedure to test for NULL values in the parameters and skip inserts accordingly. If NULL values are allowed in the underlying table, you would also need a method to indicate when a NULL should be stored and when a NULL represents a value not to be stored.

A more common technique is to create a singleton insert procedure, as follows:

Use AdventureWorks2014;
GO
CREATE PROCEDURE dbo.usp_INS_Department_Oldstyle_V2
@Name nvarchar(50),
@GroupName nvarchar(50)
AS
INSERT INTO HumanResources.Department (Name, GroupName)
      VALUES (@Name, @GroupName);
GO

If you have five rows to be inserted, you would call this procedure five times. This may be acceptable in many circumstances. However, if you will always be inserting multiple rows in a single batch, SQL Server provides a better alternative. Instead of performing singleton calls, you can pass the values to be inserted into a single parameter that represents a table of values. Such a parameter is called a table-valued parameter.

To use a table-valued parameter, the first step is to define a user-defined table data type, as I demonstrate here:

Use AdventureWorks2014;
GO
CREATE TYPE Department_TT AS TABLE (Name nvarchar(50), GroupName nvarchar(50));
GO

Once the new table type is created in the database, it can be referenced in module definitions and within the code:

Use AdventureWorks2014;
GO
CREATE PROCEDURE dbo.usp_INS_Department_NewStyle
      @DepartmentTable as Department_TT
READONLY
AS

INSERT INTO HumanResources.Department (Name, GroupName)
      SELECT Name, GroupName
            FROM @DepartmentTable;
GO

Let’s assume that an external process is used to populate a list of values, which I will then pass to the procedure. In your own applications, the data source that you pass in can be generated from a populated staging table, directly from an application rowset, or from a constructed rowset, as demonstrated next:

Use AdventureWorks2014;
GO
/*
-- I can declare our new type for use within a T-SQL batch
-- Insert multiple rows into this table-type variable
*/

DECLARE @StagingDepartmentTable as Department_TT
INSERT INTO @StagingDepartmentTable(Name, GroupName)
      VALUES ('Archivists', 'Accounting'),
INSERT INTO @StagingDepartmentTable(Name, GroupName)
      VALUES ('Public Media', 'Legal'),
INSERT @StagingDepartmentTable(Name, GroupName)
      VALUES ('Internal Admin', 'Office Administration'),
/*
-- Pass this table-type variable to the procedure in a single call
*/
EXECUTE dbo.usp_INS_Department_NewStyle @StagingDepartmentTable;
GO

How It Works

To pass result sets to modules, I must first define a user-defined table type within the database. I used the CREATE TYPE command and defined it AS TABLE:

CREATE TYPE Department_TT AS TABLE

Next, I defined the two columns that made up the table, just as one would for a regular table:

(Name nvarchar(50), GroupName nvarchar(50)) GO

I could have also defined the table type with PRIMARY KEY, UNIQUE, and CHECK constraints. I could also have designated nullability as well as defined whether the column was computed.

Next, I created a new procedure that used the newly created table type. In the input parameter argument list, I created an input parameter with a type of Department_TT:

CREATE PROCEDURE dbo.usp_INS_Department_NewStyle
      @DepartmentTable as Department_TT
READONLY
AS

Notice the READONLY keyword after the data type designation. This is a requirement for stored procedure and user-defined function input parameters, because you are not allowed to modify the table-valued result set in this version of SQL Server.

The next block of code handled the INSERT to the table, using the input parameter as the data source of the multiple rows:

INSERT INTO HumanResources.Department (Name, GroupName)
      SELECT Name, GroupName
            FROM @DepartmentTable;
GO

After that, I demonstrated declaring a local variable that would contain multiple rows that would be passed to the procedure. The DECLARE statement defines the variable name, followed by the name of the table user-defined type defined earlier in the recipe:

DECLARE @StagingDepartmentTable as Department_TT

Once declared, I inserted multiple rows into this table and then passed it as a parameter to the stored procedure call:

INSERT INTO @StagingDepartmentTable(Name, GroupName)
      VALUES ('Archivists', 'Accounting'),
INSERT INTO @StagingDepartmentTable(Name, GroupName)
      VALUES ('Public Media', 'Legal'),
INSERT @StagingDepartmentTable(Name, GroupName)
      VALUES ('Internal Admin', 'Office Administration'),
EXECUTE dbo.usp_INS_Department_NewStyle @StagingDepartmentTable;
GO

The benefits of this new functionality come into play when you consider procedures that handle business processes. For example, if you have a web site that handles product orders, you can now pass result sets to a single procedure that includes the general header information along with multiple rows representing the products that were ordered. This application process can be constructed as a single call versus having to issue several calls for each unique product line item ordered. For extremely busy systems, using table-valued parameters allows you to reduce the chatter between the application and the database server, resulting in increased network bandwidth and more efficient batching of transactions on the SQL Server side.

18-13. Dropping User-Defined Types

Problem

You suspect there are unused user-defined types within your database. You would like to remove these types from the database.

Solution

To remove a user-defined type (also called an alias data type) from the database, use the DROP TYPE command. As with most DROP commands, the syntax for removing a user-defined type is very straightforward:

DROP TYPE [ schema_name. ] type_name

The DROP TYPE command uses the schema and type name, as this recipe will demonstrate. First, however, any references to the user-defined type need to be removed beforehand. In this example, the AccountNBR type is changed to the base equivalent for two tables and a stored procedure:

Use AdventureWorks2014;
GO
ALTER TABLE dbo.InventoryAccount
ALTER COLUMN InventoryAccountNBR char(14);
GO
ALTER TABLE dbo.CustomerAccount
ALTER COLUMN CustomerAccountNBR char(14);
GO

ALTER PROCEDURE dbo.usp_SEL_CustomerAccount
@CustomerAccountNBR char(14)

AS

SELECT CustomerAccountID, CustomerID, CustomerAccountNBR
FROM dbo.CustomerAccount
WHERE CustomerAccountNBR = @CustomerAccountNBR;
GO

With the referencing objects now converted, it is OK to go ahead and drop the type:

Use AdventureWorks2014;
GO
DROP TYPE dbo.AccountNBR;

How It Works

To remove a type, you must first change or remove any references to the type in a database table. If you are going to change the definition of a UDT, you need to remove all references to that UDT everywhere in all database objects that use that UDT. That means changing tables, views, stored procedures, and so on before dropping the type. This can be very cumbersome if your database objects depend very heavily on them. Also, if any schema-bound stored procedures, functions, or triggers use the data type as parameters or variables, these references must be changed or removed. In this recipe, ALTER TABLE...ALTER COLUMN was used to change the data type to the system data type.

ALTER TABLE dbo.InventoryAccount
ALTER COLUMN InventoryAccountNBR char(14)

A stored procedure parameter was also modified using ALTER PROCEDURE:

ALTER PROCEDURE usp_SEL_CustomerAccount (@CustomerAccountNBR char(14))