Wie werden Parameter von SQL server zu einer CLR-basierten gespeicherten Prozedur übergeben?

Verwenden Sie dieses Beispiel:

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

…. Ich habe eine CLR-basierte gespeicherte Prozedur auf SQL server gebaut und erfolgreich implementiert. Bei der Betrachtung der resultierenden gespeicherten Prozedurdefinition innerhalb von SSMS (siehe Code unten) stelle ich fest, dass die in der C # -De definierten Parameter tatsächlich Teil der SP-Definition sind, aber wenn man den tatsächlichen Aufruf der gespeicherten Prozedur betrachtet, gibt es keine Verweis auf die Parameter, also bin ich neugierig, wie die Parameterwerte tatsächlich durchlaufen werden?

(Der Grund, den ich gefragt habe, ist im C # -Code, ich frage mich, ob ich die Query- Variable auf jede vorhandene gespeicherte Prozedur setzen möchte, die ich will, und dann in einem großen Varchar-Argument in allen relevanten Argumenten als abgegrenzte KeyValue-Paare übergeben, und dann teilen Sie diese und fügen Sie sie als Command.Parameters innerhalb einer loop. Grundsätzlich versuche ich, eine generische gespeicherte Prozedur zu erstellen, die jede andere gespeicherte Prozedur ausführen kann, ohne irgendwelche Einschränkungen für unterschiedliche Zähl- oder datatypen von Parametern, all das würde zur Laufzeit gelesen werden.So grundsätzlich die gleiche Flexibilität haben Sie beim Aufruf von C # in SQL server, außer in SQL server implementiert.)

Resultierende gespeicherte Proc (in SSMS gesehen):

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 # Quelle:

 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); } } }; 

es gibt keinen Hinweis auf die Parameter, also bin ich neugierig, wie die Parameterwerte tatsächlich durchlaufen werden?

Es ist die gleiche Signatur (datatyp & Position nur) zwischen dem T-SQL-Wrapper-object (dh die gespeicherte Prozedur, function, benutzerdefinierter Typ, benutzerdefinierter Aggregat oder Trigger), die Sie in SQL server sehen und mit via T interagieren -SQL (zB das CREATE PROCEDURE Beispiel in der Frage) und die .NET-Methode, auf die über die EXTERNAL NAME Klausel dieses T-SQL-Wrapperobjekts verwiesen wird.

Die Parameternamen werden nicht zwischen den T-SQL-Wrapperobjektparametern und den Parameter der .NET-Methode abgeglichen. Sie können dies überprüfen, indem Sie die Parameternamen in der in der Frage gespeicherten gespeicherten Prozedur neu @a oder sie einfach nur umbenennen, um @a , @b , '@c' usw. zu sein. Dann ändern Sie das CREATE in ALTER , führen Sie es aus und dann lauf die gespeicherte Prozedur wieder mit den gleichen Eingaben und es wird immer noch so funktionieren, wie es vor den Parameternamen geändert wurde.


Auf einer verwandten Anmerkung ist diese besondere Verwendung von SQLCLR ziemlich sinnlos. Es gibt keinen Vorteil, dasselbe in reinen T-SQL zu tun (dh dynamisch eine Abfrage, eine list von Parametern und eine list von Parameterwerten in 3 NVARCHAR (MAX) variables zu erstellen und sie dann in einer zweiten dynamisch erzeugten Abfrage zu kombinieren ist nur der EXEC sp_executesql Aufruf. Nun, wenn das dynamische Such- EXEC sp_executesql stattdessen ein TVF wäre , wäre das eine gültige Verwendung von SQLCLR, da es etwas tun würde, was nicht in T-SQL getan werden kann: dynamisches SQL in einer function!