Sql 基本语句 -- 懒人代码

代码如下：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
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 = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM [Person].[Contact]
WHERE [ContactID] = @ContactID;

SET @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e
WHERE e.[ContactID] = @ContactID)
THEN (SELECT [Title]
FROM [HumanResources].[Employee]
WHERE [ContactID] = @ContactID)

-- Check for vendor
WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc
INNER JOIN [Person].[ContactType] ct
ON vc.[ContactTypeID] = ct.[ContactTypeID]
WHERE vc.[ContactID] = @ContactID)
THEN (SELECT ct.[Name]
FROM [Purchasing].[VendorContact] vc
INNER JOIN [Person].[ContactType] ct
ON vc.[ContactTypeID] = ct.[ContactTypeID]
WHERE vc.[ContactID] = @ContactID)

-- Check for store
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
WHERE sc.[ContactID] = @ContactID)
THEN (SELECT ct.[Name]
FROM [Sales].[StoreContact] sc
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
WHERE [ContactID] = @ContactID)

ELSE NULL
END;

SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e
WHERE e.[ContactID] = @ContactID)
THEN 'Employee'

-- Check for vendor
WHEN EXISTS(SELECT * FROM [Purchasing].[VendorContact] vc
INNER JOIN [Person].[ContactType] ct
ON vc.[ContactTypeID] = ct.[ContactTypeID]
WHERE vc.[ContactID] = @ContactID)
THEN 'Vendor Contact'

-- Check for store
WHEN EXISTS(SELECT * FROM [Sales].[StoreContact] sc
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
WHERE sc.[ContactID] = @ContactID)
THEN 'Store Contact'

-- Check for individual consumer
WHEN EXISTS(SELECT * FROM [Sales].[Individual] i
WHERE i.[ContactID] = @ContactID)
THEN 'Consumer'
END;

-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;

RETURN;
END;

================================================================================================
4.update语句：
a.更新users（用户）表中name为a的，部门departname名字为b的users名字为g。
--错误：此处更新所有的users表信息，可以与下边的sql进行比较，发from后存在users u，只要where为true，即更新所有的users。
update users set name='g' from users u,depart d where u.departid=d.id and d.departname='b' and u.name='a'
--正确
update users u set name='g' from depart d where u.departid=d.id and d.departname='b' and u.name='a'
--正确
update users set name='k' where departid = (select d.id from depart d,users u where u.departid=d.id and d.departname='b' and u.name='a')