Implementing Managed Code in the Database

Importing and Configuring Assemblies



-- Enable CLR integration
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Register assembly
USE AdventureWorks

CREATE ASSEMBLY Utilities
FROM 'D:\Democode\Utilities.dll'
WITH PERMISSION_SET = Safe
GO

-- Drop assembly
DROP ASSEMBLY Utilities
GO

Creating Managed Database Objects

-- Create the managed user-defined function
Use AdventureWorks
GO
CREATE FUNCTION dbo.GetOSVersion()
RETURNS NVARCHAR(50)
AS EXTERNAL NAME Utilities.UserDefinedFunctions.GetOSVersion
GO

-- Test the managed user-defined function
SELECT dbo.GetOSVersion()
GO

-- Create managed user-defined type
CREATE TYPE Point
EXTERNAL NAME Utilities.Point
GO

-- Test the managed user-defined type
DECLARE @P Point
SET @P = '1,5'
SELECT @P.X AS X, @P.Y AS Y
GO

-- Create table using managed user-defined type
CREATE TABLE dbo.Points
(ID int IDENTITY(1,1) PRIMARY KEY, PointValue Point)
GO

INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '3,4'));
INSERT INTO dbo.Points (PointValue) VALUES (CONVERT(Point, '1,5'));
INSERT INTO dbo.Points (PointValue) VALUES (CAST ('1,99' AS Point));

SELECT ID,
PointValue.X AS X,
PointValue.Y AS Y,
PointValue.ToString() AS String
FROM dbo.Points
GO

-- Clean up
DROP TABLE dbo.Points
DROP FUNCTION dbo.GetOSVersion
DROP TYPE Point
DROP ASSEMBLY Utilities
GO

Functions in SQL Server 2005

--Scalar Function

CREATE FUNCTION Sales.SumSold(@ProductID int) RETURNS int
AS
BEGIN
DECLARE @ret int
SELECT @ret = SUM(OrderQty)
FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END
---
SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold
FROM Production.Product
-----------------------------
-- Inline Table-Valued Function

CREATE FUNCTION HumanResources.EmployeesForManager
(@ManagerId int)
RETURNS TABLE
AS
RETURN (
SELECT FirstName, LastName
FROM HumanResources.Employee Employee INNER JOIN
Person.Contact Contact
ON Employee.ContactID = Contact.ContactID
WHERE ManagerID = @ManagerId )
---
SELECT * FROM HumanResources.EmployeesForManager(3)
-- OR
SELECT * FROM HumanResources.EmployeesForManager(6)
-----------------------------
-- Multi-Statement Table-Valued Function

CREATE FUNCTION HumanResources.EmployeeNames
(@format nvarchar(9))
RETURNS @tbl_Employees TABLE
(EmployeeID int PRIMARY KEY, [Employee Name] nvarchar(100))
AS
BEGIN
IF (@format = 'SHORTNAME')
INSERT @tbl_Employees
SELECT EmployeeID, LastName FROM HumanResources.vEmployee
ELSE IF (@format = 'LONGNAME')
INSERT @tbl_Employees
SELECT EmployeeID, (FirstName + ' ' + LastName)
FROM HumanResources.vEmployee
RETURN
END
----
SELECT * FROM HumanResources.EmployeeNames('LONGNAME')

Using XML in SQL server 2005

use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw
----------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR xml raw,elements
-------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML RAW('Order'), ROOT('Orders')
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XMl auto, elements
---------------------
use Northwind

SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity FROM dbo.Orders INNER JOIN dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID FOR XML AUTO, ELEMENTS, ROOT('Orders')
-----------------------
use Northwind

use AdventureWorks SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH
-----------------------
use AdventureWorks

SELECT EmployeeID "@EmpID", FirstName "EmpName/First", LastName "EmpName/Last" FROM Person.Contact INNER JOIN HumanResources.Employee ON Person.Contact.ContactID = Employee.ContactID FOR XML PATH('Employee')
-----------------------