Accessing Stored Procedure Return Value From .NET

Ed
Ed used Ask the Experts™
on

Can somone explain to me the simplest most effective way of accessing the return value from a stored procedure using .Net(vb)?

I want to display it in a label on a page

Return @@identity

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project manager
Commented:
You can find an example here

Modifying Data with Stored Procedures (ADO.NET)
http://msdn.microsoft.com/en-us/library/59x02y99.aspx

Short example below
myCommand.Parameters.Add("@myReturn")
myCommand.Parameters("@myReturn").Direction = ParameterDirection.ReturnValue

or you could simply declare an OUTPUT parameter for your stored procedure and set its value to 0 or 1 within the sp and then define as follows

myCommand.Parameters.Add("@myReturn")
myCommand.Parameters("@myReturn").Direction = ParameterDirection.Output

myCommand.ExecuteNonQuery()
If myCommandParameters("@myReturn").Value = 0 then
    ' do something
ELSE
    ' do something else
End If

Open in new window

Imran Javed ZiaConsultant Software Engineer - .NET Architect

Commented:
Hi,
You can use following:
SqlCommand cmd = new SqlCommand();
            //please command Initialization

            object val = cmd.ExecuteScalar();
            if (val != null && val != DBNull.Value)
                lbl.Text = val.ToString();

or

SqlCommand cmd = new SqlCommand();
            //please command Initialization

            object val = cmd.ExecuteScalar();
            if (val != null && val != DBNull.Value)
                return  val.ToString();
            else
                return string.Empty;
   

Thanks
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Hi,
You can use following:

If inserting a new row then use Scope_Identity()
SqlCommand cmd = new SqlCommand();
            object val = cmd.ExecuteScalar();
            if (val != null && val != DBNull.Value)
                lbl.Text = val.ToString();

or

SqlCommand cmd = new SqlCommand();
            //please command Initialization

            object val = cmd.ExecuteScalar();
            if (val != null && val != DBNull.Value)
                return  val.ToString();
            else
                return string.Empty;

Open in new window

Top Expert 2012

Commented:
edjones1,

Since you just need to get the RETURN parameter, you cannot use the ExecuteScalar method, go with the ExecuteNonQuery method instead as suggested by Dhaest (no points please).
Ed

Author

Commented:
The link was really helpful, thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial