How to execute SubSonic3 StoredProcedure with return value

1
votes

How do I execute a SP and get the return value. The below code always returns null object. The storedprocedure has been tested in the database using the same parameters as in code, but the SubSonic sp always returns null. When executed in the db via sql, it returns the correct values.

This is using SubSonic 3.0.0.3.

myDB db = new myDB();
StoredProcedure sp = db.GetReturnValue(myParameterValue);
sp.Execute();
int? myReturnValue = (int?)sp.Output;

In the above code, sp.Output is always null. When executed in the database, the returned variable is a valid integer (0 or higher) and is never null.

Stored procedure code below:

CREATE PROCEDURE  [dbo].[GetReturnValue]    
     @myVariable varchar(50)
AS
    declare @myReturn int
BEGIN
    set @myReturn = 5;
    return @myReturn;   
END

When executing the stored proc in SQL Server, the returned value is '5'.

3
stored-proceduressubsonicsubsonic3
Can you post the sql from the sproc? – John Sheehan
Added stored proc code to question above. – Jim W

3 Answers

3
votes

I copied your sproc and stepped through the SubSonic code and .Output is never set anywhere. A work around would be using an output parameter and referring to it after executing: sproc.OutputValues[0];

1
votes

Here's a simple way to do it:

In the stored procedure, instead of using RETURN, use SELECT like this:

SELECT @@ROWCOUNT

or

SELECT @TheIntegerIWantToReturn

Then in the code use:

StoredProcName.ExecuteScalar()

This will return the single integer you SELECTED in your stored procedure.

0
votes
CREATE PROCEDURE  [dbo].[GetReturnValue]    
     @myVariable varchar(50)
     @myReturn BIGINT OUTPUT
AS
    declare @myReturn int
BEGIN
    set @myReturn = 5;
    return @myReturn;   
END