StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POProcessing multiple results from CLR stored procedure in T-SQL
text
Body
copied!<p>I have some complex algorithm written in C# as CLR Stored procedure. Procedure is not deterministic (it depends on current time). The result of procedure are two tables. I didn't found any solution how to process multi-results from stored procedures in T-SQL. The performance of this procedure is key (procedure is called every ~2 seconds).</p> <p>I found the fastest way how to update tables is:</p> <pre><code>UPDATE [db-table] SET ... SELECT * FROM [clr-func] </code></pre> <p>It's much faster then update db-table from CLR procedure via ADO.NET.</p> <p>I used static field to store results and query it after the execution of clr stored procedure.</p> <p>The call stack is:</p> <pre><code>T-SQL proc -> CLR proc (MyStoredProcedure) -> T-SQL proc (UpdateDataFromMyStoredProcedure) -> CLR func (GetFirstResultOfMyStoredProcedure) -> CLR func (GetSecondResultOfMyStoredProcedure) </code></pre> <p>The problem is, sometimes CLR functions has null in static field <code>result</code>, but in CLR procedure is <code>result</code> not null. I found, sometimes the CLR functions are called in another AppDomain than CLR procedure. However CLR procedure is still running and can do next operations and no exception is thrown.</p> <p>Is there some way, how to force CLR functions to be called in same AppDomain as "parent" CLR procedure?</p> <p>Or is there some another way, how to achieve my intention?</p> <p>P.S.: Originally the complex algorithm was written in T-SQL, but performance was poor (~100x slower than algorithm in C#).</p> <p>Thanks!</p> <p>Simplified code:</p> <pre><code>// T-SQL CREATE PROC [dbo].[UpdateDataFromMyStoredProcedure] AS BEGIN UPDATE [dbo].[tblObject] SET ... SELECT * FROM [dbo].[GetFirstResultOfMyStoredProcedure]() UPDATE [dbo].[tblObjectAction] SET ... SELECT * FROM [dbo].[GetSecondResultOfMyStoredProcedure]() END // ... somewhere else EXEC [dbo].[MyStoredProcedure] </code></pre> <p>-</p> <pre><code>// C# public class StoredProcedures { // store for result of "MyStoredProcedure ()" private static MyStoredProcedureResult result; [SqlProcedure] public static int MyStoredProcedure() { result = null; result = ComputeComplexAlgorithm(); UpdateDataFromMyStoredProcedure(); result = null; } [SqlFunction(...)] public static IEnumerable GetFirstResultOfMyStoredProcedure() { return result.First; } [SqlFunction(...)] public static IEnumerable GetSecondResultOfMyStoredProcedure() { return result.Second; } private static void UpdateDataFromMyStoredProcedure() { using(var cnn = new SqlConnection("context connection=true")) { using(var cmd = cnn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "[dbo].[UpdateDataFromMyStoredProcedure]"; cmd.ExecuteNonQuery(); } } } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload