Difference between Stored Procedure and User Define Function in SQL Server 2005:

I have written many articles on differences and comparison on various topic of Microsoft SQL Server. Here is one more difference between Stored Procedure and User Define Function. Many times I have seen new developer in confusion about usage of functions so after finishing the series of articles in stored procedures and user define functions I thought to write one article for these differences.

Actually before Microsoft SQL Server 2000, there was no concept of UDFs and it has first been revealed in Microsoft SQL Server 2000 to wrap up your complex T-SQL logic but initially people were not started adopt it but I guess they have started focusing in it with Microsoft SQL Server 2005 when UDFs comes up with new features like table valued function and CLR function implementation. Now, the situation is, UDFs become one powerful tool in SQL Server kit.

The main difference I feel between SPs and UDFs is usage in SELECT. One can use UDFs anywhere in JOIN, FROM, WHERE and HAVING clause whereas SPs are not that much flexible.

UDFs are simple to invoke in any T-SQL statement then SPs.

Some DML statements like INSERT, UPDATE and DELETE are not permissible in UDFs whereas you can use it in SPs.

You can not call non-deterministic function of SQL Server inside the UDFs whereas you can call them in SPs. For example GETDATE() function, it is non-deterministic and can’t be called within UDFs.

Stored Procedure can call function but function can’t call stored procedure.

User defined function can have only input parameter whereas SPs can have input as well as output parameter.

You can use Try…Catch in SPs whereas UDF can’t support it.

If you are more interested to learn about SPs and UDFs than you can sear http://www.sqlhub.com for more details as I wrote dozens of article on this topic with so many real life examples.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Advertisements

Multi Statement Table Valued Function in SQL Server 2005:

Multi Statement Table Valued User Define Function is very useful and handy in-order to retain complex code with the table variable. This function populates table variable inside and then returns record set like stored procedure.

–creating demo table

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating multi-statment inline table valued function

CREATE FUNCTION dbo.GetEmployeeData1(@dept VARCHAR(10))

RETURNS @empData TABLE

(

Name VARCHAR(10),

Dept VARCHAR(10),

Company VARCHAR(10)

)

AS

BEGIN

INSERT @empData(Name,Dept,Company)

SELECT Name,Dept,Company FROM emps WHERE dept=@dept

RETURN

END

GO

–use above function

SELECT * FROM dbo.GetEmployeeData1(‘MIS’)

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Cross Apply in inline table valued function in SQL Server 2005

CROSS APPLY and OUTER APPLY is new in Microsoft SQL Server 2005. CROSS APPLY works like JOIN and a.k.a. INNER APPLY. You can use it like co-related query.

–create one table for demo

use adventureworks

–department table

CREaTE TABLE deptInfo

(

DeptName VARCHAR(10),

Description VARCHAR(20)

)

–INSERT records

INSERT INTO DeptInfo

SELECT ‘MIS’,‘IT DEPT’ UNION ALL

SELECT ‘account’,‘finance department’ UNION ALL

SELECT ‘chemical’,‘chemical department’

GO

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating inline table valued function

CREATE FUNCTION dbo.GetEmployeeData(@dept VARCHAR(10))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM emps WHERE dept=@dept

)

GO

–use cross apply in above function

SELECT d.Deptname,d.description,e.name,e.company FROM DeptInfo d

CROSS APPLY

dbo.getemployeedata(d.deptname) as e

WHERE d.deptname=‘MIS’

Note: You can refer another article on Cross Apply and Outer apply at:

http://www.sqlhub.com/2009/03/cross-apply-and-outer-apply-clause-in.html

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Scalar User Defined Function (UDF) in SQL Server 2005

As I explained in my previous article that Scalar User Defined Function is return only one single value so I would like to convert one of my stored procedures I have developed for BSA (Body Surface Area) count into scalar UDF. Since it is returning only one value, we can develop Scalar function for that.

BTW, you can refer my basic article on UDF and SP of BSA with following URLs.

http://www.sqlhub.com/2009/03/user-defined-function-or-udf-in-sql.html

http://www.sqlhub.com/2009/03/bsa-body-surface-area-calculation-in.html

Now, let us create scalar value function to calculate BSA in Microsoft SQL Server 2005:

–CREATING scalar function to return BSA (Body Surface Area)

–The calculation is from the formula of DuBois and DuBois:

–BSA = (W 0.425 x H 0.725) x 0.007184

–where the weight is in kilograms and the height is in centimeters.

–DuBois D, DuBois EF. A formula to estimate the approximate surface area

–if height and weight be known. Arch Intern Medicine. 1916; 17:863-71.

–Wang Y, Moss J, Thisted R. Predictors of body surface area.

–J Clin Anesth. 1992; 4(1):4-10

CREATE FUNCTION dbo.CalculateBSA(@option INT,@weight FLOAT,@height FLOAT)

RETURNS FLOAT

AS

BEGIN

DECLARE @bsa FLOAT

–if weight and height are in kg and cm accordingly

IF @option=1

BEGIN

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

–if weight and height are in lbs and inch accordigly

ELSE

BEGIN

SET @weight=(@weight/2.2046)

SET @height=@height*2.54

SET @bsa=power(@weight,0.425)*power(@height,0.725)*0.007184

END

RETURN @bsa

END

GO

–run CalculateBSA function

SELECT dbo.CalculateBSA(1,95,180)

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

User Defined Function or UDF in SQL Server 2005.

User Defined Functions was first introduced in Microsoft SQL Server 2000 and it was not became very popular in starting period but over a period people found that it is useful in implementing difficult logic and sometime answer of cursor. You can use UDF with SELECT statement in FROM clause like we used to do with VIEW in SQL Server. It is totally proprietary format so it may be difficult to use it in other DATABASE in different platform.

There are mainly three types of functions available in Microsoft SQL Server 2005 Scalar function which used to return only single value, updateable inline function and Multi-statement table function.

Microsoft has introduced two new features in Functions. CLR functions and Table valued functions.

I will come up with examples of Functions in my next few articles.

Reference: Ritesh Shah
http://www.SQLHub.Com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Propercase or TitleCase users define function in SQL Server 2005

We may need to convert some text string to proper case (first letter of each word capital). I have one UDF to share with you guys for proper case. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Examples:

To convert from ‘ritesh a shah’ to ‘Ritesh A Shah’:

SELECT dbo.PROPCASE(‘ritesh a shah’)

*************************************************************************************************/

CREATE FUNCTION [dbo].[PROPCASE](@in varchar(5000))

RETURNS varchar(8000)

AS

BEGIN

IF @in IS NULL

BEGIN

RETURN NULL

END

DECLARE @out varchar(8000)

DECLARE @i int, @len int, @found_at int

DECLARE @LCASE_a int, @LCASE_z int, @Delimiter char(3), @UCASE_A int, @UCASE_Z int

SET @i = 1

SET @len = LEN(@in)

SET @out =

SET @LCASE_a = 97

SET @LCASE_z = 122

SET @Delimiter = ‘ ,-‘

SET @UCASE_A = 65

SET @UCASE_Z = 90

WHILE @i <= @len

BEGIN

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) > 0

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

SET @i = @i + 1

END

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @LCASE_a AND @LCASE_z

BEGIN

SET @out = @out + UPPER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

WHILE CHARINDEX(SUBSTRING(@in,@i,1), @Delimiter) = 0 AND (@i <= @len)

BEGIN

IF ASCII(SUBSTRING(@in,@i,1)) BETWEEN @UCASE_A AND @UCASE_Z

BEGIN

SET @out = @out + LOWER(SUBSTRING(@in,@i,1))

END

ELSE

BEGIN

SET @out = @out + SUBSTRING(@in,@i,1)

END

SET @i = @i + 1

END

END

RETURN @out

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah

SPLIT users define function in SQL Server 2005:

We may need to split the string with some separator in SQL Server. To cater this need, I have one function which I am using since very long time and felt to share it with you. Have a look at it and enjoy the power of T-SQL programming.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

/*************************************************************************************************

Purpose: To convert a given string to proper case

Tested on: SQL Server 2005

Date Created:March-19-2007

Examples:

To return splitted value

select * from dbo.splitData(‘ritesh, a, shah’,’,’)

it will return three row as three word separated by comma will be splitted

*************************************************************************************************/

CREATE FUNCTION [dbo].[SplitData]

(

@RowData nvarchar(2000),

@SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

Id int identity(1,1),

Data nvarchar(100)

)

AS

BEGIN

Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END

Note: I don’t remember whether I have written this script, modified it or got it somewhere from internet as I am using it since long time.

Reference: Ritesh Shah