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.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Return results or return to caller?

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

   

- Advertisement -