[Solved] Getting the value from an SP which returns one value

[sitefinitysteve]

My SP calls over to another DB to return a username string...in a single line of code how can I just extract that value to display?

[Serge]

Hello Steve,

In this scenario you can use an output parameter for retrieving this value. More information on how to handle output parameters with OpenAccess Classic you can find in this help article. If you are using the new domain model however, you will need to do a small modification of the generated code.

If you for example have reverse mapped a Northwind database with the domain model in you context class you will have a method for each procedure. Lets have a look at this one: 

public object[] SalesByCategory(string categoryName, string ordYear)

{

    SqlParameter parameterCategoryName = new SqlParameter("CategoryName", OpenAccessType.Varchar);

    SqlParameter parameterOrdYear = new SqlParameter("OrdYear", OpenAccessType.Varchar);

 

    List<SqlParameter> sqlParameters = new List<SqlParameter>()

    {

        parameterCategoryName, parameterOrdYear

    };

     

    object[] queryResult = this.ExecuteStoredProcedure<object>("'SalesByCategory' ?,?", sqlParameters ,categoryName, ordYear); 

    return queryResult;

}

If say the CategoryName parameter was an out parameter this would not work, as a matter of fact there is a small glitch in the code generation of the context class that we have fixed but is not released. What we would need to do to make it work is copy paste this method into a partial class of the context class and modify the declaration of the parameterCategoryName to : 

SqlParameter parameterCategoryName = new SqlParameter("CategoryName", OpenAccessType.Varchar, ParameterMode.Out);

While this will work, it seems rather troublesome as you need to modify both your stored procedure and code. Unfortunately there is no simple way of return a single value from a stored procedure. 

Maybe retrieving the QueryResult and parsing it by hand is the better choice here. 

I do hope this helps.

Greetings,
Serge
the Telerik team