Is it in any way possible to have a sProc called and executed within
SQLServer return its return value to an .Net Vb application or indeed any
application?

As a programmer I beleive this can be done and if I wreck my head enough I
will find a way. I have toyed with the ideas of xml, SQLServer events but
have found nothing so far.

As this is the final stage of my project I really would appreciate any input
given.

Thanks
marc.

Returning results from procs is the preferred way. With the .NET framework
you set the command type to stored procedure, the command text to the name of
the proc, add parameters and return a data reader which loops thru the result
set(s).

Try the group dotnet.framework.adonet for more info on the client side stuff.


Show quoteHide quote

Input and Output Parameters, and Return Values http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp


AMB

Show quoteHide quote

Show quote Hide quote Anything you can do in TSQL you can do from ADO.NET.

Just paste the TSQL into a SqlCommand, and replace local variables with
bound parameters.

    declare @p1 varchar(50)
    declare @p2 int
    declare @rv int

    set @p1 = "hello"
    set @p2 = 4

    exec @rv = MyProc @p1, @p2

becomes


    using (SqlConnection con = new SqlConnection(""))
    {
      con.Open();
      SqlCommand cmd = new SqlCommand("exec @rv = MyProc @p1, @p2", con);
      cmd.Parameters.Add("@rv", SqlDbType.Int).Direction =
ParameterDirection.Output;
      cmd.Parameters.Add("@p1", SqlDbType.VarChar, 50).Value = "hello";
      cmd.Parameters.Add("@p2", SqlDbType.Int).Value = 4;
      cmd.ExecuteNonQuery();
      int rv = cmd.Parameters["@rv"].Value;
    }

David

Thanks guys but the clincher is this.
I know how to catch the return value within the application from the sProc
if it is executed within the application.

I execute the sProc from SQLServer not from the app.
However I would somehow like the app to pick up the return value from the
sProc even though it is executed from within SQLServer(inside another
independant program seperate from the application logic).

Told you it was weird.
Impossible?

Client call has a single link to the db.. the initial call (sp, I
assume)...  Without getting into funky export/import data or saving your
resultset in a table (temp or otherwise, you have to have a direct link
between the calling process and the data you wish returned..

HTH
P.
marcmc wrote:
Show quoteHide quote

So?

DECLARE @r INT
EXEC @r = dbo.SomeProcedureName
PRINT @r

For the results are you talking about a resultset, like you get back when
you perform a SELECT * statement or are you talking about a single scalar
(INT, CHAR, etc.) value?

Either way my recommendation would be the same, although the implementation
details would be a little different depending on which route you're going.
Just have the SP save the results to a table.

The catch is that you need to make sure the table does not grow out of
control.

Show quoteHide quote

Hi,

I know exactly what u mean. I am looking for the same thing. If you have
got the answer please post it.

Thanks,
Imran

*** Sent via Developersdex http://www.developersdex.com ***

At the end of your SP, add a SELECT statement.  Here's a simple example:

SELECT IDNum, LastName, FirstName FROM People

On the VB.NET side you could either Open a Dataset and Fill it with a
SqlDataAdapter that calls this SP, or you could use a SqlDataReader and set
it to ExecuteReader on a SqlCommand.

Show quoteHide quote

Can I do this?
is there a set-based solution to this task?
Basic Query Help
Fetch statement
DTS Issue while calling from another domain