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)
AS
BEGIN
 declare @result nvarchar(20) 
 select @result=case @country
 when 'A' then 'AMERICA'
 when 'B' then 'BELGAON'
 when 'I' then 'INDIA'
 else 'Unknown'
 end
 
 return @result
 END
 
 

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