Monday, December 5, 2011

0 Function

SQL Server has 3 types of User defined functions

  • Scalar functions
  • Inline table-valued functions
  • Multistatement table-valued functions

Scalar functions

Here is the simple example

  1. Create function fnProductCatalogByColor ( @color nvarchar(15))
  2. RETURNS table
  3. AS
  4. RETURN (
  5.     SELECT * from production.Product
  6.     WHERE production.Product.Color=@color
  7. )

 

  1. select dbo.fnSimpleMath(2,5)

image

Inline table-valued functions

Here is the simple example

  1. Create function fnProductCatalogByColor ( @color nvarchar(15))
  2. RETURNS table
  3. AS
  4. RETURN (
  5.     SELECT * from production.Product
  6.     WHERE production.Product.Color=@color
  7. )

 

  1. select * from dbo.fnProductCatalogByColor('Black')
image

Multistatement table-valued functions

Here is the simple example

  1. CREATE FUNCTION fnProductCatalogByColor2  ( @color nvarchar(15))
  2. RETURNS @Products TABLE
  3. (
  4.     ProductID int,
  5.     Name nvarchar(50),
  6.     Color nvarchar(15)
  7. )
  8. AS
  9. BEGIN
  10.     INSERT @Products
  11.         SELECT ProductID,Name,Color from production.Product
  12.         WHERE production.Product.Color=@color
  13.     RETURN
  14. END

 

  1. select * from dbo.fnProductCatalogByColor2('Black')

image

How to see the function definition

Any one of the below SQL can show the detail of the function

  1. sp_helptext fnProductCatalogByColor2
  2. SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.fnProductCatalogByColor2'))
  3. select * from sys.sql_modules where object_id= OBJECT_ID(N'dbo.fnProductCatalogByColor2')
  4. select * from sys.syscomments where id=OBJECT_ID(N'dbo.fnProductCatalogByColor2')

image

How to see the function information  

  1. sp_help fnProductCatalogByColor2
  2. select * from sys.objects where object_id= OBJECT_ID(N'dbo.fnProductCatalogByColor2')
  3. select * from sys.parameters  where object_id= OBJECT_ID(N'dbo.fnProductCatalogByColor2')

image

How to see function dependence

Find out what object is needed by dbo.fnProductCatalogByColor2 function

  1. select  object_name( referenced_id)   from sys.sql_expression_dependencies
  2. where referencing_id = OBJECT_ID(N'dbo.fnProductCatalogByColor2')
image

Find out what column/Object is needed by dbo.fnProductCatalogByColor2 function

  1. select * from sys.dm_sql_referenced_entities ('dbo.fnProductCatalogByColor2','OBJECT')

image

Find out what objects dependence

  1. select * from sys.dm_sql_referencing_entities ('production.Product ','OBJECT')

This DMV will show all the objects has dependence by the Product table, including functions, views … etc

image

Reference

Viewing User-Defined Functions http://msdn.microsoft.com/en-us/library/ms189890.aspx

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates