For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

Click here for my training feedbacks

Followers

Follow Us



Share your Interview questions here


For software development queries contact SukeshMarla@Gmail.com

Follow by Email

Learn everything about MSBI

22 December 2010

Functions vs Stored Procedure

  • UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
  • UDFs that return tables can be treated as another rowset(which can be used in JOINs with other tables),but Procedures cant.
  • Procedure can return zero or n values whereas function can return one value which is mandatory.
  • Procedures can have input,output parameters for it whereas functions can have only input parameters.
  • Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
  • Functions can be called from procedure whereas procedures cannot be called from function.
  • Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
  • We can go for transaction management in procedure whereas we can't go in function.

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.

Example
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
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))
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
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) )
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')