/*============================================================================
  File:     SearchForActor.sql

  Summary:  This script registers the assembly used to Serialize the dataset returned
			by the Amazon Web Service. Then makes the assembly visible for usage.
			Finally, a TVF is created using the entry point of the corresponding dll.

			The rest of the script is for testing and for using the new function!

  Date:     April 2005

  Tested on SQL Server Version: 9.00.1116.08 (IDW14 - April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE SimpleDeploy
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'AmazonWS.XmlSerializers')
	DROP ASSEMBLY [AmazonWS.XmlSerializers]
IF OBJECTPROPERTY(object_id('Actors'), 'IsUserTable') = 1
	DROP TABLE [Actors]
GO

CREATE ASSEMBLY [AmazonWS.XmlSerializers]
  AUTHORIZATION dbo
FROM 'C:\SYSolutions, Inc\ConferenceSessions\TechEd\200506 Orlando\DAT250-BridgingGap\AmazonWS\AmazonWS\bin\Debug\AmazonWS.XmlSerializers.dll'
  WITH permission_set = unsafe
GO

ALTER ASSEMBLY AmazonWS 
WITH visibility = on
go

CREATE FUNCTION dbo.fn_SearchForActor(@actor nvarchar(40))
RETURNS TABLE 
( 
	[Original Release Date] datetime,
	Title nvarchar(100),
  	[Amazon List Price] money,
	[Running Time] int,
	[Amazon Used Price] money,
    [Used Stock Quantity] int
)
AS
EXTERNAL NAME AmazonWS.Amazon.SearchForActor
go

SELECT * FROM dbo.fn_SearchForActor(N'Morgan Freeman')
go

SELECT TOP 1 * 
FROM dbo.fn_SearchForActor(N'Elisabeth Shue')
ORDER BY [Original Release Date]
GO

CREATE TABLE dbo.[Actors]
 (Actor NVarchar(100)
	CONSTRAINT ActorsPK
		PRIMARY KEY CLUSTERED)
GO

INSERT dbo.Actors
	VALUES(N'Morgan Freeman')
INSERT dbo.Actors
	VALUES(N'Tom Cruise')
INSERT dbo.Actors
	VALUES(N'Elisabeth Shue')
INSERT dbo.Actors
	VALUES(N'Jean Claude Van Damme')
INSERT dbo.Actors
	VALUES(N'Kimberly Tripp') -- not a real actor... :)
INSERT dbo.Actors
	VALUES(N'NotARealName') -- not a real name...
GO

SELECT * 
FROM dbo.Actors AS a
  CROSS APPLY dbo.fn_SearchForActor(a.Actor)
GO

SELECT * 
FROM dbo.Actors AS a
  OUTER APPLY dbo.fn_SearchForActor(a.Actor)
GO

-- The prices are correct at the time of the query and are NOT 
-- guaranteed by Amazon (or me :)~

-- OK - the real disclaimer - as per Amazon Web Services Agreement:
-----------------------------------------------------------
--- Price is accurate as of the date/time indicated.	---
--- Prices and product availability are subject to		---
--- change. Any price displayed on the Amazon website	---
--- at the time of purchase will govern the sale of		---
--- this product.										---
-----------------------------------------------------------