User Defined Functions

a Scalar User-Defined Function
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

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

an Inline Table-Value User-Defined Function
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')

a Multi-statement Table-Value User-Defined Function
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')