top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

SQL Server: User-Defined Functions and Types

+9 votes
594 views

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
posted Jan 19, 2014 by Atul Mishra

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: Functions

Learn how to create and drop functions in SQL Server (Transact-SQL) with syntax and examples.

What is a function in SQL Server?

In SQL Server, a function is a stored program that you can pass parameters into and return a value.

Create Function

You can create your own functions in SQL Server (Transact-SQL). Let's take a closer look.

Syntax

The syntax to create a function in SQL Server (Transact-SQL) is:

CREATE FUNCTION [schema_name.]function_name
( [ @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ]
  , @parameter [ AS ] [type_schema_name.] datatype 
    [ = default ] [ READONLY ] ]
)

RETURNS return_datatype

[ WITH { ENCRYPTION
       | SCHEMABINDING
       | RETURNS NULL ON NULL INPUT
       | CALLED ON NULL INPUT
       | EXECUTE AS Clause ]

[ AS ]

BEGIN

   [declaration_section]

   executable_section

   RETURN return_value

END;

schema_name

The name of the schema that owns the function.

function_name

The name to assign to this function in SQL Server.

@parameter

One or more parameters passed into the function.

type_schema_name

The schema that owns the data type, if applicable.

datatype

The data type for @parameter.

default

The default value to assign to @parameter.

READONLY

It means that @parameter can not be overwritten by the function.

return_datatype

The datatype of the function's return value.

ENCRYPTION

It means that the source for the function will not be stored as plain text in the system views in SQL Server.

SCHEMABINDING

It means that the underlying objects can not be modified so as to affect the function.

RETURNS NULL ON NULL INPUT

It means that the function will return NULL if any parameters are NULL without having to execute the function.

CALL ON NULL INPUT

It means that the function will execute the function even if any parameters are NULL.

EXECUTE AS clause

Sets the security context to execute the function.

return_value

The value returned by the function.

Example

Let's look at an example of how to create a function in SQL Server (Transact-SQL).

The following is a simple example of a function:

CREATE FUNCTION ReturnSite
( @site_id INT )

RETURNS VARCHAR(50)

AS

BEGIN

   DECLARE @site_name VARCHAR(50);

   IF @site_id < 10
      SET @site_name = 'TechOnTheNet.com';
   ELSE
      SET @site_name = 'CheckYourMath.com';

   RETURN @site_name;

END;

This function is called ReturnSite. It has one parameter called @site_id which is an INT datatype. The function returns a VARCHAR(50) value, as specified by the RETURNS clause.

You could then reference the new function called ReturnSite as follows:

USE [test]
GO

SELECT dbo.ReturnSite(8);

GO

Drop Function

Once you have created your function in SQL Server (Transact-SQL), you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in SQL Server (Transact-SQL) is:

DROP FUNCTION function_name;

function_name

The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in SQL Server.

For example:

DROP FUNCTION ReturnSite;

This DROP FUNCTION example would drop the function called ReturnSite.

 READ MORE
...