How to pass parameters to CLR Stored Procedures using C#

This article is in continuation to my previous article : How to create a CLR Stored Procedure using C# and Visual Studio where I have discussed on how to write your first CLR Stored procedure and what/why/how to use CLR stored procedure, how to deploy and lots more.

Now coming back to this post. In this article I will show you how to pass an input parameter to the CLR stored procedure. For the example I am using a Product table and the input parameter is an integer value.

So, below is how the CLR stored procedure should look like

public partial class StoredProcedures  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void GetProductsByPrice(int price)  
    {  
        SqlConnection connection = new SqlConnection("context connection=true");  
        connection.Open();

        string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

        SqlCommand command = new SqlCommand(commandText, connection);  
        SqlDataReader reader = command.ExecuteReader();

        SqlContext.Pipe.Send(reader);  
    }  
};

now build and deploy your CLR stored procedure. And test your stored procedure as shown below

USE [ProductsDB]  
GO  
EXEC [dbo].[GetProductsByPrice]  
@price = 200  
GO