22 December 2010

User Defined Functions In Sql

A user-defined function (UDF) is a prepared code segment that can accept parameters, process some logic, and then return some data.

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function
This are the type of user-defined functions that most developers used in various programming languages,which accepts 0 or more parameters and returns a single value.
Here Return type will be any of the scalar data type Except Text,ntext,Image and timestamp.

CREATE FUNCTION whichContinent 
(@Country nvarchar(15))
RETURNS varchar(30)
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'

return @return
Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.

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
It 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))
  SELECT dbo.WhichContinent(Customers.Country) as continent,
  FROM customers
  WHERE dbo.WhichContinent(Customers.Country) = @Continent

SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')

Multi-statement Table-Value User-Defined Function
It 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) )
 @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)
 INSERT INTO @CustomersByCountryTab 
 SELECT  [CustomerID], 
 FROM [Northwind].[dbo].[Customers]
 WHERE country = @Country
 SELECT @cnt = COUNT(*) FROM @customersbyCountryTab
 IF @cnt = 0
  INSERT INTO @CustomersByCountryTab (
   [Fax]  )
  VALUES ('','No Companies Found','','','','','','','','')
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')

No comments:

Post a Comment

Your comments, Feedbacks and Suggestions are very much valuable to me :)

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...