Output Parameter is not returning value

Ankit,

 

if you are not expecting any answer please close this thread

Hi Nilesh,

 

I tried your solution too but didn't get my things done,however, 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,

 

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 = '' 

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 <= @Counter)

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, createdby,UpdatedOn, UpdatedBy)
(select username,' ', firstname, lastname, EmployeeRoleId, 1, departmentid,EmployeeRoleId, 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, FirstName,MiddleName,LastName,Gender,DepartmentId,DepartmentName,DesignationId,DesignationName, ShiftId, CompanyID, BranchID,Phone,CellPhone,OfficePhone,Extension, ReportingToEmpId, RoleId, ContractType, username)
(select EmployeeCode, @userid, FirstName, MiddleName, LastName, Gender, DepartmentId, Department, DesignationId, Designation, ShiftId, CompanyId, BranchId, HomePhoneNo, CellPhone, OfficePhoneNo, Extension, SelectEmployee, EmployeeRoleId, EmployeeType,username 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 @empid,ShiftId, 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,

 

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.

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.

 

 

@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.

where is the SP part?

 

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