[ Join Now (FREE) | Log In]

How to create and test an User Defined Function (SQL Server)

by Jagadish Pulakhandam on 9/23/2011 8:37:29 AM
Rated 0 from 0 votes
Brief: Demonstrates on creating and testing a "Scalar-Valued" User Defined Function (UDF) using SQL Server Database
Posted to: Microsoft SQL Server - Quick Learn ("How to"s for Developers)
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Attached video demonstrates the following:
  • Create a "Scalar Valued" User Defined Function (UDF) from the scratch using SSMS
  • Test (execute) it as part of SQL SELECT query
NOTE: "Table Valued" User Defined Function is similar to "Scalar Valued" UDF except that the "Table Valued" returns a table of data (and can be used in FROM clause of SELECT or even in JOIN).

Following is a simple "Scalar Valued" UDF demonstrated:

01.CREATE FUNCTION [dbo].[fGetDname]
02.(
03.    @Deptno int
04.)
05.RETURNS VARCHAR(20)
06.AS
07.BEGIN
08.    DECLARE @Dname VARCHAR(20)
09. 
10.    SELECT @Dname = Dname FROM Dept WHERE Deptno = @Deptno
11. 
12.    RETURN @Dname
13. 
14.END

To test the above, following statement is issued:

1.SELECT TOP 1000 [Empno]
2.      ,[Ename]
3.      ,[Sal]
4.      ,[Deptno]
5.      ,[dbo].[fGetDname](Deptno)
6.  FROM [dbo].[Emp]


Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout


Video/Screen Recording (may not have audio narration/annotations)
You need to Login or Join for FREE to download the following