{"id":3733,"date":"2013-01-05T20:56:05","date_gmt":"2013-01-05T20:56:05","guid":{"rendered":"http:\/\/craftydba.com\/?p=3733"},"modified":"2013-01-05T21:23:20","modified_gmt":"2013-01-05T21:23:20","slug":"inline-table-value-functions","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=3733","title":{"rendered":"Inline Table Value Functions"},"content":{"rendered":"

\"\"<\/a>
\nToday, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL<\/a>) or Common Language Runtime (CLR<\/a>) routine that accepts parameters, performs an action, and returns the result of that action as a value. <\/p>\n

The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the former.<\/p>\n

Like most Data Definition Language (DDL<\/a>) constructs, a user define function has three operations associated with it: CREATE FUNCTION<\/a>, ALTER FUNCTION<\/a> and DROP FUNCTION<\/a> <\/p>\n

I will be using the Adventure Works <\/a>2012 sample database supplied by Microsoft during this talk. The SQL snippet below, drops the function if it exists, creates a new stub function, and alters the function to be identical to the original.<\/p>\n

-- Use the correct database\r\nUSE [AdventureWorks2012]\r\nGO\r\n\r\n-- Delete existing inline tvf\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects\r\n    WHERE object_id = OBJECT_ID(N'[dbo].[ufnCustomersInRegion]') AND [type] in (N'IF')\r\n\t)\r\nDROP FUNCTION [dbo].[ufnCustomersInRegion]\r\nGO\r\n\r\n\r\n-- Create stub inline tvf\r\nCREATE FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50))\r\nRETURNS TABLE\r\nAS\r\nRETURN \r\n  ( SELECT 'Stub Store' as Store, 'Stub City' as City  );\r\nGO\r\n\r\n\r\n-- Alter inline tvf\r\nALTER FUNCTION [dbo].[ufnCustomersInRegion] (@Region varchar(50))\r\nRETURNS TABLE\r\nAS\r\nRETURN \r\n(\r\n    SELECT DISTINCT \r\n        s.Name AS 'Store', \r\n\ta.City AS 'City'\r\n    FROM \r\n\tSales.Store AS s\r\n        INNER JOIN Person.BusinessEntityAddress AS bea \r\n            ON bea.BusinessEntityID = s.BusinessEntityID \r\n        INNER JOIN Person.Address AS a \r\n            ON a.AddressID = bea.AddressID\r\n        INNER JOIN Person.StateProvince AS sp \r\n            ON sp.StateProvinceID = a.StateProvinceID\r\n    WHERE sp.Name = @Region\r\n);\r\nGO\r\n\r\n<\/span><\/pre>\n
Please note that the return type of the function  is a generic datatype of TABLE which is not defined.  Also, there is only a single T-SQL statement in the function.  In the Adventure Works example above, we are using a SELECT statement.  This result set defines the return value.<\/p>\n
While functions can be very complex, this simple one returns the customer store and city names for a given region.  What good is a function, if it is not used in a query?  The code snippet below returns the customer information for the Washington region.<\/p>\n
-- Example use of the function\r\nSELECT *\r\nFROM [dbo].[ufnCustomersInRegion] ('Washington')\r\nORDER BY City;\r\nGO\r\n\r\n<\/span><\/pre>\n
In short, Inline Table Value Functions take zero or more values as an input parameters and return a table as single output parameter.  They are limited by the fact that only one TSQL statement is supplied inside the RETURN statement.  <\/p>\n
Next time, I will be talking about Multiline Table Valued Functions (TVF<\/a>).<\/p>\n
Inline Table Value Function Output<\/a><\/p>\n
Inline Table Value Function Example<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the former. Like most Data Definition Language (DDL) constructs,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[698,565,31,564,12,700,15,28,29],"class_list":["post-3733","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-alter-function","tag-create-funtion","tag-database-developer","tag-drop-function","tag-free-code","tag-inline-table-value-function","tag-john-f-miner-iii","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3733"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/3733\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}