C# – How are parameters passed from SQL Server to a CLR based stored procedure

Using this example:

http://www.sommarskog.se/dynsearch-2008/search_orders_cs.cs

….I built and successfully deployed a CLR based stored procedure to SQL Server. When viewing the resulting stored procedure definition within SSMS (see code below), I notice that the parameters as defined in the C# definition are indeed part of the SP definition, but when you look at the actual call to the stored procedure, there is no reference to the parameters, so I'm curious how the parameter values are actually passed through?

(The reason I ask specifically is, in the c# code, I'm wondering if I could set the Query variable to any existing stored procedure I want, and then in one big varchar argument pass in all the relevant arguments as delimited KeyValue pairs, and then split those and add them as Command.Parameters within a loop. Basically, I'm trying to build a generic stored procedure that can execute any other stored procedure, without any restrictions on varying count or data types of parameters, all of that would be read at runtime. So basically, the same flexibility you have when calling from C# into SQL Server, except implemented in SQL Server.)

Resulting Stored Proc (viewed within SSMS):

CREATE PROCEDURE [dbo].[search_orders_cs]
    @Orderid [int],
    @Fromdate [datetime],
    @Todate [datetime],
    @Minprice [money],
    @Maxprice [money],
    @Custid [nvarchar](4000),
    @Custname [nvarchar](4000),
    @City [nvarchar](4000),
    @Region [nvarchar](4000),
    @Country [nvarchar](4000),
    @Prodid [int],
    @Prodname [nvarchar](4000),
    @Debug [bit]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlServerProject1].[StoredProcedures].[search_orders_cs]
GO


EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'twg_clr_based_sp.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'search_orders_cs'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'23' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'search_orders_cs'
GO

C# Source:

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void search_orders_cs(
           SqlInt32 Orderid,
           SqlDateTime Fromdate,
           SqlDateTime Todate,
           SqlMoney Minprice,
           SqlMoney Maxprice,
           SqlString Custid,
           SqlString Custname,
           SqlString City,
           SqlString Region,
           SqlString Country,
           SqlInt32 Prodid,
           SqlString Prodname,
           SqlBoolean Debug)
    {
        string Query;
        SqlCommand Command = new SqlCommand();

        Query = @"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
                     c.CustomerID, c.CompanyName, c.Address, c.City,
                     c.Region, c.PostalCode, c.Country, c.Phone,
                     p.ProductID, p.ProductName, p.UnitsInStock,
                     p.UnitsOnOrder
              FROM   dbo.Orders o
              JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
              JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
              JOIN   dbo.Products p ON p.ProductID = od.ProductID
              WHERE  1 = 1 ";

        if (!Orderid.IsNull)
        {
            Query += " AND o.OrderID = @orderid " +
                     " AND od.OrderID = @orderid";
            Command.Parameters.Add("@orderid", SqlDbType.Int);
            Command.Parameters["@orderid"].Value = Orderid;
            Command.Parameters["@orderid"].Direction = ParameterDirection.Input;
        }

        if (!Fromdate.IsNull)
        {
            Query += " AND o.OrderDate >= @fromdate";
            Command.Parameters.Add("@fromdate", SqlDbType.DateTime);
            Command.Parameters["@fromdate"].Value = Fromdate;
            Command.Parameters["@fromdate"].Direction = ParameterDirection.Input;
        }

        if (!Todate.IsNull)
        {
            Query += " AND o.OrderDate <= @todate";
            Command.Parameters.Add("@todate", SqlDbType.DateTime);
            Command.Parameters["@todate"].Value = Todate;
            Command.Parameters["@todate"].Direction = ParameterDirection.Input;
        }

        if (!Minprice.IsNull)
        {
            Query += " AND od.UnitPrice >= @minprice";
            Command.Parameters.Add("@minprice", SqlDbType.Money);
            Command.Parameters["@minprice"].Value = Minprice;
            Command.Parameters["@minprice"].Direction = ParameterDirection.Input;
        }

        if (!Maxprice.IsNull)
        {
            Query += " AND od.UnitPrice <= @maxprice";
            Command.Parameters.Add("@maxprice", SqlDbType.Money);
            Command.Parameters["@maxprice"].Value = Maxprice;
            Command.Parameters["@maxprice"].Direction = ParameterDirection.Input;
        }

        if (!Custid.IsNull)
        {
            Query += " AND o.CustomerID = @custid" +
                     " AND c.CustomerID = @custid";
            Command.Parameters.Add("@custid", SqlDbType.NChar, 5);
            Command.Parameters["@custid"].Value = Custid;
            Command.Parameters["@custid"].Direction = ParameterDirection.Input;
        }

        if (!Custname.IsNull)
        {
            Query += " AND c.CompanyName LIKE @custname + '%'";
            Command.Parameters.Add("@custname", SqlDbType.NVarChar, 40);
            Command.Parameters["@custname"].Value = Custname;
            Command.Parameters["@custname"].Direction = ParameterDirection.Input;
        }

        if (!City.IsNull)
        {
            Query += " AND c.City = @city";
            Command.Parameters.Add("@city", SqlDbType.NVarChar, 15);
            Command.Parameters["@city"].Value = City;
            Command.Parameters["@city"].Direction = ParameterDirection.Input;
        }

        if (!Region.IsNull)
        {
            Query += " AND c.Region = @region";
            Command.Parameters.Add("@region", SqlDbType.NVarChar, 15);
            Command.Parameters["@region"].Value = Region;
            Command.Parameters["@region"].Direction = ParameterDirection.Input;
        }

        if (!Country.IsNull)
        {
            Query += " AND c.Country = @country";
            Command.Parameters.Add("@country", SqlDbType.NVarChar, 15);
            Command.Parameters["@country"].Value = Country;
            Command.Parameters["@country"].Direction = ParameterDirection.Input;
        }

        if (!Prodid.IsNull)
        {
            Query += " AND od.ProductID = @prodid" +
                     " AND p.ProductID = @prodid";
            Command.Parameters.Add("@prodid", SqlDbType.Int);
            Command.Parameters["@prodid"].Value = Prodid;
            Command.Parameters["@prodid"].Direction = ParameterDirection.Input;
        }

        if (!Prodname.IsNull)
        {
            Query += " AND p.ProductName LIKE @prodname + '%'";
            Command.Parameters.Add("@prodname", SqlDbType.NVarChar, 40);
            Command.Parameters["@prodname"].Value = Prodname;
            Command.Parameters["@prodname"].Direction = ParameterDirection.Input;
        }

        Query += " ORDER BY o.OrderID";

        using (SqlConnection Connection = new SqlConnection("context connection=true"))
        {
            Connection.Open();

            if (Debug)
            {
                SqlContext.Pipe.Send(Query);
            }

            Command.CommandType = CommandType.Text;
            Command.CommandText = Query;
            Command.Connection = Connection;
            SqlContext.Pipe.ExecuteAndSend(Command);
        }
    }
};