CLR Integration/ Call a .net user defined function from SQL server Stored Proc

how we do CLR Integration with SQL....

Here is a sample illustrating the actions…

DECLARE @MailBody varchar(max)
SET @MailBody=dbo.GetAlertForTimesheetUpdate(@Id)

--Here dbo.GetAlertForTimesheetUpdate() is an SQL server programmability
--user defined function, calling from a stored procedure…

Lets see the function like..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[GetAlertForTimesheetUpdate](@Id [int])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [TP.Automation].[TP.Automation.Facade].[GetAlertForTimesheetUpdate]

The above function will be autogenerated when we will build and deploy our .net project..

Lets see the code block in c# in our .net project..

//Add namespace
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static string GetAlertForTimesheetUpdate(int Id)
{
string Report = string.Empty;


string strHeader = @"”; // build some html
string Report= “”; // build some html
string GetReportFooter = “”; // build some html

return strHeader + Report + GetReportFooter;
}

So here it is… enjo..o..y…