Create 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

Popularity  (11132 Views)

Perry posted at Tuesday, October 21, 2008 10:00 AM

eliza replied to Perry at Tuesday, October 21, 2008 6:35 PM