CLR Stored ProceduresStored 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_procedureThe @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
{EXECUTE @return_status = your_procedureThe @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)
{
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)
{
con.Open();
SqlCommand cmd = new SqlCommand(“SELECT Rate FROM Products”, con);
SqlDataReader reader = cmd.ExecuteReader();using (reader)
{
while( reader.Read() )
{
{
value += reader.GetSqlInt32(0);
}
}}
}