none
how to obtain declared variable value in a sql statement RRS feed

  • Question

  • this code works in mfc

    " DECLARE @Err int, @OrderExists int "
                             " EXEC @Err = CheckForGA800Orders "
                             "   @DBNo      = %d, "
                             "   @LocaleNo  = %d  ",
                             dbNo,
                             localeNo);

    I want to make it work with ADO.net. I want to get values of @Err and @OrderExists after executing the statement:

    can some one help me please. I tried

      cmnd.CommandType = CommandType.StoredProcedure;
                    cmnd.CommandText = "CheckForOrders";
                    cmnd.Parameters.Add("@DBNo", SqlDbType.Int).Value =  dbNo;
                    cmnd.Parameters.Add("@LocaleNo", SqlDbType.Int).Value =  localeNo;
                    SqlParameter outputIdParam = new SqlParameter(" @OrderExists", SqlDbType.Int);
                  cmnd.Parameters.Add(outputIdParam);
    
                    conn.Open();
                int result= (int)    cmnd.ExecuteScalar ();
               int xst =(int) outputIdParam.Value;

    but got error  stored proc .... does not have out put parameter @@OrderExists

    Thursday, June 6, 2013 8:10 PM

Answers

  • i found solution after looking into the stored procedure: the final product is this

    select @err,@orderexists

    so obvious

    Friday, June 7, 2013 4:42 PM

All replies

  • Hi Kobosh,

    Welcome to the MSDN Forum.

    You also need to set the direction: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.direction.aspx 

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 7, 2013 10:08 AM
    Moderator
  •  cmnd.CommandType = CommandType.StoredProcedure;
                    cmnd.CommandText = "CheckForOrders";
                    cmnd.Parameters.Add("@DBNo", SqlDbType.Int).Value = 1;// dbNo;
                    cmnd.Parameters.Add("@LocaleNo", SqlDbType.Int).Value = 2000;// localeNo;
                    SqlParameter outputIdParam = new SqlParameter("@OrderExists", SqlDbType.Int);
                    outputIdParam.Direction = ParameterDirection.Output;
                   cmnd.Parameters.Add(outputIdParam);
    
                    conn.Open();
                int result= (int)    cmnd.ExecuteScalar ();
                int xst =(int) outputIdParam.Value;
    this did not work: the same error  stored proc checkfororders does not have parameter    @OrderExists
    Friday, June 7, 2013 3:44 PM
  • i found solution after looking into the stored procedure: the final product is this

    select @err,@orderexists

    so obvious

    Friday, June 7, 2013 4:42 PM