none
How get the output, return values from exec sql procedure? RRS feed

  • Question

  •  

    i have procedure as follow:

     

    myprocedure(@Cusname varchar(50), @Cusid bigint output)

    as

    insert into Customer (Cusname) values (@Cusname)

    SELECT @@cusid = @@IDENTITY

     

    in CuslistTableAdapter, i add the query:

     

    CreateCustomerID(@Cusname,@CusID)

     

    Now, in BLL and UI layer how can i write function in order to receive output (CusID) value?

     

    Thanks,

     

     

    Thursday, October 11, 2007 3:38 AM

Answers

All replies

  • Here is sample how to work with output parameters,

     

    http://support.microsoft.com/kb/308049/en-us

     

    Basically you need to declare parameters in your code and set Direction property of output parameters to ParameterDirection.Output or ParameterDirection.InputOutput

     

    Thursday, October 11, 2007 10:02 AM
    Moderator
  • Now i want to write the function in BLL in order to receive ouput parameter from procedure execution

     

    private Merp_CusListTableAdapter _CuslistAdapter = null;

    protected Merp_CusListTableAdapter Adapter

    {

    get

    {

    if (_CuslistAdapter == null)

    _CuslistAdapter = new Merp_CusListTableAdapter();

    return _CuslistAdapter;

    }

    }

     

     

    public Int64 CreateCusID(String Cusname)

    {

    long CusID;

    Adapter.CreateCustomerID(Cusname,ref CusID);

    // Int64 CusID = 30000;

    return CusID;

    }

     

    But it is not run, how can i fix it?

    Friday, October 12, 2007 4:09 AM
  • Have you created collection of the parameters in your code to pass it to SqlCommand that selects data? After you call Fill method of your DataAdapter, you need to read output parameter to get that data. I believe sample I have posted shows how to do this.

     

    Friday, October 12, 2007 10:22 AM
    Moderator