Create Function in SQL Server
INSTANTLY dtSearch TERABYTES OF POPULAR DATA TYPES; hundreds of reviews, etc.!
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
@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 (
--Save number of direct reports
SET @iRowsAdded = @@ROWCOUNT
WHILE @iRowsAdded > 0
SET ProcessedState = @PROCESSING
WHERE ProcessedState = @PREPROCESSED
Popularity (12351 Views)