{"id":1809,"date":"2014-05-19T00:00:00","date_gmt":"2014-05-19T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/tsql-user-defined-functions-ten-questions-you-were-too-shy-to-ask\/"},"modified":"2021-08-16T15:01:57","modified_gmt":"2021-08-16T15:01:57","slug":"tsql-user-defined-functions-ten-questions-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/tsql-user-defined-functions-ten-questions-you-were-too-shy-to-ask\/","title":{"rendered":"TSQL User-Defined Functions: Ten Questions You Were Too Shy To Ask"},"content":{"rendered":"
\n

The Questions<\/h2>\n
    \n
  1. “I’m creating a scalar function that will be used against a column that contains multiple NULL values. Is there a way to stop the function from being executed when a NULL is passed in as a parameter value?”<\/a><\/li>\n
  2. “I created a table-valued function that I want to apply to a column in each row returned by the query. However, when I try to join the function to the target table in the query’s FROM clause, I receive an error. Is there an effective way to invoke the function for each row in the result set?”<\/a><\/li>\n
  3. “I’ve heard you can use a function to parameterize a view, but I don’t see how you can incorporate a function into a view definition in such a way to support parameters. Can you explain how that is done?”<\/a><\/li>\n
  4. “I’m creating a user-defined function and want to specify a default value for an input parameter. Is that possible?”<\/a><\/li>\n
  5. “I created a scalar function that’s used in multiple queries, some of which can return millions of rows. Ever since I incorporated the function into the queries, performance has nosedived. Are there steps we can take to fix this?”<\/a><\/li>\n
  6. “I want to call the GETDATE system function from within a user-defined function, but I’ve read you cannot do this. Is there a workaround that lets me use the GETDATE function?”<\/a><\/li>\n
  7. “I want to execute a stored procedure from within a user-defined function, but I keep running into errors in the syntax. How do I call a stored procedure from within a function?”<\/a><\/li>\n
  8. “When creating user-defined functions, I like to include the SCHEMABINDING option, but in some cases, I receive an error when creating the function, saying that I cannot schema bind the function. Any idea what’s going on?”<\/a><\/li>\n
  9. “I’ve seen user-defined functions called in different ways in code. Is there a proper way to call a function?”<\/a><\/li>\n
  10. “I’m working on a query whose T-SQL code I want to encapsulate and parameterize. The query will aggregate a subset of values based on an input parameter and return the aggregated value as a column in the query results. Should I create a stored procedure or a user-defined function?”<\/a><\/li>\n<\/ol>\n
    \n

    “I’m creating a scalar function that will be used against a column that contains multiple NULL values. Is there a way to stop the function from being executed when a NULL is passed in as a parameter value?”<\/h3>\n
    \n

    Yes. In fact, doing so is actually a very straightforward process, at least for scalar functions. You simply include the RETURNS<\/code> NULL<\/code> ON<\/code> NULL<\/code> INPUT<\/code> option in your WITH<\/code> clause, as shown in the following example (option highlighted):<\/p>\n

    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.fnGetTotalItems', N'FN') IS NOT NULL\r\n\tDROP FUNCTION dbo.fnGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.fnGetTotalItems (@OrderID INT)\r\n\tRETURNS INT\r\n\tWITH RETURNS NULL ON NULL INPUT,\r\n\t\u00a0 SCHEMABINDING AS\r\n\tBEGIN\r\n\t\u00a0 DECLARE @TotalItems INT\r\n\t\u00a0 SELECT @TotalItems = SUM(OrderQty)\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t\u00a0 RETURN @TotalItems;\r\n\tEND;\r\n\tGO\r\n\t<\/pre>\n
    By default, when you call a scalar function, the database engine executes the function body whether or not a NULL<\/code> is passed in as a parameter value. However, by including the RETURNS<\/code> NULL<\/code> ON<\/code> NULL<\/code> INPUT<\/code> option, the database engine will not execute the function body when a NULL<\/code> value is passed in. For example, the following SELECT<\/code> statement returns a NULL<\/code> value:<\/p>\n
    SELECT dbo.fnGetTotalItems(null);\r\n<\/pre>\n
    When the database engine sees the NULL<\/code> input value, it simply returns NULL<\/code> without processing the function body. If your function supports multiple input parameters, the database engine returns NULL<\/code> if NULL<\/code> is passed into any one of those parameters and does not execute the function body.<\/p>\n
    However, you cannot use the RETURNS<\/code> NULL<\/code> ON<\/code> NULL<\/code> INPUT<\/code> option for a table-valued function. Because a table-value function returns a full resultset (table), it is possible for the function to return data even if a parameter value is NULL<\/code>. As such, the option cannot be practically applied to a table-valued function.<\/p>\n<\/div>\n
    “I created a table-valued function that I want to apply to a column in each row returned by the query. However, when I try to join the function to the target table in the query’s FROM clause, I receive an error. Is there an effective way to invoke the function for each row in the result set?”<\/h3>\n
    \n
    There is as long as you’re using SQL Server 2005 or later. Starting with SQL Server 2005, you’ve been able to use the APPLY<\/code> operator to join one or more tables to a table-valued function in order to invoke that function against each row in the resultset. Prior to SQL Server 2005, you had to come up with a complex workaround to achieve this.<\/p>\n
    The best way to understand how the operator works is to look at an example. The following T-SQL creates a table-valued function that returns the total number of items sold for each sale listed in the SalesOrderDetail<\/code> table of the AdventureWorks2012<\/code> database:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.ifGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT)\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT SUM(OrderQty) AS TotalItems\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t);\r\n\t<\/pre>\n
    The function takes as an argument the SalesOrderID<\/code> value that identifies the sale. In this case, the function returns only a single row with one column, but you can just as easily create a function that returns multiple rows. The simplest way to verify that the function is working as you expect is to run a simple SELECT<\/code> statement that calls the function and passes in a SalesOrderID<\/code> value:<\/p>\n
    SELECT TotalItems FROM dbo.ifGetTotalItems(43659);\r\n<\/pre>\n
    For this example, we pass in 43659<\/code> the parameter value. The function then returns 26<\/code>. However, all we’ve so far is to demonstrate that the function works as we expect. Let’s create a more complex SELECT<\/code> statement that applies the function to each row returned from the SalesOrderHeader<\/code> table. That’s where the APPLY<\/code> operator comes in, as shown in the following example:<\/p>\n
    \tSELECT s.SalesOrderID, s.OrderDate, s.SalesPersonID, \r\n\t\u00a0 f.TotalItems\r\n\tFROM Sales.SalesOrderHeader s \r\n\t\u00a0 CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f\r\n\tORDER BY SalesOrderID;\r\n\t<\/pre>\n
    Notice that after we specify the SalesOrderHeader<\/code> table in the FROM<\/code> clause, we then include the CROSS<\/code> APPLY<\/code> keywords, following by the function, with the SalesOrderID<\/code> column passed in as the parameter value.<\/p>\n
    The APPLY<\/code> operator takes two forms: CROSS<\/code> APPLY<\/code> and OUTER<\/code> APPLY<\/code>. The CROSS<\/code> APPLY<\/code> combination returns rows from the primary table (in this case, SalesOrderHeader<\/code>) only if they produce a result set from the table-valued function. The OUTER<\/code> APPLY<\/code> combination returns all rows from the primary table. In this case, both forms of APPLY<\/code> return the same number of rows. The following table shows a partial list of the results returned by this query:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     SalesOrderID<\/b><\/p>\n<\/td>\n
    		\n
     OrderDate<\/b><\/p>\n<\/td>\n
    		\n
     SalesPersonID<\/b><\/p>\n<\/td>\n
    		\n
     TotalItems<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    43659<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    26<\/p>\n<\/td>\n<\/tr>\n
    \n
    43660<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    43661<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    38<\/p>\n<\/td>\n<\/tr>\n
    \n
    43662<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    54<\/p>\n<\/td>\n<\/tr>\n
    \n
    43663<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43664<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    280<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n<\/tr>\n
    \n
    43665<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n
    \n
    43666<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n<\/tr>\n
    \n
    43667<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    277<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43668<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    93<\/p>\n<\/td>\n<\/tr>\n
    \n
    43669<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43670<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43671<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    17<\/p>\n<\/td>\n<\/tr>\n
    \n
    43672<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n<\/tr>\n
    \n
    43673<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As you can see, the results include the TotalItems<\/code> column, which is the number of items associated with that sale. In other words, we were able to apply the table-valued function to each row returned from the SalesOrderHeader<\/code> table. If our function had returned multiple rows, the result set would have included that number of rows for each row returned by the function. For example, if the function always returned three rows for each SalesOrderID<\/code> value, our resultset would include three times the number of rows than it currently does.<\/p>\n<\/div>\n
    “I’ve heard you can use a function to parameterize a view, but I don’t see how you can incorporate a function into a view definition in such a way to support parameters. Can you explain how that is done?”<\/h3>\n
    \n
    Using a function to parameterize a view has little to do with the view definition itself. It merely means you’re creating a function that either duplicates the logic of the view or calls the view within the function. In either case, you use a parameter to qualify the function’s SELECT<\/code> statement.<\/p>\n
    For example, suppose we create the following view to retrieve data about the number of employees per job title:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.JobData', N'V') IS NOT NULL\r\n\tDROP VIEW dbo.JobData;\r\n\tGO\r\n\tCREATE VIEW dbo.JobData\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\t\u00a0 SELECT JobTitle, COUNT(*) AS TotalEmps\r\n\t\u00a0 FROM HumanResources.Employee\r\n\t\u00a0 GROUP BY JobTitle;\r\n\tGO\r\n\t<\/pre>\n
    As you can see, the view is very straightforward. The SELECT<\/code> statement groups the data by the JobTitle<\/code> column and retrieves a count for each group. You can, of course, create a view that is far more complex than this one, but what we’ve done here is enough to demonstrate how this all works.<\/p>\n
    Once you’ve created the view, you can test it by running a simple SELECT<\/code> statement, similar to the following:<\/p>\n
    SELECT * FROM dbo.JobData;\r\n<\/pre>\n
    Not surprisingly, the statement returns all rows and columns returned by the view. The following table provides a partial list of those results.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     JobTitle<\/b><\/p>\n<\/td>\n
    		\n
     TotalEmps<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Accountant<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Accounts Manager<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Accounts Payable Specialist<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Accounts Receivable Specialist<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Application Specialist<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n
    \n
    Assistant to the Chief Financial Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Benefits Specialist<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Buyer<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n<\/tr>\n
    \n
    Chief Executive Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Chief Financial Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Control Specialist<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Database Administrator<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Design Engineer<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Document Control Assistant<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Document Control Manager<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    As with any SELECT<\/code> statement that retrieves data from a view, we can further refine our SELECT<\/code> statement by including the logic necessary to return the results we need, as shown in the following example:<\/p>\n
    \tSELECT TotalEmps FROM dbo.JobData\r\n\tWHERE JobTitle = 'Buyer';\r\n\t<\/pre>\n
    In this case, we’ve merely specified a column in the SELECT<\/code> list and added a WHERE<\/code> clause that limits the results to those rows in which the JobTitle<\/code> value equals Buyer<\/code>. Now the statement returns only a value of 9<\/code> because that’s how many employees have that title.<\/p>\n
    Rather than creating a view and then qualifying the SELECT<\/code> statements that call the view, we can instead create a table-valued function that incorporates the view’s logic, but also provides the ability to qualify the results through a parameter, as shown in the following example:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetJobData', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetJobData;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetJobData (@title NVARCHAR(25))\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT COUNT(*) AS TotalEmps\r\n\t\u00a0 FROM HumanResources.Employee\r\n\t\u00a0 WHERE JobTitle = @title\r\n\t\u00a0 GROUP BY JobTitle\r\n\t);\r\n\tGO\r\n\t<\/pre>\n
    Notice that the function’s SELECT<\/code> statement is similar to that of the view’s except that we also include a WHERE<\/code> clause that compares the JobTitle<\/code> column to the @title<\/code> input parameter. When you call the function, you simply pass in the job title as an argument:<\/p>\n
    SELECT * FROM dbo.GetJobData('Buyer');\r\n<\/pre>\n
    As to be expected, the SELECT<\/code> statement returns a value of 9<\/code>. Chances are, however, you’ll want to incorporate the function in a more complex query. In the following example, we use the APPLY<\/code> operator to join the Person<\/code> and Employee<\/code> tables to the GetJobData<\/code> function:<\/p>\n
    \tSELECT\r\n\t\u00a0 p.FirstName + ' ' + p.LastName AS FullName,\r\n\t\u00a0 e.JobTitle,\r\n\t\u00a0 f.TotalEmps\r\n\tFROM\r\n\t\u00a0 HumanResources.Employee e\r\n\t\u00a0\u00a0\u00a0 INNER JOIN Person.Person p\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 ON e.BusinessEntityID = p.BusinessEntityID\r\n\t\u00a0\u00a0\u00a0 CROSS APPLY dbo.GetJobData(e.JobTitle) f;\r\n<\/pre>\n
    For each row returned from the joined Person<\/code> and Employee<\/code> tables, the GetJobData<\/code> function is applied to that row, based on the value of the JobTitle<\/code> column. The results will then include a TotalEmps<\/code> column, which will provide the total number of employees who share the same title as the person listed in that row. The following table provides a partial list of employees, their job titles, and the number of people who share that title.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     FullName<\/b><\/p>\n<\/td>\n
    		\n
     JobTitle<\/b><\/p>\n<\/td>\n
    		\n
     TotalEmps<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Ken S\u00c3\u00a1nchez<\/p>\n<\/td>\n
    		\n
    Chief Executive Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Roberto Tamburello<\/p>\n<\/td>\n
    		\n
    Engineering Manager<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Rob Walters<\/p>\n<\/td>\n
    		\n
    Senior Tool Designer<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Gail Erickson<\/p>\n<\/td>\n
    		\n
    Design Engineer<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Jossef Goldberg<\/p>\n<\/td>\n
    		\n
    Design Engineer<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ovidiu Cracium<\/p>\n<\/td>\n
    		\n
    Senior Tool Designer<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Thierry D’Hers<\/p>\n<\/td>\n
    		\n
    Tool Designer<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Janice Galvin<\/p>\n<\/td>\n
    		\n
    Tool Designer<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Michael Sullivan<\/p>\n<\/td>\n
    		\n
    Senior Design Engineer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Sharon Salavaria<\/p>\n<\/td>\n
    		\n
    Design Engineer<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Bradley<\/p>\n<\/td>\n
    		\n
    Marketing Manager<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Kevin Brown<\/p>\n<\/td>\n
    		\n
    Marketing Assistant<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    John Wood<\/p>\n<\/td>\n
    		\n
    Marketing Specialist<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mary Dempsey<\/p>\n<\/td>\n
    		\n
    Marketing Assistant<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Wanida Benshoof<\/p>\n<\/td>\n
    		\n
    Marketing Assistant<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    The point to all this is that we were able to encapsulate the logic of the view and do a lot more. One issue with this approach, however, is that we have two similar sets of code that would both need to be updated if the schema changed. If we want to avoid this scenario (and simplify our function in the process), we can call the view from within the function definition, as shown in the following example:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetJobData', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetJobData;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetJobData (@title NVARCHAR(25))\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT TotalEmps FROM dbo.JobData\r\n\t\u00a0 WHERE JobTitle = @title \r\n\t);\r\n\tGO\r\n\t<\/pre>\n
    This function achieves the results as the preceding example, but instead calls the view. That way, if the view changes in a way that does not affect the function’s SELECT<\/code> statement, the function will not need to be updated, and we’ve simplified the function’s code in the process. In that sense, we have truly parameterized the view.<\/p>\n<\/div>\n
    “I’m creating a user-defined function and want to specify a default value for an input parameter. Is that possible?”<\/h3>\n
    \n
    Yes, it is possible and fairly easy to do. When defining the parameter, include the default value, along with the equal sign, as shown the following example (highlighted):<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetPersonTypeCount', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetPersonTypeCount;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetPersonTypeCount \r\n\t\u00a0 (@type NCHAR(2) = 'IN')\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT COUNT(*) AS PersonCount\r\n\t\u00a0 FROM Person.Person\r\n\t\u00a0 WHERE PersonType = @type\r\n\t\u00a0 GROUP BY PersonType\r\n\t);\r\n\tGO\r\n\t<\/pre>\n
    In this case, we’ve merely specified that the default value for the @type<\/code> parameter is IN<\/code>. However, if we want to use the default value when calling the view, we must specify the default<\/code> keyword as the parameter value, as shown in the following example:<\/p>\n
    SELECT * FROM dbo.GetPersonTypeCount(default);\r\n<\/pre>\n
    The function will now use the IN<\/code> default value and return a value of 18484<\/code>. That said, even though we’ve defined a default value, we can still specify that value when calling the function:<\/p>\n
    SELECT * FROM dbo.GetPersonTypeCount('in');\r\n<\/pre>\n
    Or we can specify a different value, as we would if no default had been specified:<\/p>\n
    SELECT * FROM dbo.GetPersonTypeCount('em');\r\n<\/pre>\n
    This time around, our SELECT<\/code> statement returns a value of 273<\/code>.<\/p>\n<\/div>\n
    “I created a scalar function that’s used in multiple queries, some of which can return millions of rows. Ever since I incorporated the function into the queries, performance has nosedived. Are there steps we can take to fix this?”<\/h3>\n
    \n
    Yes. Get rid of the function. Scalar functions are the bane of DBAs everywhere. The database engine has a habit of executing a scalar function for each row of data returned by the query. Even if a function can return only a dozen possible values, it might still run millions of times. You would think the query optimizer would be smarter than this. It is not.<\/p>\n
    If your query returns relatively few rows, a scalar function is usually no big deal and you can enjoy the encapsulation, parameterization, and ease of implementation that the function affords. But turn those few rows into millions and you’re suddenly faced with an abundance of extra executions, which translate into unnecessary disk I\/O as well as hits on memory and processing resources. Plus, you can incur extra locking, which can further impact concurrency and performance.<\/p>\n
    To complicate matters, the estimated and actual execution plans that your queries generate are far from reliable. The true cost of the execution is often buried within the plan properties. You might even have to look to profile traces to get a more accurate picture of what the function is doing.<\/p>\n
    When possible, consider turning your scalar function into an inline table valued function. Such a function returns a table, rather than a single value (as is the case with a scalar function). However, you can create a table-valued function that returns only a single column with a single row, giving you results comparable to the scalar function. Best of all, the database engine normally executes the table-value function only once, regardless of the number of rows returned by the query, resulting in far better performance.<\/p>\n
    Assuming you can turn your scalar function into a table-value one, you would then incorporate the function into your query’s FROM<\/code> clause when retrieving the data, rather than in the SELECT<\/code> list. For example, suppose we were to start with a basic scalar function similar to the following:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.fnGetTotalItems', N'FN') IS NOT NULL\r\n\tDROP FUNCTION dbo.fnGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.fnGetTotalItems (@OrderID INT)\r\n\tRETURNS INT\r\n\tWITH SCHEMABINDING AS\r\n\tBEGIN\r\n\t\u00a0 DECLARE @TotalItems INT\r\n\t\u00a0 SELECT @TotalItems = SUM(OrderQty)\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t\u00a0 RETURN @TotalItems;\r\n\tEND;\r\n\tGO\r\n\t<\/pre>\n
    The function simply returns the total number of items associated with a sale in the SalesOrderDetail<\/code> table, based on the inputted SalesOrderID<\/code> value. You can then use a SELECT<\/code> statement to run the function, in this case, entering a SalesOrderID<\/code> value of 43659<\/code>:<\/p>\n
    SELECT dbo.fnGetTotalItems(43659);\r\n<\/pre>\n
    Based on the data in the sample database, the function returns a value of 26<\/code>. If you were to use the function in a slightly more complex query, it might look something like the following:<\/p>\n
    \tSELECT SalesOrderID, OrderDate, SalesPersonID, \r\n\t\u00a0 dbo.fnGetTotalItems(SalesOrderID) AS TotalItems\r\n\tFROM Sales.SalesOrderHeader\r\n\tORDER BY SalesOrderID;\r\n<\/pre>\n
    This time around, we’re incorporating the function into the SELECT<\/code> list of a query retrieving data from the SalesOrderHeader<\/code> table. The following table shows a partial list of results returned by that query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     SalesOrderID<\/b><\/p>\n<\/td>\n
    		\n
     OrderDate<\/b><\/p>\n<\/td>\n
    		\n
     SalesPersonID<\/b><\/p>\n<\/td>\n
    		\n
     TotalItems<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    43659<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    26<\/p>\n<\/td>\n<\/tr>\n
    \n
    43660<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    43661<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    38<\/p>\n<\/td>\n<\/tr>\n
    \n
    43662<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    54<\/p>\n<\/td>\n<\/tr>\n
    \n
    43663<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43664<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    280<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n<\/tr>\n
    \n
    43665<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n
    \n
    43666<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n<\/tr>\n
    \n
    43667<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    277<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43668<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    93<\/p>\n<\/td>\n<\/tr>\n
    \n
    43669<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43670<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43671<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    17<\/p>\n<\/td>\n<\/tr>\n
    \n
    43672<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n<\/tr>\n
    \n
    43673<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    For each row returned, a column has been added that uses the fnGetTotalItems<\/code> function to calculate the total number of items sold for each order. As a result, the database engine calls the function for each row in the resultset. If we were returning millions of rows-or even billions!-all our operations could be impacted. For that reason, it’s often worth rewriting the function as a table-valued function that returns only one value:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.ifGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT)\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT SUM(OrderQty) AS TotalItems\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t);\r\n\tGO <\/pre>\n
    The function again takes a single argument, the SalesOrderID<\/code> value, but this time returns the results as a table. That means, when we call the function, we must do so in a place in the query that accepts table expressions. For that, we can use the APPLY<\/code> operator to join the SalesOrderHeader<\/code> table to the function in the FROM<\/code> clause:<\/p>\n
    \tSELECT s.SalesOrderID, s.OrderDate, s.SalesPersonID, \r\n\t\u00a0f.TotalItems\r\n\tFROM Sales.SalesOrderHeader s \r\n\t\u00a0 CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f\r\n\tORDER BY SalesOrderID;\r\n\t<\/pre>\n
    The statement returns the same results as the previous SELECT<\/code> statement; only this time, the database engine usually calls the function only once and then applies it to each row.<\/p>\n
    You might not always be able to easily turn your scalar function into a table-value function, in which case, you’ll probably want to take another approach. You might, for example, create a stored procedure or simply create the necessary T-SQL, without encapsulating any of the logic. Whatever approach you take, you should test and compare your queries under realistic workloads and then determine which approach is best suited to your circumstances.<\/p>\n<\/div>\n
    “I want to call the GETDATE system function from within a user-defined function, but I’ve read you cannot do this. Is there a workaround that let’s me use the GETDATE function?”<\/h3>\n
    \n
    First off, despite the plethora of articles and blog posts that state you cannot use a nondeterministic function such as GETDATE<\/code> within a user-defined function, it is simply not true, at least not in SQL Server 2008 R2 and SQL Server 2012, and from what I can tell, this has been the case since SQL Server 2005. For proof, check out the TechNet article “User-Defined Functions<\/a>,” or try it out yourself. You’ll find that some nondeterministic functions, such as GETDATE<\/code>, can indeed be called from within a user-defined function.<\/p>\n
    But first a step back to explain what we mean to deterministic and nondeterministic functions. A deterministic function is one that always returns the same results when given the same specific set of input values. For example, the SQRT<\/code> system function will always return the same square root value of the inputted number if that number is always the same.<\/p>\n
    However, a nondeterministic function will not necessarily return the same results each time it runs, even if input values are the same. For example, the GETDATE<\/code>, HOST_ID<\/code> and NEWID<\/code> system functions might return different results each time they’re called; therefore, they’re considered nondeterministic functions.<\/p>\n
    For quite a few years, the conventional wisdom has been that SQL Server does not let you call a nondeterministic function from within a user-defined function. However, the following CREATE<\/code> FUNCTION<\/code> statement will run with no problem:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetJobData', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetJobData;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetJobData \r\n\t\u00a0\u00a0\u00a0 (@title NVARCHAR(25), @HireDate DATETIME)\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT COUNT(*) AS TotalEmps\r\n\t\u00a0 FROM HumanResources.Employee\r\n\t\u00a0 WHERE JobTitle = @title\r\n\t\u00a0\u00a0\u00a0 AND HireDate BETWEEN @HireDate AND \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 DATEADD(MM, -6, GETDATE())\r\n\t\u00a0 GROUP BY JobTitle\r\n\t);\r\n\tGO\r\n\t<\/pre>\n
    The function determines the number of employees with the specified job title who have been hired between the specified date and current date, less six months. The function uses the GETDATE<\/code> function to determine that six-month window.<\/p>\n
    Despite what you might have heard, SQL Server will create the function. Even when I included the HOST_ID<\/code> function within the definition (just to test things out), SQL Server created the function. However, when I tried to incorporate the NEWID<\/code>, RAND<\/code>, TEXTPTR<\/code> or NEWSEQUENTIALID<\/code> nondeterministic functions into the function definition, I received an error message.<\/p>\n
    It turns out that SQL Server supports only some<\/em> nondeterministic functions, such as GETDATE<\/code> and HOST_ID<\/code>. However, to be sure that GETDATE<\/code> works correctly in our function, we can test it by calling the function and passing in the necessary parameter values:<\/p>\n
    SELECT * FROM dbo.GetJobData('Buyer', '2004-01-01');\r\n<\/pre>\n
    In this case, the function returns only seven employees with the title of Buyer who have been hired within the specified date range. We can test the function further by using the APPLY<\/code> operator to join the Employee<\/code> and Person<\/code> tables to the function, as in the following example:<\/p>\n
    \tSELECT\r\n\t\u00a0 p.FirstName + ' ' + p.LastName AS FullName,\r\n\t\u00a0 e.JobTitle,\r\n\t\u00a0 f.TotalEmps\r\n\tFROM\r\n\t\u00a0 HumanResources.Employee e\r\n\t\u00a0\u00a0\u00a0 INNER JOIN Person.Person p\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0 ON e.BusinessEntityID = p.BusinessEntityID\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CROSS APPLY dbo.GetJobData(e.JobTitle, e.HireDate) f\r\n\tORDER BY JobTitle;\r\n\t<\/pre>\n
    For each row returned from the joined tables, the function is applied, based on the JobTitle<\/code> and HireDate<\/code> values. The following table shows a partial list of results returned by the query.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     FullName<\/b><\/p>\n<\/td>\n
    		\n
     JobTitle<\/b><\/p>\n<\/td>\n
    		\n
     TotalEmps<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    Barbara Moreland<\/p>\n<\/td>\n
    		\n
    Accountant<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mike Seamans<\/p>\n<\/td>\n
    		\n
    Accountant<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    David Liu<\/p>\n<\/td>\n
    		\n
    Accounts Manager<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Karen Berg<\/p>\n<\/td>\n
    		\n
    Application Specialist<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ramesh Meyyappan<\/p>\n<\/td>\n
    		\n
    Application Specialist<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Dan Bacon<\/p>\n<\/td>\n
    		\n
    Application Specialist<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Janaina Bueno<\/p>\n<\/td>\n
    		\n
    Application Specialist<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mindy Martin<\/p>\n<\/td>\n
    		\n
    Benefits Specialist<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Mikael Sandberg<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n<\/tr>\n
    \n
    Arvind Rao<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    8<\/p>\n<\/td>\n<\/tr>\n
    \n
    Linda Meisner<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n<\/tr>\n
    \n
    Fukiko Ogisu<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    Gordon Hee<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    5<\/p>\n<\/td>\n<\/tr>\n
    \n
    Frank Pellow<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    4<\/p>\n<\/td>\n<\/tr>\n
    \n
    Eric Kurjan<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    3<\/p>\n<\/td>\n<\/tr>\n
    \n
    Erin Hagens<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ben Miller<\/p>\n<\/td>\n
    		\n
    Buyer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Ken S\u00c3\u00a1nchez<\/p>\n<\/td>\n
    		\n
    Chief Executive Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    Laura Norman<\/p>\n<\/td>\n
    		\n
    Chief Financial Officer<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Notice how, for the Buyer job title, the number of total employees decreases with each row. This is because the date range is shrinking with each row, as the hire date is compared to the GETDATE<\/code> value (minus the six months). The point is, the GETDATE<\/code> function, a built-in nondeterministic function, works fine in our user-defined function. So don’t believe everything you read.<\/p>\n
    In cases when you want to use a nondeterministic function that is not permitted, such as NEWID<\/code>, RAND<\/code>, TEXTPTR<\/code> or NEWSEQUENTIALID<\/code>, you can get around this limitation by calling the system function within a view and then calling the view from within your user-defined function. Or instead consider creating a stored procedure that achieves what your after or just going with straight T-SQL and not encapsulating the logic.<\/p>\n<\/div>\n
    “I want to execute a stored procedure from within a user-defined function, but I keep running into errors in the syntax. How do I call a stored procedure from within a function?”<\/h3>\n
    \n
    The reason you’re receiving errors is because, technically, you cannot call a stored procedure from within a user-defined function, unless it is an extended stored procedure. But even there, time is running out. Extended stored procedures have been deprecated and will eventually disappear from the SQL Server landscape.<\/p>\n
    That said, you’ll find plenty of online articles and blog posts suggesting methods for bypassing SQL Server’s built-in limitation against calling stored procedures within a function. One suggested workaround is to use an xp_cmdshell<\/code> command to run a batch file that executes the stored procedure. Another suggestion is to use the OPENQUERY<\/code> built-in function to connect via a linked server and then call the stored procedure.<\/p>\n
    In either case, you’re essentially creating a second process to trick SQL Server into running the stored procedure. The problem with such an approach is that you can end up tricking other SQL Server components as well, and end up in a deadlock that SQL Server cannot resolve.<\/p>\n
    SQL Server functions are specifically designed to prevent them from being able to change the state of the database in any way. Because stored procedures can make such changes, you cannot run them from within a function, just like you cannot modify the schema or the stored data. Functions essentially read data and then usually, in some way, manipulate the read data. But a function should never impact the underlying schema or data. That means you cannot create a function that inadvertently corrupts your entire database. If you need a routine to run a batch that includes stored procedures, or that modifies data or metadata, you need a stored procedure.<\/p>\n<\/div>\n
    “When creating user-defined functions, I like to include the SCHEMABINDING option, but in some cases, I receive an error when creating the function, saying that I cannot schema bind the function. Any idea what’s going on?”<\/h3>\n
    \n
    My guess is that you’re referencing a view or user-defined function within your function that is itself not schema bound. You cannot schema bind a function if the referenced object isn’t bound.<\/p>\n
    Schema binding ensures that the function you’re creating is bound to the database objects that it references. When you specify the SCHEM<\/code>ABINDING<\/code> option, no one can modify the base object in a way that would affect the function definition. The function must first be modified or dropped to remove any dependencies before the underlying objects can be changed.<\/p>\n
    However, when your function definition references a view or other user-defined function, that object must also be schema bound before you can apply the SCHEMABINDIN<\/code>G<\/code> function to your new function, otherwise, you’re not really protecting the new function from underlying schema changes.<\/p>\n
    Let’s look at an example to get a sense of how this works. Suppose we create a basic table-valued function similar to the one shown in the following example:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetPersonTypeCount', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetPersonTypeCount;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetPersonTypeCount (@type NCHAR(2))\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT COUNT(*) AS PersonCount\r\n\t\u00a0 FROM Person.Person\r\n\t\u00a0 WHERE PersonType = @type\r\n\t\u00a0 GROUP BY PersonType\r\n\t);\r\n\tGO\r\n<\/pre>\n
    Notice that the function’s SELECT<\/code> statement references only the Person<\/code> table and its columns. You can include the SCHEMABINDING<\/code> option in the WITH<\/code> clause with no problem because you’re directly referencing the database objects (the table and columns). If we were to call the function, as in the following example, it would return the number of people associated with a specific type, in this case, in<\/code>.<\/p>\n
    SELECT * FROM dbo.GetPersonTypeCount('in');\r\n<\/pre>\n
    The SELECT<\/code> statement returns a value of 18<\/code>484<\/code>. We can achieve the same results be creating the following view and then qualifying our SELECT<\/code> statement when we call the view:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.PersonTypeCount', N'V') IS NOT NULL\r\n\tDROP VIEW dbo.PersonTypeCount;\r\n\tGO\r\n\tCREATE VIEW dbo.PersonTypeCount\r\n\tAS\r\n\t\u00a0 SELECT PersonType, COUNT(*) AS PersonCount\r\n\t\u00a0 FROM Person.Person\r\n\t\u00a0 GROUP BY PersonType;\r\n\tGO\r\n\t<\/pre>\n
    To return the same results as the preceding SELECT<\/code> statement, we must include a WHERE<\/code> clause to specify the person type, as shown in the following example:<\/p>\n
    SELECT PersonCount FROM dbo.PersonTypeCount\r\n\tWHERE PersonType = 'in';\r\n\t<\/pre>\n
    Suppose we now want to update our function to reference the view, rather than include its own object. This allows us to pass in a parameter without having to define a WHERE<\/code> clause each time we call the view. The function does it for us, as shown in the following definition:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetPersonTypeCount', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetPersonTypeCount;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetPersonTypeCount (@type NCHAR(2))\r\n\tRETURNS TABLE\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT PersonCount FROM dbo.PersonTypeCount\r\n\t\u00a0 WHERE PersonType = @type \r\n\t);\r\n\tGO\r\n\t\r\n\t<\/pre>\n
    Notice in this function definition we do not include the WITH<\/code> SCHEMABINDING<\/code> clause, as we did the first time we created the function. If we include it, we’ll receive an error message saying that we cannot schema bind the function. We would either have to remove the clause or re-create the view to include the WITH<\/code> SCHEMABINDING<\/code> clause, as is the following example:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.PersonTypeCount', N'V') IS NOT NULL\r\n\tDROP VIEW dbo.PersonTypeCount;\r\n\tGO\r\n\tCREATE VIEW dbo.PersonTypeCount\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\t\u00a0 SELECT PersonType, COUNT(*) AS PersonCount\r\n\t\u00a0 FROM Person.Person\r\n\t\u00a0 GROUP BY PersonType;\r\n\tGO\r\n\t<\/pre>\n
    After we re-create the view, we can then run our function definition again, but this time with the WITH<\/code> SCHEMABINDING<\/code> clause:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.GetPersonTypeCount', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.GetPersonTypeCount;\r\n\tGO\r\n\tCREATE FUNCTION dbo.GetPersonTypeCount (@type NCHAR(2))\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT PersonCount FROM dbo.PersonTypeCount\r\n\t\u00a0 WHERE PersonType = @type \r\n\t);\r\n\tGO\r\n<\/pre>\n
    Because we made the view schema bound, we can now do the same with the function. As a result, no schema changes can be made that will affect either the view or the function.<\/p>\n<\/div>\n
    “I’ve seen user-defined functions called in different ways in code. Is there a proper way to call a function?”<\/h3>\n
    \n
    The ways in which you can call a user-defined function depends on whether it is a scalar function or a table-valued function. For example, suppose we have the following scalar function:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.fnGetTotalItems', N'FN') IS NOT NULL\r\n\tDROP FUNCTION dbo.fnGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.fnGetTotalItems (@OrderID INT)\r\n\tRETURNS INT\r\n\tWITH SCHEMABINDING AS\r\n\tBEGIN\r\n\t\u00a0 DECLARE @TotalItems INT\r\n\t\u00a0 SELECT @TotalItems = SUM(OrderQty)\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t\u00a0 RETURN @TotalItems;\r\n\tEND;\r\n\tGO\r\n\t<\/pre>\n
    The function takes an input parameter based on the SalesOrderID<\/code> column in the SalesOrderDetail<\/code> table and returns a total number of items associated with that sale. You can call the function within a column expression in your query’s SELECT<\/code>, WHERE<\/code>, or HAVING<\/code> clause. For example, that query can be something as simple as the following:<\/p>\n
    SELECT dbo.fnGetTotalItems(43659);\r\n<\/pre>\n
    In this case, the query returns a value of 26<\/code>, which means order 43659 has 26 items associated with it. The column expression in this case is simply the function and its parameter value. However, we can create a query slightly more complex:<\/p>\n
    \tSELECT SalesOrderID, OrderDate, SalesPersonID, \r\n\t\u00a0 dbo.fnGetTotalItems(SalesOrderID) AS TotalItems\r\n\tFROM Sales.SalesOrderHeader\r\n\tORDER BY SalesOrderID;\r\n\t<\/pre>\n
    Once again, the function is included as part of a column expression in the SELECT<\/code> list. Only this time, we’ve assigned an alias to the column and specified the SalesOrderID<\/code> column as the parameter’s value. The following table provides a partial list of the values returned by this statement.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    \n
     SalesOrderID<\/b><\/p>\n<\/td>\n
    		\n
     OrderDate<\/b><\/p>\n<\/td>\n
    		\n
     SalesPersonID<\/b><\/p>\n<\/td>\n
    		\n
     TotalItems<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    43659<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    26<\/p>\n<\/td>\n<\/tr>\n
    \n
    43660<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    279<\/p>\n<\/td>\n
    		\n
    2<\/p>\n<\/td>\n<\/tr>\n
    \n
    43661<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    38<\/p>\n<\/td>\n<\/tr>\n
    \n
    43662<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    54<\/p>\n<\/td>\n<\/tr>\n
    \n
    43663<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43664<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    280<\/p>\n<\/td>\n
    		\n
    14<\/p>\n<\/td>\n<\/tr>\n
    \n
    43665<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n
    \n
    43666<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    276<\/p>\n<\/td>\n
    		\n
    7<\/p>\n<\/td>\n<\/tr>\n
    \n
    43667<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    277<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43668<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    93<\/p>\n<\/td>\n<\/tr>\n
    \n
    43669<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    1<\/p>\n<\/td>\n<\/tr>\n
    \n
    43670<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    6<\/p>\n<\/td>\n<\/tr>\n
    \n
    43671<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    283<\/p>\n<\/td>\n
    		\n
    17<\/p>\n<\/td>\n<\/tr>\n
    \n
    43672<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    282<\/p>\n<\/td>\n
    		\n
    9<\/p>\n<\/td>\n<\/tr>\n
    \n
    43673<\/p>\n<\/td>\n
    		\n
    2005-07-01 00:00:00.000<\/p>\n<\/td>\n
    		\n
    275<\/p>\n<\/td>\n
    		\n
    20<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Despite the differences between the preceding two SELECT<\/code> statements, they’re essentially doing the same thing, in terms of calling the function as part of a column expression. However, SQL Server also lets you call a scalar function within an EXECUTE<\/code> statement, as shown in the following example:<\/p>\n
    \tDECLARE @OrderQty INT;\r\n\tSET @OrderQty = NULL;\r\n\tEXEC @OrderQty = dbo.fnGetTotalItems @OrderID = 43659;\r\n\tSELECT @OrderQty;\r\n<\/pre>\n
    Like our first SELECT<\/code> statement, the T-SQL here returns a value of 26<\/code>; however, the way we get there is a bit more complicated in this example. First we declare the @<\/code>OrderQty<\/code> variable, set its value to NULL<\/code>, and then use the EXECUTE<\/code> statement to call the function and assign the returned value to the variable. Finally, we use a SELECT<\/code> statement to retrieve the variable value.<\/p>\n
    Not surprisingly, the ways in which we can call a table-valued function are different because the function returns a full resultset (table). That means we must treat the function similar to how we would treat a table or view in our queries. Let’s look at an example to see how this works. The following T-SQL creates a table-valued function that contains logic similar to our previous example:<\/p>\n
    \tUSE AdventureWorks2012;\r\n\tGO\r\n\tIF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL\r\n\tDROP FUNCTION dbo.ifGetTotalItems;\r\n\tGO\r\n\tCREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT)\r\n\tRETURNS TABLE\r\n\tWITH SCHEMABINDING\r\n\tAS\r\n\tRETURN\r\n\t(\r\n\t\u00a0 SELECT SUM(OrderQty) AS TotalItems\r\n\t\u00a0 FROM Sales.SalesOrderDetail\r\n\t\u00a0 WHERE SalesOrderID = @OrderID\r\n\t\u00a0 GROUP BY SalesOrderID\r\n\t);\r\n\tGO\r\n\t<\/pre>\n
    When calling the function, we must remember that we’re dealing with a table-like resultset. The simplest way to do this is to include the function in our query’s FROM<\/code> clause, where a table expression is expected:<\/p>\n
    SELECT TotalItems FROM dbo.ifGetTotalItems(43659);\r\n<\/pre>\n
    Once again, our SELECT<\/code> statement returns a value of 26<\/code>. However, if the function had returned more rows and more columns, those would be included as well. In this case, the SELECT<\/code> statement returns whatever results the function generates.<\/p>\n
    When working with table-valued functions, you can also use the APPLY<\/code> operator to join a table to the function, as shown in the following example:<\/p>\n
    \tSELECT s.SalesOrderID, s.OrderDate, s.SalesPersonID, \r\n\t\u00a0f.TotalItems\r\n\tFROM Sales.SalesOrderHeader s \r\n\t\u00a0 CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f\r\n\tORDER BY SalesOrderID;\r\n<\/pre>\n
    This time around, we’re calling the function in the FROM<\/code> clause (by joining it to the table) and including the function’s returned value (TotalItems<\/code>) in the SELECT<\/code> list. The SELECT<\/code> statement will then return the same results shown with the preceding scalar function example. Be aware, however; you cannot use an EXECUTE<\/code> statement to call a table-valued function. That is reserved for scalar functions only.<\/p>\n<\/div>\n
    “I’m working on a query whose T-SQL code I want to encapsulate and parameterize. The query will aggregate a subset of values based on an input parameter and return the aggregated value as a column in the query results. Should I create a stored procedure or a user-defined function?”<\/h3>\n
    \n
    Based on what you’re describing, a user-defined function is likely the way to go. SQL Server lets you call a function from within a SELECT<\/code> statement, which you cannot do with a stored procedure. However, both provide a mechanism for encapsulating your T-SQL and passing in parameters. With stored procedures, though, you can define both input and output parameters. Functions are limited to input parameters. In addition, a function must return a single result, either a scalar value or a table. Stored procedures can return a single result, multiple results, or no results.<\/p>\n
    Another difference is that you can include all sorts of T-SQL statements within a stored procedure, which means you can retrieve data, modify data, create tables, delete tables, or take a variety of other actions. In a user-defined function, you’re basically limited to retrieving data, along with creating and managing local variables and calling extended stored procedures (which have been deprecated and will one day disappear altogether). Basically, you cannot take any action in a function that would modify the database state.<\/p>\n
    Also worth nothing is that you can call a function from within a stored procedure, but not the other way around (at least not without some clunky and often risky workarounds). Stored Procedures also let you incorporate TRY...CATCH<\/code> error handling into your code. Functions do not.<\/p>\n
    It’s not all bad news for functions, however. One of their biggest advantages, as noted above, is the ability to call them from within your SELECT<\/code> statements. Not only can you use functions in the SELECT<\/code> list, but also within your WHERE<\/code> and HAVING<\/code> clauses. In addition, you can join a table-valued function in your FROM<\/code> clause to one or more tables, usually with the help of the APPLY<\/code> operator. Plus, you can use functions in your computed column and CHECK<\/code> constraint definitions. Try doing that with a stored procedure.<\/p>\n
    Further Reading<\/h2>\n
      \n
    1. SQL Server Functions: The Basics<\/a><\/li>\n
    2. SQL String User Function Workbench: part 1<\/a><\/li>\n
    3. SQL String User Function Workbench: part 2 <\/a><\/li>\n<\/ol>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      SQL Server User-Defined Functions are good to use in most circumstances, but there just a few questions that rarely get asked on the forums. It is a shame, because the answers to them tend to clear up some ingrained misconceptions about functions that can lead to problems, particularly with locking and performanc…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4150,4151,5347,5771,4190],"coauthors":[6779],"class_list":["post-1809","post","type-post","status-publish","format-standard","hentry","category-learn","tag-sql","tag-sql-server","tag-sql-training","tag-too-shy","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1809"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1809\/revisions"}],"predecessor-version":[{"id":90057,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1809\/revisions\/90057"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1809"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}