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

 

Comments (1)

Skip to main content