DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to name user-defined functions?

Use the following prefixes for functions:

    fn (general)
   ufn (general)
   tvf (table-valued function)
   itvf (inline table-valued function
   svf (scalar-valued function)

The following user-defined function example is from AdventureWorks2008 (ufnGetContactInformation renamed).

USE AdventureWorks2008;

GO

CREATE FUNCTION dbo.tvfGetContactInformation(@PersonID int)

RETURNS @retContactInformation TABLE

(

    PersonID int NOT NULL,

    FirstName nvarchar(50) NULL,

    LastName nvarchar(50) NULL,

      JobTitle nvarchar(50) NULL,

    BusinessEntityType nvarchar(50) NULL

)

AS

BEGIN

      IF @PersonID IS NOT NULL

            BEGIN

            IF EXISTS(SELECT * FROM HumanResources.Employee e

                              WHERE e.BusinessEntityID = @PersonID)

                  INSERT INTO @retContactInformation

                        SELECT @PersonID, p.FirstName, p.LastName, e.JobTitle, 'Employee'

                        FROM HumanResources.Employee AS e

                              INNER JOIN Person.Person p

                              ON p.BusinessEntityID = e.BusinessEntityID

                        WHERE e.BusinessEntityID = @PersonID;

 

            IF EXISTS(SELECT * FROM Purchasing.Vendor AS v

                              INNER JOIN Person.BusinessEntityContact bec

                              ON bec.BusinessEntityID = v.BusinessEntityID

                              WHERE bec.PersonID = @PersonID)

                  INSERT INTO @retContactInformation

                        SELECT @PersonID, p.FirstName, p.LastName, ct.Name, 'Vendor Contact'

                        FROM Purchasing.Vendor AS v

                              INNER JOIN Person.BusinessEntityContact bec

                              ON bec.BusinessEntityID = v.BusinessEntityID

                              INNER JOIN Person.ContactType ct

                              ON ct.ContactTypeID = bec.ContactTypeID

                              INNER JOIN Person.Person p

                              ON p.BusinessEntityID = bec.PersonID

                        WHERE bec.PersonID = @PersonID;

           

            IF EXISTS(SELECT * FROM Sales.Store AS s

                              INNER JOIN Person.BusinessEntityContact bec

                              ON bec.BusinessEntityID = s.BusinessEntityID

                              WHERE bec.PersonID = @PersonID)

                  INSERT INTO @retContactInformation

                        SELECT @PersonID, p.FirstName, p.LastName, ct.Name, 'Store Contact'

                        FROM Sales.Store AS s

                              INNER JOIN Person.BusinessEntityContact bec

                              ON bec.BusinessEntityID = s.BusinessEntityID

                              INNER JOIN Person.ContactType ct

                              ON ct.ContactTypeID = bec.ContactTypeID

                              INNER JOIN Person.Person p

                              ON p.BusinessEntityID = bec.PersonID

                        WHERE bec.PersonID = @PersonID;

 

            IF EXISTS(SELECT * FROM Person.Person AS p

                              INNER JOIN Sales.Customer AS c

                              ON c.PersonID = p.BusinessEntityID

                              WHERE p.BusinessEntityID = @PersonID AND c.StoreID IS NULL)

                  INSERT INTO @retContactInformation

                        SELECT @PersonID, p.FirstName, p.LastName, NULL, 'Consumer'

                        FROM Person.Person AS p

                              INNER JOIN Sales.Customer AS c

                              ON c.PersonID = p.BusinessEntityID

                              WHERE p.BusinessEntityID = @PersonID AND c.StoreID IS NULL;

            END

 

      RETURN;

END;

 

GO

 

SELECT * FROM dbo.tvfGetContactInformation (1)

GO

/*

PID   FirstName   LastName    JobTitle                BusinessEntityType

1     Ken         Sánchez     Chief Executive Officer Employee

*/

 

SELECT * FROM dbo.tvfGetContactInformation (100)

GO

/*

PID   FirstName   LastName    JobTitle                BusinessEntityType

100   Lolan       Song        Prod Technician - WC50  Employee

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE