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);
}
}
};
there is no reference to the parameters, so I'm curious how the parameter values are actually passed through?
It is the same signature (datatype & position only) between the T-SQL wrapper object (i.e. the Stored Procedure, Function, User-Defined Type, User-Defined Aggregate, or Trigger) that you see in SQL Server and interact with via T-SQL (e.g. the CREATE PROCEDURE example in the question) and the .NET method that is referenced via the EXTERNAL NAME clause of that T-SQL wrapper object.
The parameter names are not matched between the T-SQL wrapper object parameters and the .NET method's parameters. You can verify this by rearranging the parameter names in the Stored Procedure shown in the question, or even just rename them all to be @a, @b, '@c', etc. Then change the CREATE into ALTER, run it, and then run the Stored Procedure again with the same inputs and it will still work just as it did before the parameter names change.
On a related note, that particular usage of SQLCLR is fairly pointless. It offers no benefit over doing the same thing in pure T-SQL (i.e. dynamically creating a query, list of parameters, and list of parameter values, in 3 NVARCHAR(MAX) variables, and then combining them in a second dynamically created query that is just the EXEC sp_executesql call. Now, if that dynamic search proc would have instead been a TVF, that would be a valid use of SQLCLR as it would be doing something that cannot be done in T-SQL: dynamic SQL in a function!