http://technet.microsoft.com/en-us/library/ms191165.aspx
USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = BusinessEntityID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Person
WHERE BusinessEntityID = @ContactID;
-- Get contact job title
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN (SELECT JobTitle
FROM HumanResources.Employee AS e
WHERE e.BusinessEntityID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN (SELECT ct.Name
FROM Person.ContactType AS ct
INNER JOIN Person.BusinessEntityContact AS bec
ON bec.ContactTypeID = ct.ContactTypeID
WHERE bec.PersonID = @ContactID)
ELSE NULL
END;
-- Get contact type
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM')
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN')
THEN 'Consumer'
-- Check for general contact
WHEN EXISTS(SELECT * FROM Person.Person AS p
WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC')
THEN 'General Contact'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
分享到:
相关推荐
Topological Spaces: Including a Treatment of Multi-Valued Functions, Vector Spaces and Convexity Topological Spaces: Including a Treatment of Multi-Valued Functions, Vector Spaces and Convexity ...
2.2.2 Complex-Valued Functions 7 2.3 Analytic versus Non-Analytic Functions 8 2.4 Matrix-Related Definitions 12 2.5 Useful Manipulation Formulas 20 2.5.1 Moore-Penrose Inverse 23 2.5.2 Trace Operator ...
英文版-复数矩阵的求导公式的详细的推导过程及结论<Complex-valued_matrix_derivatives>
In this paper, we review different methods to design or learn valid kernel functions for multiple outputs, paying particular attention to the connection between probabilistic and functional methods.
Chan_Active-Contours-without-Edges-for-Vector-Valued-Images_Journal-of-Visual-Communication-and-Image-Representation_2000.pdf
Abstract This chapter contains sections titled: Introduction Derivatives in Wirtinger Calculus Complex Gradient Learning Algorithms for Feedforward CVNNs Learning Algorithms for Recurrent CVNNs ...
Vector-Valued Optimization Problems in Control Theory (M. E. Salukvadze) 控制论
《Complex-Valued_Neural_Networks》 作者: Akira Hirose 英文高清原版PDF
In logic, a three-valued logic (also trivalent, ternary, trinary logic, or trilean,[citation needed] sometimes abbreviated 3VL) is any of several many-valued logic systems in which there are three ...
经典教材: 复数矩阵的求导。复矩阵的计算、复信号处理、机器学习必备。
另一个有用的四值逻辑,林作铨,贾肇聪,本文提出一个具有直观语义的新四值逻辑,其联词对理解常识知识、自然语言和哲学中的矛盾很有用,其直观语义反映了任何判断据所拥有�
- Introduce the rules for computing the derivative of complex-valued functions. -Provide applications in signal processing and communications. - A great handbook for both research and engineering.
这是关于时间序列离散化的电子书,高清,最新版本,经典著作,英文版
A Method for Interval-valued Intuitionistic Fuzzy Multiple Attribute Decision Making with Incomplete Weight Information,卫贵武,,With respect to multiple attribute decision making problems with ...
Multi-Valued Neural Network Trained by Differential Evolution for Synthesizing Multiple-Valued Functions
Using the method of maximizing deviations to multiple attribute decision making under interval-valued intuitionistic fuzzy environment,卫贵武,,With respect to multiple attribute decision making ...
复值时滞神经网络的拉格朗日稳定性,涂正文,曹进德,本文研究了复值时滞神经网络的Lagrange稳定性,借助于矩阵测度和广义的Halanay 不等式获得了能保证复值时滞神经网络拉格朗日指数稳定的�
Statistical SIgnal processing of complex-valued data: the theory
On the handling of Continuous-valued attributes in decisiontree generation