Tag Archives: USER DEFINE FUNCTION

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is 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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005

HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005:
I wrote an article for introduction of HTTP Endpoint (web service version of SQL-Server 2005) and have used simple stored procedure in it at:
Or
If you are new to HTTP Endpoint and yet to create your first HTP Endpoint than please do stop reading this article and go for any of the above link to know more about HTTP Endpoint.
You are reading this line so, now I am assuming that you are aware with basic concept of HTTP Endpoint. Let us move further:
We are going to create one function that will calculate Latitude and Longitude distance in miles. Note: I have searched mathematical formula for calculating distance from internet. I have just used that formula in my function just to demonstrate the use of parameter function in HTTP Endpoint.
CREATE FUNCTION DistanceOfLatLon(
@Latitude1 Float,
@Longitude1 Float,
@Latitude2 Float,
@Longitude2 Float
)
Returns Float
As
Begin
Return 3963.0*acos(
sin(@Latitude1/57.295779513082323)
* sin(@Latitude2/57.295779513082323)
+ cos(@Latitude1/57.295779513082323)
* cos(@Latitude2/57.295779513082323)
* cos((@Longitude2@Longitude1)/57.295779513082323)
)
End
You can run above function directly in sql server and test its usage by:
SELECT [AdventureWorks].[dbo].[DistanceOfLatLon](33.0,-84.0,33.3,-84.0)
You will get result 20.750219476997 miles
Now we will implement this function in HTTP Endpoint.
CREATE ENDPOINT FindLatLong –This will create HTTP Endpoint
AUTHORIZATION sa –this is something absolutely optional authoraization for db owner
STATE = STARTED — State could be STARTED, STOPPED and DISABLE
AS HTTP — You can create HTTP or TCP endpoint
(
PATH = ‘/SQLLatLong’, –virtual path, will be used in adding reference in web or windows app.
AUTHENTICATION = (INTEGRATED), –authentication type for endpoint
PORTS = (CLEAR), — PORT coulbe be all (CLEAR) or may be SSL
SITE = ‘localhost’ –site name, in this case “localhost” as I am running it locally
)
FOR SOAP –protocol type
(
WEBMETHOD ‘getLetLong’ — you can define more than one webmethod also to expose
(
NAME = ‘[AdventureWorks].[dbo].[DistanceOfLatLon]’, SCHEMA = STANDARD,
FORMAT = ALL_RESULTS
),
WSDL = DEFAULT, –this will generate WSDL as per request
BATCHES = DISABLED –you could enable BATCHES but it becomes security thread
)
I am assuming that you have created Endpoint in Adventureworks database and you are using “localhost” in your endpoint as a site name. You can test Endpoint by running following URL in any of the internet browser.
Now, you can implement your HTTP Endpoint in your Windows or Web application. Steps to use HTTP Endpoint in your application, has been given in my previous article. You can find that link at the top of this article.
After adding the reference in your windows application, you can write following code in your button click to print the value of calculation in your label on the same form.
private void button1_Click(object sender, EventArgs e)
{
localhost1.FindLatLong finding = new ForHTTPendPoint.localhost1.FindLatLong();
finding.Credentials = System.Net.CredentialCache.DefaultCredentials;
label1.Text = finding.getLetLong(33.0, -84.0, 33.3, -84.0).ToString();
}
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005
I will be introducing you with magical feature of Microsoft SQL-Server 2005, called CROSS APPLY and OUTER APPLY. If you want to get top 2 or 3 maximum inventory quantity of each product or all product of AdventureWorks database then it is bit difficult in SQL-Server 2000 as it doesn’t support CROSS APPLY or OUTER APPLY. You can use UDF or sub query for CROSS APPY or OUTER APPLY.
BTW, you can consider CROSS APPLY clause as INNER APPLY also as it will use outer (main) query as an input of subquery or function and will return the result set. In CROSS APPLY we will be getting full set of left side query (main query) and its corresponding value from right side query or function, if it is not available in right side query or function, it will return NULL.
First let us see use of CROSS APPLY:
We are going to create one function which will return top row from production.productinventory table of AdventureWorks database based on supplied productID and row number.
CREATE FUNCTION dbo.fn_GetMax_ProductItem(@Productid AS int, @rownum AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@rownum) *
FROM Production.ProductInventory
WHERE ProductID = @Productid
ORDER BY Quantity DESC
GO
You can run above UDF and test it with following query.
select * from dbo.fn_GetMax_ProductItem(1,2)
It will return top two row of productID 1.
Note: I strongly recommend not using “*” in query in live production environment due to performance issue, this is ok in this example.
As function is ready, we will start using that function in our CROSS APPLY example.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
CROSS APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
As soon as you run above query, you will get 8 rows. Two rows for each productID (1,2,3,531). You will not get any row for ProductID 706 as it is not available in Production.Product table. This proves that CROSS APPLY clause works like INNER APPLY.
Now let us tweak above query a bit with OUTER APPLY instead of CROSS APPLY.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
OUTER APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
This time you will get records for 706 productID as well but it will come with NULL in Quantity and LocationID.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah