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  
    public static void GetProductsByPrice(int price)  
        SqlConnection connection = new SqlConnection("context connection=true");  

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

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


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

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