.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Creating and managing User Defined Functions in SQL Server 2008

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Creating and managing User Defined Functions in SQL Server 2008
 

 

What are UDF: SQL server provides list of many predefined functions that are built in to the T-SQL language. The supplied functions helps extend the capabilities of T-SQL, providing the ability to perform string manipulation, mathematical calculations data type conversion etc. but often we need something which is not provided using these functions. So we can create stored procedure to perform custom processing, but the problem is that we can't use the result of stored procedure in WHERE or SELECT list, for this type of scenario we need UDF.

Why to use User Defined Functions: The main benefit of UDF is that we are not just limited to sql provided functions. We can write our own functions to meet our specific needs or to simplify complex SQL codes.

Let's take an example:

SQL getdate() returns current system date and time. It always includes both data and time components. We want to get just date and have the time always set to midnight. One solution is to to the conversion like below;

select convert(datetime,CONVERT(date,getdate()))

But the problem is that when we want to have date with time always set to midnight, we need to do this conversion. Solution is to make UDF for this.

create function getonlydate()

returns datetime

as

begin

      return(select convert(datetime,convert(date,getdate())))

end

go

 

Now we can call this UDF in our SQL query.

select dbo.getonlydate()

Let us see how we can use this UDF in other SQL statements.

Let us create a table Order

CREATE TABLE Orders (

OrderID int IDENTITY (1, 1) NOT NULL Primary Key,

CustomerID nchar (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

EmployeeID int NULL ,

OrderDate datetime NULL default dbo.getonlydate(),

RequiredDate datetime NULL ,

ShippedDate datetime NULL

)

Let us INSERT values in this table using UDF function we created.

INSERT Orders(CustomerID,EmployeeID,RequiredDate)

values('BERGS',3,dbo.getonlydate() +7)

Let us UPDATE values in this table using UDF function we created.

UPDATE Orders set ShippedDate = dbo.getonlydate()

      where OrderID=1

SELECT OrderDate,RequiredDate,ShippedDate

FROM orders

WHERE orderdate = dbo.getonlydate()

 

Orderdate               Requireddate            Shippeddate   

--------------------------------------------------------------------

2011-05-01 00:00:00.000       2011-05-08 00:00:00.000       2011-05-01 00:00:00.000

 

Types of User Defined Functions:

1) Scalar functions

2) Inline table valued function

3) Multistatement table valued functions.

For all examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your Sql Sever 2008.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

 

A) Scalar Function:

1) They are like standard built in functions provided with SQL Server.

2) It return scalar values that can be used anywhere a constant expression can be used.

3) It typically takes one or more arguments and returns a value of a specified data types.

4) Every T-SQL function must return a result using the RETURN statement.

Example:

The following two functions are variations of a function that returns the average price for a specified type of book from the titles table:

 

CREATE FUNCTION AverageBookPrice (@booktype varchar(12) = '%')

RETURNS money

AS

BEGIN

DECLARE @Avg money

      SELECT @Avg = AVG(price)

      FROM titles

      WHERE type  like @booktype

RETURN @Avg

END

GO

 

 

CREATE FUNCTION   AverageBookPrice2 (@booktype varchar(12) ='%')

RETURNS money

AS

BEGIN

RETURN (SELECT AVG(PRICE)

             FROM TITLES

             WHERE TYPE LIKE @booktype)

 

END

 

## SQL Server doesn't allow aggregate functions in a WHERE clause unless they are contained in a subquery.

 

The AvgBookPrice() function lets you compare against the average price without having to use a subquery:

SELECT  title_id, type, price from titles

where price > dbo.AverageBookPrice('popular_comp')

 

titleid type      price   

-----------------------

PC1035 popular_comp  22.95

PS1372 psychology    21.59

 

You can return the value from a user-defined scalar function into a local variable in two ways. You can assign the result to a local variable by using the SET statement or an assignment select, or you can use the EXEC statement. The following commands are functionally equivalent:

 

declare @avg1 money,

@avg2 money,

@avg3 money

select @avg1 = dbo.AverageBookPrice('popular_comp')

set @avg2 = dbo.AverageBookPrice('popular_comp')

exec @avg3 = dbo.AverageBookPrice 'popular_comp'

 

select @avg1 as avg1, @avg2 as avg2, @avg3 as avg3

go

 

Result is below

avg1     avg2       avg3 

-----------------------------------

21.475 21.475 21.475

 

B) Table Value Function:

1) A table-valued user-defined function returns a rowset instead of a single scalar value.

2) Can be invoked in the FROM clause of a SELECT statement, just as we would a table or view.

3) A table-valued function can almost be thought of as a view that accepts parameters, so the result set is determined dynamically.

4) A table valued function specifies the keyword TABLE in its RETURNS clause.

5) They are of two types.

            1) Inline table valued function

            A) An inline table-valued function specifies only the TABLE keyword in the RETURNS clause,

            Without table definition information.

            B) The code inside the function is a single RETURN statement that invokes a SELECT            statement.

 

            Example:

      CREATE FUNCTION AveragePriceByType (@price money = 0.0)

      RETURNS table

      AS

      RETURN (SELECT type,avg(isnull(price,0)) as avg_price

               FROM titles

               GROUP BY type

               HAVING avg(isnull(price,0)) > @price )

 

           

            select * from AveragePriceByType(15.0)

      type        averageprice

      ----------------------------------------

            trad_cook        15.9633

            ------------------------------------------------------------

           

 

            2) Multi statement table valued function:

            a) Multistatement table-valued functions differ from inline functions in two major ways

                        A) The RETURNS clause specifies a table variable and its definition.

                        B) The body of the function contains multiple statements, at least one of which                                populates the table variable with data values.

            b) The scope of the table variable is limited to the function in which it is defined.

            c) Within the function in which a table variable is defined, that table variable can be treated like a regular table. You can perform any SELECT, INSERT, UPDATE, or DELETE statement            on the rows in a table variable, except for SELECT INTO.

           

The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function called AveragePricebyType3():

 

CREATE FUNCTION   AveragePricebyType3 (@price money =0.0)

RETURNS @table table(type varchar(12) null,avg_price money null)

AS

BEGIN

      INSERT @table

      SELECT type,avg(isnull(price,0)) as avg_price

      FROM titles

      GROUP BY type

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend