Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2009-03-27 : 11:22:44
|
Ok, I have a class that defines two stored procedures (RunProcess and GetData). Now, I want to use GetData in two contexts. First, I want to be able to call it directly and have it feed the results directly to the client as it does now. The second context is I want to be able to call RunProcess, have it call GetData and iterate through the results GetData returns.Now my question is this, is there a way to intercept the pipe directly or will I need to resort to pushing the data to a datareader, sending that back to the client and also returning a datareader object from GetData?using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;namespace MyCompany.DBA.Maintenance.Index{ public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void RunProcess() { try { GetData(); //TO DO: loop through results and execute additional tasks. } catch (System.Exception e) { SqlContext.Pipe.Send("An error occured: " + e.Message); throw new System.ApplicationException(e.Message); } } [Microsoft.SqlServer.Server.SqlProcedure] public static void GetData() { System.Text.StringBuilder sql = new System.Text.StringBuilder(); try { sql.AppendLine("SELECT * FROM some long join"); using (SqlConnection conn = new SqlConnection("Context connection=true")) { using (SqlCommand command = new SqlCommand(sql.ToString(), conn)) { conn.Open(); SqlContext.Pipe.ExecuteAndSend(command); conn.Close(); } } } catch (System.Exception e) { SqlContext.Pipe.Send("An error occured: " + e.Message); throw new System.ApplicationException(e.Message); } } };} |
|
|
|
|