Output Parameter is not returning value

Question

Hello friends My stored is not returning any value in output parameter Code is given below. try { //Declaring parameter SqlParameter[] parameter = new SqlParameter[2]; //name and type declaration of the table variable to be passed as parameter parameter[0] = new SqlParameter(@EmpDetailsdt); parameter[0].Direction = ParameterDirection.Input; parameter[0].SqlDbType = SqlDbType.Structured; parameter[1] = new SqlParameter(); parameter[1].ParameterName = @errormessage; parameter[1].Direction = ParameterDirection.Output; parameter[1].SqlDbType = SqlDbType.VarChar; parameter[1].Size = 1000; //invoking stored procedure ImportEmployee SqlHelper.ExecuteNonQuery(connectsql CommandType.StoredProcedure sp_importemployee parameter); if (parameter[1].Value.ToString() != ) { Errors=(parameter[1].Value.ToString()); } } catch (Exception ex) { Errors=ex.Message.ToString(); } Any quick help will be very much appreciable

Nilesh Shah · Answer

@Ankit it seems you did not try what I said in my last answer. In your SP you are not setting any value for @errormessage anywhere you are just adding the message to it. When you don't set any value and don't pass any value too from C# code default is null. hence your statement set @errormessage = @errormessage+' User name '+@user_name+' already Exists'; always sets @errormessage as null and you don't get any return value. just write this in first line of your SP it will work: set @errormessage = ''

Nilesh Shah · Answer

@ankit first of all make sure your SP sets the value of error message in case there is an error. try running it temporary modifying it to set error message to some default in first line of SP and check if you get value back or not. if you get this default value back then it has been confirmed that your code to get SP return value is fine now change SP to original and see the SP is not executing the way you think it should.

Nilesh Shah · Answer

where is the SP part? are you setting some value in the @errormessage variable in your SP when an error occurs?

Nilesh Shah · Answer

Ankit if you are not expecting any answer please close this thread

Ankit Kumar · Answer

Hi Nilesh I tried your solution too but didn't get my things donehowever i have resolved the error the problem was whenever sql was throwing exception error message was not getting returned and i was throwing those errors intentionally for testing purpose. Thanks for replying

Ankit Kumar · Answer

HI @Nilesh Thanks for your reply. Here is my Sp part CREATE procedure [dbo].[sp_importemployee] ( @EmpDetails dbo.EmployeeType readonly @errormessage varchar(100) output ) as begin declare @idColumn int declare @userid int declare @user_name varchar(30) declare @emp_code varchar(30) declare @empid int declare @Counter int =0 DECLARE @intFlag INT =1 select @Counter= count(*) from @empdetails begin try begin tran WHILE (@intFlag BEGIN select @user_name = username from @EmpDetails where id = @intFlag; select @emp_code = EmployeeCode from @EmpDetails where id =@intFlag; begin if not exists (select 1 from secuser where UserName=@user_name) begin insert into secuser(username password firstname lastname roleid status DepartmentId USERTYPE createdOn createdbyUpdatedOn UpdatedBy) (select username' ' firstname lastname EmployeeRoleId 1 departmentidEmployeeRoleId getdate() ' ' getdate() ' ' from @empdetails where id=@intFlag) set @userid= SCOPE_IDENTITY() end else begin set @errormessage = @errormessage+' User name '+@user_name+' already Exists'; end end begin if not exists (select 1 from employee where EmpCode=@emp_code) begin insert into Employee(Empcode userid FirstNameMiddleNameLastNameGenderDepartmentIdDepartmentNameDesignationIdDesignationName ShiftId CompanyID BranchIDPhoneCellPhoneOfficePhoneExtension ReportingToEmpId RoleId ContractType username) (select EmployeeCode @userid FirstName MiddleName LastName Gender DepartmentId Department DesignationId Designation ShiftId CompanyId BranchId HomePhoneNo CellPhone OfficePhoneNo Extension SelectEmployee EmployeeRoleId EmployeeTypeusername from @EmpDetails where id=@intFlag) set @empid=SCOPE_IDENTITY(); end else begin set @errormessage = @errormessage+' Employee code'+@emp_code+' already exists' end end begin insert into EmployeeShifts(EmployeeId ShiftId FromDate ToDate IsActive createdon updatedon) select @empidShiftId ShiftStartDate ShiftEndDate 1 getdate() getdate() from @empdetails where id=@intFlag end SET @intFlag = @intFlag + 1 END commit tran end try begin catch rollback tran end catch end GO

Ankit Kumar · Answer

@Nilesh I have applied some validation in my sp_importemployee procedure and setting errors messages during those validations in my output parameter. and even validation will definitely fail because i am inserting duplicate data and i have applied validation for the same and i am not getting any error but still value isn't getting returned.

Tapan Patel · Answer

If you are not sure about sp returning value back please start with executing SP in SSMS as below. If you are getting value back then focus on code. EXEC dbo.YourSP @param1 = somevalue @param2 OUT PRINT @param2