createobjects.sql

< SQL Server2005程序设计>

-- Update assembly (dll) path below according to your install directory
-- Assembly
CREATE ASSEMBLY Chapter03
AUTHORIZATION dbo
FROM 'C:\Documents and Settings\VM_User\My Documents\Microsoft Press\Programming SQL Server 2005\Chapter03\VS\Chapter03Manual\Chapter03\bin\Debug\Chapter03.dll'
WITH PERMISSION_SET = SAFE
GO

-- Stored Procedures
CREATE PROCEDURE spContacts
AS EXTERNAL NAME Chapter03.StoredProcedures.spContacts
GO
CREATE PROCEDURE spContactCount
AS EXTERNAL NAME Chapter03.StoredProcedures.spContactCount
GO

-- Function
CREATE FUNCTION fnHelloWorld()
RETURNS NVARCHAR(4000) WITH EXECUTE AS CALLER
AS EXTERNAL NAME Chapter03.UserDefinedFunctions.fnHelloWorld
GO
CREATE FUNCTION [dbo].[fnToCelsius](@Fahrenheit [smallint])
RETURNS decimal WITH EXECUTE AS CALLER
AS
EXTERNAL NAME Chapter03.UserDefinedFunctions.fnToCelsius
GO
CREATE FUNCTION fnPortfolioTable(@TickersPacked [NVARCHAR](4000))
RETURNS TABLE (
	TickerSymbol NVARCHAR(5),
	VALUE DECIMAL
)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME Chapter03.UserDefinedFunctions.fnPortfolioTable
GO

-- Triggers
CREATE TRIGGER trgUpdateContact
ON Person.Contact 
FOR UPDATE
AS EXTERNAL NAME Chapter03.Triggers.trgUpdateContact
GO
CREATE TRIGGER trgCreateTable
ON DATABASE 
FOR CREATE_TABLE
AS EXTERNAL NAME Chapter03.Triggers.trgCreateTable
GO

-- User Defined Types
CREATE TYPE typPoint
EXTERNAL NAME Chapter03.typPoint
GO
CREATE TYPE typBakersDozen
EXTERNAL NAME Chapter03.typBakersDozen
GO

-- Aggregate
CREATE AGGREGATE BakersDozen
        (@input int)
RETURNS int
EXTERNAL NAME Chapter03.BakersDozen
GO