0

I have this stored procedure and I need to get the return value from it

ALTER PROCEDURE [dbo].[sp_TenantDetails_insert]
    @TDtypeid NVARCHAR(10), 
    @TDtypeEn NVARCHAR(50), 
    @TDtypeAr NVARCHAR(50), 
    @TDNameEn NVARCHAR(MAX),
    @TDNameAr NVARCHAR(MAX),
    @TDportfolionameEn NVARCHAR(MAX), 
    @TDportfolionameAr NVARCHAR(MAX), 
    @TDpropertynameEn NVARCHAR(MAX), 
    @TDpropertynameAr NVARCHAR(MAX), 
    @TDoffice_code NVARCHAR(MAX),  
    @TDps_contract_hdr_code NVARCHAR(MAX),  
    @TDcivil_id NVARCHAR(MAX), 
    @TDmobile NVARCHAR(50), 
    @TDemail NVARCHAR(50), 
    @TDportfolio_code NVARCHAR(50),
    @TDproperty_code NVARCHAR(50),  
    @TDunit_nick_name NVARCHAR(MAX), 
    @TDstartdate NVARCHAR(50),
    @TDendtdate NVARCHAR(50), 
    @TDmon_rent NVARCHAR(50),
    @PayType NVARCHAR(50),
    @TDTotMonths INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @tenantid Int

    INSERT INTO tbl_ream_TenantDetails(R_typeId,R_typeEn, R_typeAr, R_tenantNameEn, R_tenantNameAr,R_portfolionameEn, R_portfolionameAr, R_propertynameEn, R_propertynameAr, R_office_code,  R_ps_contract_hdr_code,  R_civil_id, R_mobile,R_email, R_portfolio_code,R_property_code,  R_unit_nick_name, R_startdate,R_endtdate, R_mon_rent, R_pay_type, R_tot_months)
    VALUES (@TDtypeid, @TDtypeEn, @TDtypeAr, @TDNameEn, @TDNameAr, 
            @TDportfolionameEn, @TDportfolionameAr, @TDpropertynameEn, 
            @TDpropertynameAr, @TDoffice_code, @TDps_contract_hdr_code,  
            @TDcivil_id, @TDmobile, @TDemail, @TDportfolio_code, 
            @TDproperty_code, @TDunit_nick_name, @TDstartdate, @TDendtdate, 
            @TDmon_rent, @PayType, @TDTotMonths) 

    SET @tenantid = SCOPE_IDENTITY()

    RETURN @tenantid

    SET NOCOUNT OFF
END

I have created a dataset and added it as a query in the dataset

I tried to get the value using the below codes but it returns 0 instead of the SCOPE_IDENTITY() value

 public int GetInsertId(string typeId, string typeEn, string typeAr, string tenantNameEn, string tenantNameAr, string portfolionameEn, string portfolionameAr, string propertynameEn, string propertynameAr, string office_code, string ps_contract_hdr_code, string civil_id, string mobile, string email, string portfolio_code, string property_code, string unit_nick_name, string startdate, string endtdate, string mon_rent, string pay_type, int tot_months)
    {

        try
        {
            //int objResult = 0;
            //DataTable dt = new DataTable();
            //dt = AdapterTenantSelectDetailsInsert.sp_TenantDetailsInsert(typeId, typeEn, typeAr, tenantNameEn, tenantNameAr, portfolionameEn, portfolionameAr, propertynameEn, propertynameAr, office_code, ps_contract_hdr_code, civil_id, mobile, email, portfolio_code, property_code, unit_nick_name, startdate, endtdate, mon_rent, pay_type, tot_months);
            //if (dt.Rows.Count > 0)
            //{
            //    DataRow UserRow = dt.Rows[0];
            //    objResult = int.Parse(UserRow[0].ToString()) ;
            //}

            //var test = AdapterTenantSelectDetailsInsert.sp_TenantDetails_insert(typeId, typeEn, typeAr, tenantNameEn, tenantNameAr, portfolionameEn, portfolionameAr, propertynameEn, propertynameAr, office_code, ps_contract_hdr_code, civil_id, mobile, email, portfolio_code, property_code, unit_nick_name, startdate, endtdate, mon_rent, pay_type, tot_months);
            int objResult = 0;
            objResult = Convert.ToInt32((AdapterTenantSelectDetailsInsert.sp_TenantDetails_insert(typeId, typeEn, typeAr, tenantNameEn, tenantNameAr, portfolionameEn, portfolionameAr, propertynameEn, propertynameAr, office_code, ps_contract_hdr_code, civil_id, mobile, email, portfolio_code, property_code, unit_nick_name, startdate, endtdate, mon_rent, pay_type, tot_months)));
            return objResult;

        }
        catch (Exception)
        {
            throw;
        }
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HAJJAJ
  • 3,667
  • 14
  • 42
  • 70
  • A return value can be obtained by a `SqlParameter` on the command with direction `ReturnValue`. I don't know if you can add or access the parameter from that adapter you are using. – Crowcoder Aug 12 '18 at 11:55
  • This may help https://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value – Nagib Mahfuz Aug 12 '18 at 12:07
  • 4
    the proper way is to use `OUTPUT` parameter and not return value – Squirrel Aug 12 '18 at 12:17
  • 1
    Also, the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql) recommends avoiding prefix `sp` for user stored procedure names. That prefix is used to denote system stored procedures. – Dan Guzman Aug 12 '18 at 13:04
  • sp prefix should be avoided as it is treated slightly differently. sp does NOT stand for Stored Procedure. It stands for "Special" and should only be used for system stored procedures. – Harry Aug 12 '18 at 21:54

0 Answers0