Example and information for Creating Function in SQL Server

This example illustrate how you can create function in SQL Server that returns table datatype and also it says how you can use Loop, Table Variables, usage of @@RowCount, set the values. I have used typical employee database.

You always have to keep in mind that it is must to Append dbo or whoever is the user defined function's owner to the function name, for ex -SELECT dbo.Sum(a,b)

sp_helptext will show the function definition because it does in fact exist. It is however not executing as it is not called properly.

UDF is called using the two-part name of object owner and object name. The object's owner is required when using a UDF that returns a scalar datatype value.

For more info on UDFs, check this link - http://msdn.microsoft.com/en-us/magazine/cc164062.aspx

CREATE FUNCTION dbo.GetManagerReports ( @iEmployeeID int )
RETURNS @ManagerReports TABLE
   (
   EmployeeID int,
   EmployeeFirstName     nvarchar(10),
   EmployeeLastName nvarchar(20),
   Title nvarchar(30),
   TitleOfCourtesy nvarchar(25),
   Extension nvarchar(4),
   ManagerID int
   )
AS
BEGIN

DECLARE

@iRowsAdded int, -- Counts rows added to-- table with each iteration
@PREPROCESSED tinyint, -- Constant for record prior-- to processing
@PROCESSING tinyint, -- Constant for record -- being processed
@POSTPROCESSED tinyint -- Constant for records that -- have been processed

SET @PREPROCESSED = 0
SET @PROCESSING = 1
SET @POSTPROCESSED = 2

DECLARE @tblReports TABLE (
EmployeeID int,
   EmployeeFirstName     nvarchar(10),
   EmployeeLastName nvarchar(20),
Title nvarchar(30),
TitleOfCourtesy nvarchar(25),
Extension nvarchar(4),
   ManagerID int,
ProcessedState tinyint
DEFAULT 0
)

--Save number of direct reports
SET @iRowsAdded = @@ROWCOUNT

WHILE @iRowsAdded > 0
BEGIN
    UPDATE @tblReports
    SET ProcessedState = @PROCESSING
    WHERE ProcessedState = @PREPROCESSED
END
   RETURN
END

Didn't Find The Answer You Were Looking For?

EggHeadCafe has experts online right now that may know the answer to your question. We pay them a bonus for answering as many questions as they can. So, why not help them and yourself by becoming a member (free) and ask them your question right now?

Ask Question In Live Forum

If you have an OpenID and do not want to become a member of the EggHeadCafe forum, you can also sign on to Chat Chaos and post your question to our real time Silverlight chat application.

Ask Question In Chat Chaos

Article Discussion: Create Function in SQL Server
Raj Cool... posted at Tuesday, October 21, 2008 10:00 AM
Original Article