Tuesday, 19 June 2012

Function in SQL Server (How to use function in sql server 2005 / 2008 )

Types :
Inline Table-Valued  (For reducing complex logic or big logic )
Multi-statement Table-valued. (For reducing complex logic or big logic )
Scalar valued function      (mostly for beginners)

How do I create and use a 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. Below is an example that is based in the data found in the NorthWind Customers Table.
CREATE FUNCTION [dbo].[whichContinent] 
 @country nvarchar(30)
RETURNS nvarchar(30)
 declare @result nvarchar(20) 
 select @result=case @country
 when 'A' then 'AMERICA'
 when 'B' then 'BELGAON'
 when 'I' then 'INDIA'
 else 'Unknown'
 return @result

Execution :

1)  create table test1(Country nvarchar(15),cont as ([dbo].[whichContinent](country)))
2)  insert into test1(country) values ('I')
3)  select * from test1
I refer following link ...

More info..


No comments:

Post a Comment