address.sql

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

--Parse separated string and display mailing label (USA)
DECLARE @Addr1 Address
SET @Addr1 = CONVERT(Address, 'Rattlesnake Canyon Grocery;2817 Milton Dr.;Albuquerque;NM;94610-5708;USA')
PRINT @Addr1.MailingLabel()

--Retrieve individual fields
PRINT @Addr1.Name
PRINT @Addr1.Address
PRINT @Addr1.City
PRINT @Addr1.Region
PRINT @Addr1.PostalCode
PRINT @Addr1.Country
PRINT ''

--Alter field values
SET @Addr1.Address = '2817 Milton Drive'
SET @Addr1.PostalCode = '94610'
PRINT @Addr1.MailingLabel()

--Escape single quotes and omit Region and PostalCode (Ireland)
DECLARE @Addr2 Address
SET @Addr2 = CONVERT(Address, 'O''Reilly Irish Providers, Ltd.;8 Johnstown Road;Cork;;;Ireland')
PRINT @Addr2.ToString()
PRINT ''
PRINT @Addr2.MailingLabel()
PRINT @Addr2.MailingListCSV()
PRINT @Addr2.AddressXML()
PRINT ''

--Print the hash code
PRINT 'HashCode: ' + CONVERT(varchar, @Addr2.GetHashCode())
PRINT ''

SET NOCOUNT ON

--Data from AddressesUDT table
PRINT 'Binary Serialization'
SELECT * FROM AddressesUDT
PRINT ''

PRINT 'String Representation'
SELECT CustomerID, AddressUDT.ToString() AS AddressUDT FROM AddressesUDT
PRINT ''

PRINT 'String representation with WHERE and ORDER BY clauses'
SELECT CustomerID, AddressUDT.ToString()  AS AddressUDT FROM AddressesUDT
WHERE AddressUDT.Country = 'Germany'
ORDER BY AddressUDT.PostalCode DESC
PRINT ''

PRINT 'String representation with GROUP BY and ORDER BY clauses'
SELECT AddressUDT.Country, COUNT(*) AS [Count] FROM AddressesUDT
GROUP BY AddressUDT.Country
ORDER BY AddressUDT.Country