SQL 2005 CLR Code Samples from March 2005 ADNUG Session
These are the sample bits from todays ADNUG Intro to SQL 2005 Developer Session on T-SQL Enhancements and CLR DB Programming
SQL 2005 CLR User Defined Function
public partial class UserDefinedFunctions
{
[SqlFunction]
public static bool ValidateEmailAddress(string eMailAddress)
{
// Put your code here
return System.Text.RegularExpressions.Regex.IsMatch(eMailAddress, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*");
}
};
SQL 2005 Stored Proceedures and Permission Levels - SAFE, EXTERNAL, UNSAFE
C# Database Project - Add a Stored Procedure Item to your Project and add in the following code
public partial class StoredProcedures
{
[SqlProcedure]
public static void WriteToFile(string fileName, string message)
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName, true);
using (sw)
{
sw.WriteLine(message + DateTime.Now.ToLongDateString());
}
}
};
From SQL Management Studio
USE AdventureWorks
GO
-- Drop Proc and Assembly if they exist from previous demo
DROP PROCEDURE WriteToFile
DROP ASSEMBLY PermissionsSample
GO
-- Create an Assembly with default Safe permissions
CREATE ASSEMBLY PermissionsSample
FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\StoreProcExample\StoreProcExample\bin\Release\StoreProcExample.dll'
GO
CREATE PROCEDURE WriteToFile
@FILENAME NVARCHAR(256),
@MESSAGE NVARCHAR(4000)
AS
EXTERNAL NAME PermissionsSample.[StoredProcedures].WriteToFile
GO
-- When you fire this SP it will fail with a Security Exception
EXEC WriteToFile 'c:\writetofile\test.txt', 'This is a test'
GO
-- Drop Proc and Assembly to recreate the the assembly with External_Access permission
DROP PROCEDURE WriteToFile
DROP ASSEMBLY PermissionsSample
GO
-- Create an Assembly with External Access permissions
CREATE ASSEMBLY PermissionsSample
FROM 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\StoreProcExample\StoreProcExample\bin\Release\StoreProcExample.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE PROCEDURE WriteToFile
@FILENAME NVARCHAR(256),
@MESSAGE NVARCHAR(4000)
AS
EXTERNAL NAME PermissionsSample.[StoredProcedures].WriteToFile
GO
EXEC WriteToFile 'c:\writetofile\test.txt', 'This is a test'
GO
SQL 2005 Recursion Example
USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO
SQL 2005 Pivot and Unpivot - From SQL Books Online
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
-- Unpivot data
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO
SQL 2005 ANY Operator - From SQL Books Online
USE AdventureWorks
GO
SELECT Name, ListPrice, ProductSubcategoryID
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
ORDER BY ProductSubcategoryID
GO
SQL 2005 OPENXML - Straight from SQL 2005 Books Online
USE AdventureWorks
GO
DROP TABLE T
GO
DROP FUNCTION my_udf
GO
CREATE FUNCTION my_udf(@var xml)
RETURNS xml AS
BEGIN
RETURN @var.query('ProductDescription/Features')
END
CREATE TABLE T (Col1 xml, Col2 as dbo.my_udf(Col1) )
-- Insert a row in table T
INSERT INTO T VALUES('
<ProductDescription ProductModelID="1" >
<Features>
<Feature1>Nut</Feature1>
<Feature2>Bolt</Feature2>
</Features>
</ProductDescription>')
-- Verify the results
SELECT *
FROM T
SQL 2005 Recursion with Exception Management
USE AdventureWorks
GO
DROP FUNCTION FactorialTSQL
GO
CREATE FUNCTION FactorialTSQL (@Number FLOAT)
RETURNS FLOAT
BEGIN
DECLARE @returnValue FLOAT
IF @Number <= 1
SELECT @returnValue = 1
ELSE
SELECT @returnValue = @Number * dbo.FactorialTSQL(@Number - 1)
RETURN @returnValue
END
GO
BEGIN TRY
SELECT dbo.FactorialTSQL(33)
END TRY
BEGIN CATCH
PRINT 'Recursive call failed'
END CATCH
GO
BEGIN TRY
SELECT dbo.FactorialCLR(200)
END TRY
BEGIN CATCH
PRINT 'Recursive call failed'
END CATCH
GO
SQL 2005 Ranking Functions
USE AdventureWorks
GO
-- Row_Number Demo
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', c.FirstName, c.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
GO
-- Rank
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity desc) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY RANK
GO
-- Dense Rank
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity desc) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY DENSE_RANK
GO
-- NTile
SELECT NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', c.FirstName, c.LastName, s.SalesYTD, a.PostalCode
From Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
GO