CREATE DATABASE Demo
GO
USE Demo
GO
CREATE TABLE [dbo].[EmpRecord](
	[EmpID] [varchar](6) NOT NULL,
	[EmpName] [varchar](50) NULL,
	[ACBalance] [decimal](11, 2) NULL,
PRIMARY KEY CLUSTERED 
(
	[EmpID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[EmpSaving](
	[EmpID] [varchar](6) NOT NULL,
	[SavingBalance] [decimal](14, 2) NULL,
	[PaymentTime] [datetime] NOT NULL
) ON [PRIMARY]

GO
--Insert Some value in EmpRecord Table
INSERT INTO [EmpRecord] 
SELECT 'A00022','Jon',25000.00		UNION
SELECT 'A00003','Sam',65000.00		UNION
SELECT 'A00004','Peter',250000.00	UNION
SELECT 'B00001','Shelly',62000.00	UNION
SELECT 'B00051','Chris',72450.00	UNION
SELECT 'C00101','Mike',39000.00		UNION
SELECT 'C00329','Ronaldo',56500.00	UNION
SELECT 'D00252','David',42000.00	UNION
SELECT 'E00896','Mike',94700.00

Step 2- Now you will see a popup indicating you to add database reference. Here you can create connection with your database in your sql server.

Step 3- once you establish connection with database, you need to add a .cs (or.vb) file to write your Procedure. In Solution Explore Right Click the project and add a Stored Procedure like this-

Step 4- Named it DemoProcToCallWebService.cs and click ok.

I have already created a demo web service with name SavingAmountDetailService.This webmethod simpaly insert a row in [EmpSaving] table with value of empID and the amount that is passed to it. In Our demo application, the CLRProcedure call this web method to make an entry in [EmpSaving] table. here is the coding of this web method-

public class SavingAmountDetailService : System.Web.Services.WebService
    {

        [WebMethod]
        public string makePayment(string EmpID, Decimal amount)
        {

            string result = string.Empty;
            try
            {
                SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Demo;Integrated Security=True");
                string myCmd = "INSERT INTO [EmpSaving] VALUES ('" + EmpID + "', " + amount + ", '" + System.DateTime.Now.ToString() + "')";
                SqlCommand cmd = new SqlCommand(myCmd, con);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                result = "Payment Successful";
            }
            catch (Exception ex)
            {
                result = "Payment Failed,Exception Message :" + ex.Message;
            }
            return result;


        }
    }

public static void DemoProcToCallWebService(string EmpID,decimal amount)
    {
        //Object Creation Of Web Service Class
        SqlPipe sp = SqlContext.Pipe;
        String msz = string.Empty;
        int rowCount = 0;
        SqlConnection conn = new SqlConnection("Context Connection=true");
        conn.Open();

        SavingAmountDetailService service = new SavingAmountDetailService();
        SqlTransaction transaction = conn.BeginTransaction();
        SqlCommand cmd = new SqlCommand();
        cmd.Transaction = transaction;
        cmd.Connection = conn;

        try
        {
            cmd.CommandText = "UPDATE [EmpRecord] SET [ACBalance]=[ACBalance]-" + amount + " WHERE [EmpID]='" + EmpID + "' AND [ACBalance]>" + amount;
            rowCount += cmd.ExecuteNonQuery();
            string serviceResponse = string.Empty;
            if (rowCount > 0)
            {
                serviceResponse = service.makePayment(EmpID, amount);
            }
            if (serviceResponse.ToUpper().Contains("SUCCESSFUL"))
                transaction.Commit();
            else
                throw new Exception(serviceResponse);
            msz = "Amount deliver Successfully ";
        }
        catch (Exception ee)
        {
            transaction.Rollback();
            if (ee.Message == null)
                msz = "Amount deliver Failed due to insufficient balance or invalid empID";
            else
                msz = "Amount deliver Failed Exception: " + ee.Message;

        }
        finally
        {
            conn.Close();
            sp.Send(msz);
        }
    }

Step 7- This is the time to compile your procedure. But before compilation set the property to create a XmlSerializers.dll. In solution explore Right click the project and click properties. In build tab you will find a drop down indicating you “genrate Serialization assembly”. Chose “on” instead of auto. And save the project and build it.

Now you will find 2 dll files in bin folder with name SqlClassLibrary.dll and SqlClassLibrary.XmlSerializers.dll., you can also create XmlSerializers dll by sgen utility. Just open visual stdio command prompt and pass the command – sgen /a: “ your  SqlClassLibrary.dll path”

Step 8- after creating both assembly SqlClassLibrary.dll and SqlClassLibrary.XmlSerializers.dll,we will deploy the project. Here is the script to deploy ClrProcedure on your SQL server,( in the script , you need to change the physical path of dll files)-

sp_configure 'clr enabled', 1 
GO
RECONFIGURE; 
GO
ALTER DATABASE Demo
SET TRUSTWORTHY ON
GO

CREATE ASSEMBLY DemoCLRProcAssembly
FROM 'D:\Assembly\SqlClassLibrary.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS 
GO 

CREATE ASSEMBLY DemoCLRProcXmlSerializers
FROM 'D:\Assembly\SqlClassLibrary.XmlSerializers.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS 
GO

CREATE PROC DemoCLRProc
@EmpID [nvarchar](6),@amount Decimal(10,2)
AS
EXTERNAL NAME [DemoCLRProcAssembly].[StoredProcedures].[DemoProcToCallWebService]
GO

After running these script, you will find that two assembly are added in Assembly folder with name DemoCLRProcAssembly and DemoCLRProcXmlSerializers and a procedure with name DemoCLRProc is also created in your database.This is your CLR Procedure.

EXEC dbo.DemoCLRProc 'E00896',5000.00