none
MS SQL return value RRS feed

  • Question

  • Hello,
    I have this.
    CREATE PROCEDURE [dbo].[procGetSerialNumber]
    	@Line nvarchar(40),
    	@Family nvarchar(60)
    AS
    	SELECT * from [dbo].[LOCALDB] where ColumnFamily=@Family
    	RETURN 7
    
    and want to get the return value.
    How is the right way?
     SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
     RetVal.Direction = ParameterDirection.ReturnValue;


    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
     //Get the return value
     int returnvalue = (int)cmd.Parameters["ReturnValue"].Value;

    Sure I need Input , Output Parameter in the right way.

    Is there a good sample in the net.

    Greetings Markus


    Saturday, July 27, 2019 10:04 AM

Answers

  • My parameter is named with "Return_Value"

    Don't worry about the name of the parameter. It's irrelevant, the system doesn't use it. You can assign any name that is convenient for readability. At runtime, ADO.NET recognizes that this parameter should receive the RETURN from the procedure thanks to ParameterDirection.ReturnValue, and it ignores the parameter name.

    This only applies to ReturnValue. Other types of parameters such as In, Out or InOut do require the name supplied to match the name that the parameter has in the stored procedure (including the @).

    Monday, July 29, 2019 8:50 PM
    Moderator

All replies

  • Hello,

    Try using

    cmd.ExecuteNonQuery();
    var result = RetVal.Value;


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Saturday, July 27, 2019 11:09 AM
    Moderator
  • What you are doing is basically right: You assign to the SqlCommand a parameter with its ParameterDirection set to ReturnValue.

    However, for this to work it is critical to read the result in the correct order. You need to read the Retval after closing the SqlDataReader but before closing the SqlConnection. If you do it at any other time it doesn't work.

    using (SqlConnection cn = new SqlConnection(...))
    {
        SqlParameter RetVal = cmd.Parameters.Add("Return_Value", SqlDbType.Int);
        RetVal.Direction = ParameterDirection.ReturnValue;
        cn.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            // Process the data returned by the Select statement in the procedure
        }
    
        //Get the return value. Must be done exactly here: After disposing the SqlDataReader byt before disposing the connection.
        int returnvalue = (int)RetVal.Value;
    
        //...
    }


    Saturday, July 27, 2019 11:50 AM
    Moderator
  • Hello,
    Thanks for the response.
      
    SQL Script
    CREATE PROCEDURE [dbo].[procGetSerialNumber]
     @Line nvarchar(40),
     @Family nvarchar(60)
    AS
     SELECT * from [dbo].[LOCALDB] where ColumnFamily=@Family
     RETURN 7
    C# Code
     SqlParameter RetVal = cmd.Parameters.Add("Return_Value", SqlDbType.Int);
        RetVal.Direction = ParameterDirection.ReturnValue;
    In my example the return value is 7, however.
    My parameter is named with "Return_Value", but I think I need here a right ID.
    I think in my SQL script should be insert the name "Return_Value", but in what place, in what location?
    If I check with Return_Value, what should the script file look like?
    Do you have a script template for
      -IN
      -OUT
      -RETURN
    The question is otherwise answered.
    With best regards Markus
    Monday, July 29, 2019 5:04 PM
  • My parameter is named with "Return_Value"

    Don't worry about the name of the parameter. It's irrelevant, the system doesn't use it. You can assign any name that is convenient for readability. At runtime, ADO.NET recognizes that this parameter should receive the RETURN from the procedure thanks to ParameterDirection.ReturnValue, and it ignores the parameter name.

    This only applies to ReturnValue. Other types of parameters such as In, Out or InOut do require the name supplied to match the name that the parameter has in the stored procedure (including the @).

    Monday, July 29, 2019 8:50 PM
    Moderator