Basics:
User defined functions fall into three categories; scalar, inline table-valued, and multi-statement table-valued.
A scalar user-defined function is used to return a single value based on zero or more parameters.
An inline table-valued UDF returns a table data type based on a single SELECT statement that is used to define the returned rows and columns. Unlike a stored procedure, an inline UDF can be referenced in the FROM clause of a query, as well as be joined to other tables. Unlike a view, an inline UDF can accept parameters.
A multi-statement table-valued UDF also returns a result set and is referenced in the FROM clause. Unlike inline table-valued UDFs, they aren’t constrained to use a single SELECT statement within the function definition and instead allow multiple Transact-SQL statements in the body of the UDF definition in order to define a single, final result set to be returned.
Creating Scalar User-Defined Functions:
-- Create a function to check for any suspicious behaviors
-- from the application
CREATE FUNCTION dbo.udf_CheckForSQLInjection
(@TSQLString varchar(max))
RETURNS BIT
AS
BEGIN
DECLARE @IsSuspect bit
-- UDF assumes string will be left padded with a single space
SET @TSQLString = ' ' + @TSQLString
IF (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR
PATINDEX('% sp_%' , @TSQLString ) <> 0 OR
PATINDEX('% DROP %' , @TSQLString ) <> 0 OR
PATINDEX('% GO %' , @TSQLString ) <> 0 OR
PATINDEX('% INSERT %' , @TSQLString ) <> 0 OR
PATINDEX('% UPDATE %' , @TSQLString ) <> 0 OR
PATINDEX('% DBCC %' , @TSQLString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @TSQLString )<> 0 OR
PATINDEX('% ALTER %' , @TSQLString )<> 0 OR
PATINDEX('% CREATE %' , @TSQLString ) <> 0OR
PATINDEX('%;%' , @TSQLString )<> 0 OR
PATINDEX('% EXECUTE %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% BEGIN %' , @TSQLString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% COMMIT %' , @TSQLString )<> 0 OR
PATINDEX('% TRANSACTION %' , @TSQLString )<> 0 OR
PATINDEX('% CURSOR %' , @TSQLString )<> 0 OR
PATINDEX('% GRANT %' , @TSQLString )<> 0 OR
PATINDEX('% DENY %' , @TSQLString )<> 0 OR
PATINDEX('% ESCAPE %' , @TSQLString )<> 0 OR
PATINDEX('% WHILE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENQUERY %' , @TSQLString )<> 0 OR
PATINDEX('% OPENROWSET %' , @TSQLString )<> 0 OR
PATINDEX('% EXEC %' , @TSQLString )<> 0)
BEGIN
SELECT @IsSuspect = 1
END
ELSE
BEGIN
SELECT @IsSuspect = 0
END
RETURN (@IsSuspect)
END
GO
Creating Inline User-Defined Functions:
CREATE FUNCTION dbo.udf_ReturnEmployeeAddress
( @EmployeeID int)
RETURNS TABLE
AS
RETURN (
SELECT AddressLine1, City, StateProvinceID, PostalCode
FROM Person.Address a
INNER JOIN HumanResources.EmployeeAddress e ON
a.AddressID = e.AddressID
WHERE e.EmployeeID = @EmployeeID )
GO
Creating Multi-Statement User-Defined Functions: Creates a UDF that returns a string array as a table result set
CREATE FUNCTION dbo.udf_ParseArray
( @StringArray varchar(max),
@Delimiter char(1) )
RETURNS @StringArrayTable TABLE (Val varchar(50))
AS
BEGIN
DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) != @Delimiter
SET @StringArray = @StringArray + @Delimiter
WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN
SELECT @Delimiter_position =
CHARINDEX(@Delimiter, @StringArray)
INSERT @StringArrayTable
VALUES (left(@StringArray, @Delimiter_position - 1))
SELECT @StringArray = STUFF(@StringArray, 1,
@Delimiter_position, '')
END
RETURN
END
GO