Common Language Runtime (CLR) Integration Programming in .NET

CLR Stored Procedures

Stored procedures cannot be used in scalar expressions, unlike scalar expressions they are able to return tabular format data, can invoke data definition language (DDL) and data manipulation language (DML) statements and return Output parameters. In CLR, stored procedures are created as public static method in .NET framework assembly. This static method can be of void or integer type, if it returns an integer value, it is treated as return code of procedure as –
EXECUTE @return_status = your_procedure

The @return_status variable will contain the value returned by the method. If the method is declared void, the return code will be 0. The following code snippet shows a stored procedure returning information through an OUTPUT parameter:

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void RateSum(out SqlInt32 value)
{

using(SqlConnection con = new SqlConnection(“context connection=true”))
{

value = 0;
con.Open();
SqlCommand cmd = new SqlCommand(“SELECT Rate FROM Products”, con);
SqlDataReader reader = cmd.ExecuteReader();

using (reader)
{

while( reader.Read() )
{

value += reader.GetSqlInt32(0);

}

}

}

}

}

Read more: Crispy coding
QR:

Posted via email from Jasper-net