User defined functions

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

CREATE FUNCTION whichContinent

(@Country nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @Country

when 'Argentina' then 'South America'

when 'Belgium' then 'Europe'

when 'Brazil' then 'South America'

when 'Canada' then 'North America'

when 'Denmark' then 'Europe'

when 'Finland' then 'Europe'

when 'France' then 'Europe'

else 'Unknown'

end

return @return

end

Usage:

print dbo.WhichContinent('USA')

select dbo.WhichContinent(Customers.Country), customers.*

from customers

create table test

(Country varchar(15),

Continent as (dbo.WhichContinent(Country)))

insert into test (country)

values ('USA')

select * from test

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Country Continent

--------------- ------------------------------

USA North America

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

CREATE FUNCTION CustomersByContinent

(@Continent varchar(30))

RETURNS TABLE

AS

RETURN

SELECT dbo.WhichContinent(Customers.Country) as continent,

customers.*

FROM customers

WHERE dbo.WhichContinent(Customers.Country) = @Continent

GO

SELECT * from CustomersbyContinent('North America')

SELECT * from CustomersByContinent('South America')

SELECT * from customersbyContinent('Unknown')

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )

RETURNS

@CustomersbyCountryTab table (

[CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),

[ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),

[Address] [nvarchar] (60), [City] [nvarchar] (15),

[PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),

[Phone] [nvarchar] (24), [Fax] [nvarchar] (24)

)

AS

BEGIN

INSERT INTO @CustomersByCountryTab

SELECT [CustomerID],

[CompanyName],

[ContactName],

[ContactTitle],

[Address],

[City],

[PostalCode],

[Country],

[Phone],

[Fax]

FROM [Northwind].[dbo].[Customers]

WHERE country = @Country

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM @customersbyCountryTab

IF @cnt = 0

INSERT INTO @CustomersByCountryTab (

[CustomerID],

[CompanyName],

[ContactName],

[ContactTitle],

[Address],

[City],

[PostalCode],

[Country],

[Phone],

[Fax] )

VALUES ('','No Companies Found','','','','','','','','')

RETURN

END

GO

SELECT * FROM dbo.customersbycountry('USA')

SELECT * FROM dbo.customersbycountry('CANADA')

SELECT * FROM dbo.customersbycountry('ADF')

the benefits of User-Defined Functions

1.we can use these functions in so many different places when compared to the SQL Server stored procedure.

2.The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks.

3.ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.