The CLR Proc Container is free software that enhances the capabilities of SQL Server stored procedures by opening a door to the .NET world. Code written in any .NET language can be invoked from stored procedures, providing stored procedures all the functionality of the full .NET runtime. Named procedures can be defined in .NET, dynamic procedures can be created and run, and binary objects passed between stored procedure code and the .NET object.

Consider the following programming scenario. Your company is creating a billing system. Customers can pay in any number of ways, such as through a credit card, through PayPal, or through some other payment scheme peculiar to a local market. The details associated with each payment scheme vary. For instance, credit cards require a credit card number and might be validated through a web service run by a credit agency. The Swiss Post card would require a different number plus a pin, and is validated through a web service that operates according to different principles than credit cards. A third option might not involve immediate payment at all, choosing instead to cause a bill to be printed that is sent to a particular address. A rigid relational database model would be too inflexible to accomodate all options.

A good solution would be to use object orientation to abstract the payment details. The following diagram shows a possible solution to the problem:

interface IPaymentInfo
{
	Encrypt(RSACryptoServiceProvider rsa);
	Decrypt(RSACryptoServiceProvider rsa);

	void Pay();	
}

All that has to be done to charge a customer is to call the "Pay" method on the IPaymentInfo interface somewhere in code. This is where the CLR Proc Container comes into play.

Imagine a table defined with the following columns:

TABLE: customer
customer_id		bigint
customer_name		varchar(100)
customer_payment_dtl	varbinary(1000)

The customer_payment_dtl field will contain the serialized payment object, which contains any kind of information required by the particular payment method. To use it, simply retrieve the serialized object from the table for a particular customer and use the CLR Proc Container to charge them, as shown in the following example procedure:

CREATE PROCEDURE sp_bill_customer
	@customer_id	bigint
as
begin
	declare @customer_payment_dtl	varbinary(1000)

	begin transaction
	select 
		@customer_payment_dtl = customer_payment_dtl
	from 
		customer
	where
		customer_id = @customer_id

	DECLARE @retVal int
	-- Mark the @customer_payment_dtl parameter as OUTPUT
	-- in case the payment object in question needs to save 
	-- state between usage.
	EXEC @retVal=master.dbo.xp_exec_clr_named_proc 
		'BillingHandler',
		@customer_id,
		@customer_payment_dtl OUTPUT
	
	IF @retVal = 0
	BEGIN
		update customer
		set customer_payment_dtl = @customer_payment_dtl
		where customer_id = @customer_id
	
		commit
	END
	ELSE
	BEGIN
		rollback
	END
end

xp_exec_clr_named_proc allows named operations written in .NET to be invoked from a stored procedure. The named operation 'BillingHandler' will be responsible for invoking the "Pay" method on the IPaymentInfo method. Alternatively, IPaymentInfo classes could be defined as dynamic procedures and invoked via the xp_exec_clr_proc. See Usage for details.