Labels

Friday, January 9, 2009

05 - Functions (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          View

-          SP

-          Functions

-          Index

-          Triggers

 

 

User-Defined Functions

 

·          Were introduced in SQL Server 2000.

·          Types of UDFs

 

Scalar UDFs

Return one value, and cannot return table data types.

 

Inline UDFs

 

Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns

 

Multistatement UDFs

 

Allow you to generate a table result set, based on Transact-SQL operations within the UDF definition.

 

Multistatement UDFs allow assignment, flow-of-control, DECLARE and SELECT variable assignments, extended stored procedure executions, and local table variable DML statements

 

 

 

·          UDFs can be Deterministic or Non-Deterministic. A deterministic UDF always returns the same value given the same parameters. Non-deterministic UDFs (e.g. GetDate()) return a different value or values when executed with the same parameters across multiple executions.

 

·          UDFs can also use Schemabinding. Like schemabinding with views, UDF schemabinding prevents you from changing the underlying objects referenced by the UDF, thus preventing changes that could hurt your user-defined function.

 

·          Syntax – For all three types

 

 

CREATE FUNCTION <ownername.scalar_function_name>

(<@param1>, <@paramN...>)

RETURNS <function_data_type>

WITH [ENCRYPTION | SCHEMABINDING]

AS

 

BEGIN

<function_body>

<RETURN value>

END

GO

 

When SCHEMABINDING is chosen, underlying and referenced database objects cannot undergo certain changes.

 

 

CREATE FUNCTION taxRate (@State nvarchar(2)) RETURNS numeric (2,2) AS

BEGIN

DECLARE @TaxRate numeric (2,2)

SELECT @TaxRate = case @State

WHEN 'MN' Then .07

WHEN 'WI' Then .05

WHEN 'WA' Then 0

WHEN 'NY' Then .11

ELSE 0

END

RETURN @TaxRate

END

 

 

CREATE FUNCTION <owner.inline_function_name>

(<@param1,<@paramN>)

RETURNS TABLE

WITH [ENCRYPTION | SCHEMABINDING]

AS

RETURN <select statement>

GO

 

 

CREATE FUNCTION BooksByAuthor

(@AuthorId int)

RETURNS TABLE

AS

RETURN SELECT vchBookName FROM Books WHERE iAuthorId = @Authorid

GO

 

CREATE FUNCTION <owner.multistatement_function_name>

(<@param1>,<@paramN>)

RETURNS <@table_variable_name> TABLE

(<column_name_1> <data_type_for_column1>,

<column_name_N> <data_type_for_columnN>)

 

WITH [ENCRYPTION | SCHEMABINDING]

AS

BEGIN

<Transact-SQL batches>

RETURN

END

GO

 

 

CREATE FUNCTION dbo.AddRowHeader (@BookId int)

RETURNS @KeyValues TABLE (vchBookName varchar(500))

AS

BEGIN

INSERT @KeyValues SELECT vchBookName FROM Books WHERE iBookId = @BookId

INSERT @KeyValues VALUES (' Books Header ------------')

RETURN

END

GO

 

  

UDF vs SP -

 

 

Stored Procedures

User-Defined Functions

 

Accept parameters

Same

 

Can contain output parameters

No

 

Optional

-      Must always return a scalar value or a table.

-      UDFs can return all data types, with the exception of the CURSOR, TIMESTAMP, TEXT, NTEXT, and IMAGE data types

 

No

-      Can be used within your queries (Select, Where & From)  and DML statements.

-      Used as a substitute for a sub-query.

 

 

A function call can also be joined with other tables.

 

 

 

Hope this helps.

 

Thanks & Regards,

Arun Manglick || Tech Lead

No comments:

Post a Comment