Monday, December 5, 2011

2 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

2 comments:

  1. Thanks for your great posting! I certainly enjoyed reading it, you would be a great author. study in canada consultant in jalandhar

    ReplyDelete
  2. Rolex offers fake watches a wide range of models ranging from professional replica rolex submariner watches to classic watches to suit any wrist. Explore the Rolex collection by selecting your favourite models, materials, bezels, dials and bracelets to find the watch that was made for you.

    ReplyDelete

 

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